How To Speed Up Batch Insert MSSQL
Mar 20, 2008
Hi,
I am in the middle of writing a console application that acquires data from dynamic odbc connections and inserts the results into a MSSQL database.
I'm currently using a datareader to generate multiple calls to a stored procedure and batch execute them by means of a simple counter; this works fine.
However, I'm a little concerned that it seems to take so long to execute the sql stored procs and was wondering if anyone may know of any methods to help speed it up either on the app side or sql, or both.
I had a quick word with our dba who spoke briefly about some kind of process where by the application fires the request across to the database and carries on leaving the db to queue the request or something to that effect. He ran away before I could get any sort of sense out of him.
Any help greatly appreciated
Thanks
View 4 Replies
ADVERTISEMENT
Aug 1, 2006
Hi
I've recently been given the task to maintain a website that runs off mssql using vb and asp .net. One of the main task is to improve the mssql access time. I am fairly new to mssql, can anyone give me some insight on the procedure to improve access time to mssql. Obviously sql query plays a big part, but what about database config, asp/vb .net commands or design patterns (for large amount of data pulling and join?)
Any suggestions or pointer will be greatly appreciated.
View 1 Replies
View Related
Mar 29, 2004
ASP.NET and MsSQL are run inside the same machine, and inside win2000 server,
and the physical memory limit of mssql is set to 192MB.
any one have any good idea(s)? please share to us here
:)
View 4 Replies
View Related
Feb 14, 2005
If I have an application running on MSSQL is it possible that if an action occurs on the application side that a stored procedure can execute a batch script?
View 4 Replies
View Related
Aug 9, 1999
I want to know how I can speed up inserting rows. Will stored procedures help at all? Any ideas are wanted. Thanks.
View 1 Replies
View Related
Feb 7, 2005
I have a data gathering application written in MSVC++ 6 that uses ADO to insert large amounts of data into a table. Currently I have a stored procedure that inserts a single row at a time and I call it everytime I have more data to insert. However this can often fully load SQL Server - I often have 10's or 100's of inserts a second for short periods and load goes up to 100%...
Does anyone know a way of making the inserts more effiicient without resorting to dynamic SQL?
For example is there a way of batching up these inserts such as passing 10 at a time to the sp and inserting them all at once with an "insert into <table> select ..."?
Or would modifying my C++ and wrapping a block of inserts to the single insert sp in a transaction help?
A collegue suggested writing the data to a temporary text file then using bulk insert at regular intervals but that would then involve writing a file management system as well and seems to be a bit of a hack!
Any help much appreciated.
View 14 Replies
View Related
Mar 2, 2008
Hi all
i'm using sqlserver 2005
this statment take 1:30 min to execute
****insert into temotable (select key from table1)
if i used a select statment alone it takes 4 sec
but with insert statment it take 1:30min
by the way i put indexes on the table1
plz how i can increase the speed of insert statment.
thanks in advance
View 4 Replies
View Related
Apr 21, 2008
Hello,
Server hardware: Intel core 2 duo, 2Gb ram, SATA 1500Gb, SQL Server 2005
I have big table (~ from 50000 to 500000 rows) :
Code Snippet
CREATE TABLE [dbo].[CommonPointValues](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[pointID] [bigint] NOT NULL,
[pointValue] [numeric](10, 2) NOT NULL,
[qualityID] [int] NOT NULL,
[dateFrom] [datetime] NULL,
[dateTo] [datetime] NULL,
CONSTRAINT [PK_PointValues] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
UPDATE statement:
Code SnippetUPDATE dbo.CommonPointValues SET dateTo = @last_update WHERE ID = @lastValID
2000 rows update takes about 1 sec.
INSERT statement:
Code Snippet
INSERT INTO dbo.CommonPointValues (pointID, pointValue, qualityID, dateFrom, dateTo )
VALUES (@point_id, @value_new, @quality_new, @last_update, @last_update )
Speed with INSERT is similar like with UPDATE.
I need about 10000 updates per 1 second and 10000 inserts per 1 second or together 5000 inserts and 5000 updates per 1 sec.
Should I update hardware or try some improvements to table structure.
View 9 Replies
View Related
Jan 23, 2004
I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
Mark
View 4 Replies
View Related
Feb 7, 2005
Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches........ I need to do it in batches as the input data is huge.....
The logic is I have to insert thru bcp in fact table...
After that batch execution for 50,000 thousand record.... wise.... if any of the batch failes i need to identify and have to rerun from that point onwards...... this is OLAP thing...
View 6 Replies
View Related
Oct 8, 2015
I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
View 4 Replies
View Related
Mar 31, 2015
How do you prevent this:"If you import a very large number of rows, dividing the data into batches can offer advantages. After each batch complete, the transaction is logged. If, for any reason, a bulk-import operation terminates before completion, only the current transaction (batch) is rolled back." How can I let the whole batch rollback, instead only the current transaction (batch) rolling back?
View 1 Replies
View Related
Apr 13, 2007
I'm using MS JDBC driver to connect to SQLServer 2005 and trying to perform batch insert. Here is the code i'm using:
Code Snippet
try {
// Assume we have a valid con
con.setAutoCommit(false);
// this is the simplified SQL for the purposes of this example
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO temp_batchOpt (");
sql.append("temp)");
sql.append(" VALUES (?)");
PreparedStatement insertStatement =
con.prepareStatement(sql.toString(),
SQLServerStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < 10; i++) {
insertStatement.setInt(1,3);
insertStatement.addBatch();
}
int[] r = insertStatement.executeBatch();
// the correct number of insertions are made
if(r != null)
for(int x: r)
System.out.println("inserted "+x);
SQLServerResultSet keyRS = (SQLServerResultSet)insertStatement.getGeneratedKeys();
while (keyRS.next()) {
int id = keyRS.getInt(1);
System.out.println("**filling id ="+id);
}
con.commit();
} catch (BatchUpdateException bue) {
bue.printStackTrace();
}
catch(SQLException sqle){
sqle.printStackTrace();
}
finally {
// close the connection...
}
I'm getting the following exception:
Code Snippetinserted 1
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(Unknown Source)
at com.x.U.m.Transformer.prefetchData(Transformer.java:285)
at com.x.U.m.Transformer.init(Transformer.java:51)
at com.x.U.m.XMLParse.main(XMLParse.java:215)
I can get the getGeneratedKeys() to work fine with single update (not usign batch update). Is batch + getGenerated key not supported?
TIA
View 4 Replies
View Related
Oct 18, 2007
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
View 1 Replies
View Related
Sep 9, 2015
I have a full backup scheduled at 12.00AM ET and have a batch import on 11.55PM(5 min before full backup) which takes 30min to complete .Will the backup cover the data which is being imported?
View 2 Replies
View Related
Jul 20, 2005
I want to Insert 10000 rows at a time and commit in sql server , Isthere a way to do it if the source tables have no id fields ?What would be the most efficient method?ThanksAjay
View 1 Replies
View Related
Jan 16, 2008
I€™m facing a SQL batch insert issue on Windows 64 bit (IA-64) platform. I€™m using €˜Column-Wise Binding€™ of parameters. The example program hosted on Microsoft site (http://msdn2.microsoft.com/en-us/library/ms709287.aspx) returns error of SQL_NEED_DATA for batch insert on Windows 64 bit platform. The same code works fine on 32 bit platform. I€™m using SQL Server 2000 as the database and tried using both native SQL server ODBC driver and Merant 5.2 ODBC drivers. Any help on this will be greatly appreciated.
View 5 Replies
View Related
Feb 8, 2007
Hi,I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.What is the most efficient way to do this using C#2005 and mssql2000?Any good article or tutorial?
View 2 Replies
View Related
Oct 30, 2007
Hello Everybody,In my web application I have to insert html from editor. I have assigned the filed as "nvarchar". But after running the insert query it throws an error "A potentially dangerous Request.Form value was detected from the client
(oEdit1="<P><SPAN style="FONT..."). " I'm using WYSWYG editor. I'm now quite bit confused about the the field and the query. If you have any idea about the problem please help me out of this problem.Thanks in option.Regards--Arindam
View 3 Replies
View Related
Aug 21, 2005
HelloCan anyone help me translate this from access so that it can work in mssql(i need to get next value, but cannot use identity as if row is deleted,another must get new next column number which would be same as deleted one)Access;INSERT INTO tableSELECT(IIF(code<>Null,MAX(code)+1,1) AS code,0 AS usercodeFROM tableI tried this in mssql but will not work:INSERT INTO tableSELECTCASEWHEN code IS NULL THEN 1ELSE MAX(code)+1ENDAS code,0 AS usercodeFROM table
View 14 Replies
View Related
Dec 23, 2006
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Page.IsPostBack Then
binddata()
End If
End Sub
Sub binddata()
baglanti = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|datagoldbar.mdf;Integrated Security=True;User Instance=True")
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim cmd As New SqlCommand("INSERT INTO db " & _
"(ip,sehir,mesaj,email) VALUES (@ip, " & _
"@sehir,@mesaj,@email)", baglanti)
cmd.Parameters.Add("@ip", SqlDbType.NVarChar, 50).Value = isim.Text
cmd.Parameters.Add("@sehir", SqlDbType.NChar, 50).Value = sehir.Text
cmd.Parameters.Add("@mesaj", SqlDbType.NText).Value = mesaj.Text
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 150).Value = email.Text
baglanti.Open()
cmd.ExecuteNonQuery()
binddata()
End Sub
Server Error in
Object reference not set to an instance of an object.
where is mistake there?
View 4 Replies
View Related
Oct 31, 2007
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert.
Can some one Help me how can i do it..
THX
Sorry for my bad englisht
View 3 Replies
View Related
Jan 4, 2008
Hello there,I'm to asp.net, so please be patient :DMy question is, how do I simply add some data to my database? - With vb.net code, not a grid view or something like that..I want to connect to my database, insert some data to a table.It shouldn't be that hard?- Hope someone will take the 5 minutes, and help me :)Regards Jeppe
View 16 Replies
View Related
Jun 14, 2007
I created a link from Oracle to SQL server.
There is a trigger with insert statment in Oracle side.
I got error message as follow when trigger is invoked:
SQL> insert into test1 values ('zerbra','brazi');
insert into test1 values ('zerbra','brazi')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TRIGGER2", line 4
ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'
my simple trigger is as follows:
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');
END;
View 6 Replies
View Related
Nov 18, 2006
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
View 3 Replies
View Related
Jul 15, 2006
Hi,
anyone knows if there's an easy way to insert new GUIDs into uniqueidentifier cells when editing data in table on Management Studio Express? or Visual Studio 2005 Server Explorer? I can't find a shortcut, and copying from external tools is pain..
I suppose it's possible to build a VB macro in Visual Studio, would it work when editing table data?
Thanks in advance!
View 8 Replies
View Related
Jan 17, 2007
Greeting.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
P.S.S. Sorry for bad english.
View 1 Replies
View Related
Aug 7, 2007
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
View 4 Replies
View Related
Dec 5, 2006
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
Thanks,
Dave
View 1 Replies
View Related
Aug 11, 2004
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
View 2 Replies
View Related
Feb 10, 2008
Hi,
i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
hoping for your kind help guys. thanks :)
br
Frozenice
View 1 Replies
View Related
Sep 6, 2006
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies
View Related
Mar 24, 2008
Hi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
View 2 Replies
View Related
