4 ms·

One optional 5th revision that can get even better performance for particularly large inserts: SqlBulkCopy[1] In my experience, it tends to pull ahead of table

One optional 5th revision that can get even better performance for particularly large inserts: SqlBulkCopy[1] In my experience, it tends to pull ahead of table-valued parameters somewhere between 5 and 10,000 rows. On the other hand, the way it does locking makes it less suitable for concurrent scenarios. [1]http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclien...

balakk 15y ago
Another issue is Bulkcopy sucks for error reporting and txn management. TVPs are an acceptable compromise for most situations; if only they would let us create a TABLE type based on an existing schema.
- emn13 15y ago
  How are they any worse for error reporting? Also, unless I'm mistaken, if you want to, you can enroll a bulk copy into a custom transaction for maximum control and essentially replicate the table-valued parameter behavior.
  - sams99 15y ago
    They are totally fine for error reporting imho, you can and should implement your own datareader so you can catch/handle errors while streaming data in
emn13 15y ago
The locking options can be controlled - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclien... Is there some other locking issue?
- bunderbunder 15y ago
  None of the locking options work for concurrent bulk inserts if there's an index on the table. Trying to do so invites a mess of deadlocking.
lusr 15y ago
And on SQL Server < 2008 where TVPs aren't an option and the data set is large enough (I was importing millions of rows from user uploaded CSV files) I've had good experiences building chunks of XML (couple thousand rows at a time) on the client side and shipping that off to the client as a single XML parameter. Although, in practice, when importing complex data in real-time there's a lot of experimentation required to determine where the real bottlenecks are and this article skims the surface of the complexity involved in a real import. For example, do you do your lookups inside your stored procedure or does your application layer deal with all of that and send off pre-prepared data? If the latter, you reduce the amount of time the table is locked whilst adding rows since there are no joins and lookups in the insert, and this may be more performant overall than the alternative. You can also reduce the duration of any transaction lock you may need to hold for importing all the data which ultimately makes the system more responsive during large imports. Furthermore the "prepare-first" approach makes error reporting more practical since it's generally easier to do and report validation in the application layer.

balakk 15y ago

Another issue is Bulkcopy sucks for error reporting and txn management. TVPs are an acceptable compromise for most situations; if only they would let us create a TABLE type based on an existing schema.

emn13 15y ago
How are they any worse for error reporting? Also, unless I'm mistaken, if you want to, you can enroll a bulk copy into a custom transaction for maximum control and essentially replicate the table-valued parameter behavior.
- sams99 15y ago
  They are totally fine for error reporting imho, you can and should implement your own datareader so you can catch/handle errors while streaming data in

emn13 15y ago

The locking options can be controlled - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclien... Is there some other locking issue?

bunderbunder 15y ago
None of the locking options work for concurrent bulk inserts if there's an index on the table. Trying to do so invites a mess of deadlocking.

lusr 15y ago

And on SQL Server < 2008 where TVPs aren't an option and the data set is large enough (I was importing millions of rows from user uploaded CSV files) I've had good experiences building chunks of XML (couple thousand rows at a time) on the client side and shipping that off to the client as a single XML parameter. Although, in practice, when importing complex data in real-time there's a lot of experimentation required to determine where the real bottlenecks are and this article skims the surface of the complexity involved in a real import. For example, do you do your lookups inside your stored procedure or does your application layer deal with all of that and send off pre-prepared data? If the latter, you reduce the amount of time the table is locked whilst adding rows since there are no joins and lookups in the insert, and this may be more performant overall than the alternative. You can also reduce the duration of any transaction lock you may need to hold for importing all the data which ultimately makes the system more responsive during large imports. Furthermore the "prepare-first" approach makes error reporting more practical since it's generally easier to do and report validation in the application layer.