Insert Performance

[Andrew Robinson]

I need to insert roughly 10,000 rows from a text log file into SQL. Ideally,
I will use ADO as there is a bit of pre-processing before the inserts. I am
guessing this will eliminate Bulk Copy.

In general, what is the fastest method of inserting rows into SQL?

SqlCommand ("INSERT INTO...." cn) or build a DataTable / DataSet and do an
update with a DataAdaptor?

Should I look at Async in Whidbey?



-Andrew

 

[David Browne]

I need to insert roughly 10,000 rows from a text log file into SQL.
Ideally,
I will use ADO as there is a bit of pre-processing before the inserts. I
am
guessing this will eliminate Bulk Copy.

In general, what is the fastest method of inserting rows into SQL?

SqlCommand ("INSERT INTO...." cn) or build a DataTable / DataSet and do an
update with a DataAdaptor?

Should I look at Async in Whidbey?

DataAdapter.Update uses a plan SqlCommand for inserts, so there's no real
difference. BULK INSERT is difficult do since you need to push the file
somewhere where the SQL Server can see it.

On a good network you can get close to 1000 inserts/sec using plain old
INSERT INTO ... VALUES. Remember to wrap the inserts in a transaction to
prevent SQL Server from having to flush the log after each row.

In Whidbey you would use System.Data.SqlClient.SqlBulkCopy.

David

 

[W.G. Ryan eMVP]

Bulk copy is going to be faster, as is any mechanism that cuts ADO.NET out
of the loop. The reason I say this is that as a d loading mechanism, much
of hte benefits that ADO.NET affords are irrelevant (for instance, you
aren't going to have 25k clients connecting simultaneously doing bulk loads
when you are loading data like you are)

A data adapter calls Insert Into when it sees a rowstate that's added so
there isn't going to be a difference here other than coding simplicity. In
Whidbey there are batch updates which enhance performance, but the
asncy/mars stuff isn't going to have any impact that I know of and I really
doubt that it will.

 

[Andrew Robinson]

Wrapping my data inserts inside of a transaction improve performace about
300%. My guess is that the log file is not being flushed after each insert?

-Andre

 

[Kevin Yu [MSFT]]

Hi Andrew,

Yes, as you know, log file is not being flushed each time data is inserted
if you wrap your inserts inside a transaction.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."