At work we’re making an effort to contribute technical knowledge to a centralized IT wiki. I like that. Writing encouraged a good understanding of the technology and it’s benefits. I didn’t think I’d have much to write but I’m surprised how some things I take for granted as simple, others didn’t know and vice versa. This two part series is from those entries.
Normally, inserting rows into SQL server is quick and easy and done through a simple INSERT SQL statement. This is fine when saving data to one, two, or even a few rows. However, when it is necessary to insert larger sets of data, this method becomes not only functionally inadequate, but slow and clunky. In this entry (part two of a two-part series) I wanted to write about the second option we will look at for inserting large sets of data: using .NET’s
When it is necessary to insert more than about a 1000 rows of data, a TVP now begins to reach the limits of its performance gain. If we are using the TVP’s only for inserts, we can move up and dramatically increase performance by using .NET’s
SqlBulkCopy class. In addition to providing the functionally for large inserts, the
SQLBulkCopy class can also be used to copy large amounts of data between tables. With
SQLBulkCopy we can deal with millions of rows if need be. Here is an amazing whitepaper on the
SqlBulkCopy class’ performance: http://www.sqlbi.com/LinkClick.aspx?fileticket=svahq1Mpp9A%3d&tabid=169&mid=375
SQLBulkCopy class if fairly simple. The dataset you use must match the columns on the table. If the order or column names are a bit different, that’s okay since that can be handled with the
SqlBulkCopy class’ ColumnMapping property which is just for that. Here’s a .NET sample of using the
SqlBulkCopy class to update a table named
tblSIWellList from a table name
MyData within a DataSet:
Using objConnection As System.Data.SqlClient.SqlConnection = GetSQLConnection() objConnection.Open() Using bulkCopy As `SqlBulkCopy `= New `SqlBulkCopy`(objConnection) bulkCopy.DestinationTableName = "dbo.tblSIWellList" With bulkCopy.ColumnMappings .Add(New `SqlBulkCopyColumnMapping`("ID", "ID")) .Add(New `SqlBulkCopyColumnMapping`("EPD", "EPDate")) .Add(New `SqlBulkCopyColumnMapping`("Comments", "Comments")) .Add(New `SqlBulkCopyColumnMapping`("Date", "Date")) .Add(New SqlBulkCopyColumnMapping("RTP\_Date", "RTPDate")) End With bulkCopy.WriteToServer(ds.Tables("MyData")) End Using End Using
SqlBulkCopy class is designed to copy/insert a large number of rows, transactions are handled in batches. It is possible to specify how large each batch is (e.g. 5000 rows at a time) but by default a single transaction (“batch”) is used for all rows. When committing transaction in batches, a failed batch will only roll back the last active transaction in the batch. (This may not necessarily be all rows if a previous batch was successfully committed)
Considerations when using .NET’s SlqBulkCopy class
There are a few “gotcha”’s to keep in mind when using the
- When the source and destination table data types are different,
SqlBulkCopywill attempt to convert to the destination data type where possible but this will incur a performance hit.
- By default, PK’s are assigned by destination and are not preserved.
- By default, constraints are not checked and triggers are not fired. Also row-level locks are used. Changing these setting may affect performance.
Enjoy Reading This Article?
Here are some more articles you might like to read next: