Inserting Several thousand rows

B

barski

Well-known member
Joined
Apr 7, 2002
Messages
239
Location
Tennessee
i have this project that occassionally the user will insert several thousand rows. currently that ranges from about 3000 to 6000 on the high end and it is highly unlikely that it will ever exceed 15000.

these items happen to be a collection of objects so first i tried

foreach(object obj in myObjects)
{
//sqlinsertcommand
}

that took about 52 seconds to run which is unacceptable

so then i added the collection to a datatable and executed the dataadapter.update

that knocked it down to about 13 seconds.

which is almost acceptable in fact it probably would pass but there has to be a faster way. Anyone have any suggestions. On a side note, i dont understand why using the dataadapter.update is so much faster than executing an Command.ExecuteNonQuery for each object in the collection.
 
The command.ExecuteNonQuery makes each insert individually, the dataAdapter update runs it in a batch, much less initialization data for each insert.

Are you using Sql Server? If so, the bulk copy program is probably the fastest way...http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/08/19/22566.aspx

otherwise, how about creating a stored procedure for the insert and assigning that as the insertcommand for the dataAdapter. Also, use a counter variable to add the objects to the dataTable, as using the foreach statement retrieves the enumeration property each time.
 
for anyone interested. i passed an xml string(dataset.WriteXML) to a stored procedure and using openxml it cut the time down to about a 1.5 seconds for 5100 rows. quite a considerable improvement.
 
Back
Top