Slow inserts using SQL Data Adapter

  • Thread starter Thread starter Chris Botha
  • Start date Start date
C

Chris Botha

I have a DataTable populated with 2000 new records, then call the Update of
the SqlDataAdapter to insert the records from the DataTable into SQL Server,
and this operation takes about 18 seconds, thus around 110 inserts per
second.
This was a VB6 app, now re-written in dotnet. The VB6 app just generated
vanilla "Insert Into" statements and did the inserts in about 10 seconds
(around 200 per second), same computer, same SQL Server.

I did some research on this and the popular remedy is to generate your own
SqlCommand for Insert/Update (not to let the SqlCommandBuilder do it, not
efficient, they say), so I did it. I am not sure if the authors of these
articles want to save milli-seconds, it still takes about 18 seconds.

Anyone knows how to speed this up?
 
Hi Chris,

Check out InsertCommand.CommandText - it is probably doing a select at the
end of insert to update the values with ones inserted.
 
Chris Botha said:
I have a DataTable populated with 2000 new records, then call the Update of
the SqlDataAdapter to insert the records from the DataTable into SQL
Server,
and this operation takes about 18 seconds, thus around 110 inserts per
second.
This was a VB6 app, now re-written in dotnet. The VB6 app just generated
vanilla "Insert Into" statements and did the inserts in about 10 seconds
(around 200 per second), same computer, same SQL Server.

I did some research on this and the popular remedy is to generate your own
SqlCommand for Insert/Update (not to let the SqlCommandBuilder do it, not
efficient, they say), so I did it. I am not sure if the authors of these
articles want to save milli-seconds, it still takes about 18 seconds.

Anyone knows how to speed this up?
Click to expand...

Before running DataAdapter.Update, start a SqlTransaction and enlist your
insert/update and delete commands. Then commit after the update. This will
prevent Sql Server from having to perform a costly log flush after each
insert.

Also prepare your commands.

David
 
Hi Miha,

Thanks for the answer.
No, as said, as per the articles I found, I build the InsertCommand myself.
There is no Select- or UpdateCommand commands on the adapter, and also no
SqlCommandBuilder used. I stepped through the code and examined the
InsertCommand after the update (in case something altered the command), but
it is as before the Update call.
The Adapter is not wizard generated, I guess the wizard may add a select at
the end of the InsertCommand?


Miha Markic said:
Hi Chris,

Check out InsertCommand.CommandText - it is probably doing a select at the
end of insert to update the values with ones inserted.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Chris Botha said:
I have a DataTable populated with 2000 new records, then call the Update of
the SqlDataAdapter to insert the records from the DataTable into SQL
Server,
and this operation takes about 18 seconds, thus around 110 inserts per
second.
This was a VB6 app, now re-written in dotnet. The VB6 app just generated
vanilla "Insert Into" statements and did the inserts in about 10 seconds
(around 200 per second), same computer, same SQL Server.

I did some research on this and the popular remedy is to generate your own
SqlCommand for Insert/Update (not to let the SqlCommandBuilder do it, not
efficient, they say), so I did it. I am not sure if the authors of these
articles want to save milli-seconds, it still takes about 18 seconds.

Anyone knows how to speed this up?
Click to expand...
Click to expand...
 
Hi Chris,

I guess your problem is not .net related.
Could you peek into SqlProfiler what's going on with vb4 and what's going on
with .net and compare the results?
..net should be theoretically faster if you are performing same
operations....

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Chris Botha said:
Hi Miha,

Thanks for the answer.
No, as said, as per the articles I found, I build the InsertCommand
myself.
There is no Select- or UpdateCommand commands on the adapter, and also no
SqlCommandBuilder used. I stepped through the code and examined the
InsertCommand after the update (in case something altered the command),
but
it is as before the Update call.
The Adapter is not wizard generated, I guess the wizard may add a select
at
the end of the InsertCommand?


Miha Markic said:
Hi Chris,

Check out InsertCommand.CommandText - it is probably doing a select at
the
end of insert to update the values with ones inserted.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Chris Botha said:
I have a DataTable populated with 2000 new records, then call the Update of
the SqlDataAdapter to insert the records from the DataTable into SQL
Server,
and this operation takes about 18 seconds, thus around 110 inserts per
second.
This was a VB6 app, now re-written in dotnet. The VB6 app just
generated
vanilla "Insert Into" statements and did the inserts in about 10
seconds
(around 200 per second), same computer, same SQL Server.

I did some research on this and the popular remedy is to generate your own
SqlCommand for Insert/Update (not to let the SqlCommandBuilder do it, not
efficient, they say), so I did it. I am not sure if the authors of
these
articles want to save milli-seconds, it still takes about 18 seconds.

Anyone knows how to speed this up?
Click to expand...
Click to expand...
Click to expand...
 
Holy Moly, the transaction did it. I had to run the test a number of times
to make sure what I see is what I see. It takes about 2 seconds to complete,
9 times faster than without the transaction.
 
Back
Top