September 25, 2012 at 9:18 am
yuvipoy (9/25/2012)
I have missed the last 2 lines command.ExecuteNonQuery(); and command.Parameters.Clear(); statement in the code.
Yes, adding those two lines fixes that issue.
However, you will have very poor performance - you could get orders of magnitude improvement in time to insert by using a set-based method.
Tom
September 25, 2012 at 9:42 am
Using prepare and a loop will be fine for a very small number of rows, but it will not scale well. Using prepare is effectively the same as creating a stored procedure on the SQL Server that inserts one row at a time. The scalability issue is that each row requires a network round-trip between your application and the SQL Server; also auto-committing each row insert means SQL Server has to flush a log record to disk when each single row insert auto-commits.
For anything except a very small number of rows, you need to look at batching the inserts. This means submitting multiple rows (or all rows) in one or more steps to SQL Server. This results in many fewer network communications, and SQL Server can optimize the logging requirements, using a very fast bulk-load mode of operation.
There are multiple methods available, both within SQL Server (directly reading a text file, for example) and via .NET. SQL Server provides:
- The bcp utility http://msdn.microsoft.com/en-us/library/ms162802.aspx
- The BULK INSERT command http://msdn.microsoft.com/en-us/library/ms188365.aspx
- The OPENROWSET (BULK... command http://msdn.microsoft.com/en-us/library/ms190312.aspx
You can find an overview at http://msdn.microsoft.com/en-us/library/ms175937.aspx
From a .NET language connecting to SQL Server 2008, options for bulk/batch loading include:
- SqlBulkCopy http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
- Table-valued parameters http://msdn.microsoft.com/en-us/library/bb675163.aspx
You can find examples of using SqlBulkCopy and table-valued parameters all over the Internet. A couple of examples below:
http://orionseven.com/blog/2009/09/30/using-table-valued-parameters-in-sql-server-2008-and-c/
http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server
http://florianreischl.blogspot.co.nz/2009/11/table-valued-parametes-performance.html
As far as repeatable tests are concerned, you may find it sufficient to drop and recreate the destination table in SQL Server between each test. It is hard to argue that restarting SQL Server between tests is not a more thorough approach, however.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply