5 ms·

SQL Server: High performance inserts

42 points by dmoney67 14y ago

bunderbunder 14y ago
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 14y 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 14y 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 14y 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 14y 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 14y 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 14y 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.
j_baker 14y ago
-- No longer has a DEFAULT constraint; this means we have to insert the date -- ourselves, but it reduces the work on SQL server kill_time datetime My gut reaction is that this is unnecessary micro-optimization. Does making SQL server come up with the date and time really make that big a performance difference?
- emn13 14y ago
  Not just that; it might well be slower. It's definitely possible to saturate a network connection with a bulk operation like this; less wire overhead matters. I suspect it's mostly just necessary to permit the delay between data generation and storage.
- deleted 14y ago
  [deleted]
- Cushman 14y ago
  I think you misread the code-- the datetime is still being generated in SQL, in the stored procedure, as GETDATE(). Nothing extra is going down the wire. The point is to remove the default constraint, and thus the check for null on every insert. May still be unnecessary, but seems like a fair optimization.
  - deleted 14y ago
    [deleted]
pikewood 14y ago
It's interesting to see how the code pulls it off. If you run Profiler while this runs, you'll see a table variable being created, and then an insert statement for each row into that temp table. My guess is that the main performance gain comes from inserting into a table that doesn't require checks against constraints, etc., but eventually the overhead for all those individual inserts means that SqlBulkCopy wins out. And, the need to define your UDTs is a bit of a pain. But to prevent a glut of UDTs in the db, I've tried to make common UDTs that would commonly be used, such as CREATE TYPE [dbo].[udt_IntTable] AS TABLE( [Item_Key] [int] NULL ) One more point: it's probably best practice to use the TypeName property when using these, as it's required when using a UDT against a parameterized statement that's not a sproc. param.TypeName = "item_drop_bulk_table_rev4";
- gnaritas 14y ago
  > My guess is that the main performance gain comes from inserting into a table that doesn't require checks against constraints My guess would it's because inserting into table variables isn't logged; doubt it has anything to do with constraints. This turns all those individual statements into a single bulk insert, a single larger log operation instead of many small ones.
orcadk 14y ago
There are quite a lot of inaccuracies in this post, as well as hints that don't really mean much. First up, the non-SQL-injection code doesn't specify the parameter types. As such, each length of monster_class will result in a different execution plan being compiled and cached by SQL Server. Granted, there's only 50 different kinds - but just specify the parameter types to avoid it. Sure, you could setup a VLAN, but we're lightyears from having that affect our performance at this point. Parser overhead is not an issue as long as parameter types are specified. The plan will be compiled and cached. The only advantage to stored procedures at this point is reduced network data as well as management. Page faults - Sure, but for inserts the data will most likely be in memory already as we're writing new data. And if there's enough data, the relevant b-tree pages will most certainly be hot. Varchars - Granted, avoid them unless you're storing string data. I have no idea where the "hundreds of bytes of of memory" comes from. SQL Server caches all data as pages, exact copies of what's stored on disk. Variable-length varchars have an overhead of exactly two bytes + 2/#varlengthCOlumns in total. Disk IO - The transaction log is never merged with the main data file. SQL Server uses write ahead logging, writing to the log first while modifying the data pages in memory. Now and then a checkpoint is run, writing all the dirty pages to the main data file. A log is only "merged" with the data file if the server has crashed and recovery is being run automatically during startup. "This increases the number of rows you can insert before SQL Server succumbs to memory pressure" - Since the article only deals with inserts, why are we talking about memory pressure? You'll get disk contention for sure, but memory won't realistically be an issue unless you're reading as well. There's an alternative to keeping the potentially long running transaction open - avoiding both SqlBulkCopy and transferring datatables. The BCL has a CommandSet notion that utilitizes a TDS feature that allows batches to be streamed to SQL Server. Unfortunately you'll have to use reflection to get at it (https://github.com/ayende/rhino-etl/blob/master/Rhino.Etl.Core/Infrastructure/SqlCommandSet.cs https://github.com/ayende/rhino-etl/blob/master/Rhino.Etl.Co...), but the code has been stable for several releases and is very simple to interface. It basically allows you to create normal SqlCommands and just send a large batch of them at once, all being executed atomically with minimal locking. Finally, with the micro optimizations being mentioned, I'm surprised he doesn't recommend to "SET NOCOUNT OFF". Overall, some good points with some inaccuracies. I have a hard time with the closing statement "As you can see, you don’t need to abandon SQL Server to get massive performance improvements.". These are decent improvements, but this is such a simple scenario that I find it pretty optimistic to let this define whether to abandon SQL Server or not. The RDBMS problem is rarely with inserting enough data, but with reading data at the same time while avoiding locking.
koide 14y ago
For really massive data inserts, bcp is the way to go. In general avoid bulk insertions in code, if at all possible. If that's not an option then go for SqlBulkCopy or other similar approach.
- sams99 14y ago
  bcp and SqlBulkCopy are using the same underlying db mechanism.
  - koide 14y ago
    I read somewhere I cannot find right now that bcp is faster than SqlBulkCopy. My experience tends to match this, but that very well might be due to other factors (network, wrong coding, different default values between SqlBulkCopy and bcp.) Do you have a source to back your statement up? That way I could stop looking for mine :)
    - sams99 14y ago
      I think funnily enough even though it is the same mechanism bcp is faster cause it is 100% native, I have seen native odbc outperform sqlclient by a factor of 2, would assume you would get the same for bulk operations. As always you are going to need to test. Now ... a hand coded c++ odbc implementation that uses the bulk insert services should be identical to bcp.
DonnyV 14y ago
Looking at this reminds me why I'm happy to work with MongoDB. I do not miss TSQL.
sirfried 14y ago
Well for the money I'd pay in licensing this, I can just pay for more servers and use MySQL or PostgreSQL. Not that those two are slow at all.
- pwaring 14y ago
  You could, but that solution isn't of much help to someone with an existing .NET/SQL Server application. Making a few tweaks is probably a lot cheaper than a wholesale change to a different database server.
Umox 14y ago
Uhm, or just use a document database without all that ugly procedural code that ruins testability and obstructs OO patterns and practices. SQL is quickly becoming a legacy product.

42 points by dmoney67 14y ago

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 14y 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 14y 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 14y 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 14y 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 14y 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 14y 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.

j_baker 14y ago

-- No longer has a DEFAULT constraint; this means we have to insert the date -- ourselves, but it reduces the work on SQL server kill_time datetime My gut reaction is that this is unnecessary micro-optimization. Does making SQL server come up with the date and time really make that big a performance difference?

emn13 14y ago
Not just that; it might well be slower. It's definitely possible to saturate a network connection with a bulk operation like this; less wire overhead matters. I suspect it's mostly just necessary to permit the delay between data generation and storage.
deleted 14y ago
[deleted]
Cushman 14y ago
I think you misread the code-- the datetime is still being generated in SQL, in the stored procedure, as GETDATE(). Nothing extra is going down the wire. The point is to remove the default constraint, and thus the check for null on every insert. May still be unnecessary, but seems like a fair optimization.
- deleted 14y ago
  [deleted]

pikewood 14y ago

It's interesting to see how the code pulls it off. If you run Profiler while this runs, you'll see a table variable being created, and then an insert statement for each row into that temp table. My guess is that the main performance gain comes from inserting into a table that doesn't require checks against constraints, etc., but eventually the overhead for all those individual inserts means that SqlBulkCopy wins out. And, the need to define your UDTs is a bit of a pain. But to prevent a glut of UDTs in the db, I've tried to make common UDTs that would commonly be used, such as CREATE TYPE [dbo].[udt_IntTable] AS TABLE( [Item_Key] [int] NULL ) One more point: it's probably best practice to use the TypeName property when using these, as it's required when using a UDT against a parameterized statement that's not a sproc. param.TypeName = "item_drop_bulk_table_rev4";

gnaritas 14y ago
> My guess is that the main performance gain comes from inserting into a table that doesn't require checks against constraints My guess would it's because inserting into table variables isn't logged; doubt it has anything to do with constraints. This turns all those individual statements into a single bulk insert, a single larger log operation instead of many small ones.

orcadk 14y ago

There are quite a lot of inaccuracies in this post, as well as hints that don't really mean much. First up, the non-SQL-injection code doesn't specify the parameter types. As such, each length of monster_class will result in a different execution plan being compiled and cached by SQL Server. Granted, there's only 50 different kinds - but just specify the parameter types to avoid it. Sure, you could setup a VLAN, but we're lightyears from having that affect our performance at this point. Parser overhead is not an issue as long as parameter types are specified. The plan will be compiled and cached. The only advantage to stored procedures at this point is reduced network data as well as management. Page faults - Sure, but for inserts the data will most likely be in memory already as we're writing new data. And if there's enough data, the relevant b-tree pages will most certainly be hot. Varchars - Granted, avoid them unless you're storing string data. I have no idea where the "hundreds of bytes of of memory" comes from. SQL Server caches all data as pages, exact copies of what's stored on disk. Variable-length varchars have an overhead of exactly two bytes + 2/#varlengthCOlumns in total. Disk IO - The transaction log is never merged with the main data file. SQL Server uses write ahead logging, writing to the log first while modifying the data pages in memory. Now and then a checkpoint is run, writing all the dirty pages to the main data file. A log is only "merged" with the data file if the server has crashed and recovery is being run automatically during startup. "This increases the number of rows you can insert before SQL Server succumbs to memory pressure" - Since the article only deals with inserts, why are we talking about memory pressure? You'll get disk contention for sure, but memory won't realistically be an issue unless you're reading as well. There's an alternative to keeping the potentially long running transaction open - avoiding both SqlBulkCopy and transferring datatables. The BCL has a CommandSet notion that utilitizes a TDS feature that allows batches to be streamed to SQL Server. Unfortunately you'll have to use reflection to get at it (https://github.com/ayende/rhino-etl/blob/master/Rhino.Etl.Core/Infrastructure/SqlCommandSet.cs https://github.com/ayende/rhino-etl/blob/master/Rhino.Etl.Co...), but the code has been stable for several releases and is very simple to interface. It basically allows you to create normal SqlCommands and just send a large batch of them at once, all being executed atomically with minimal locking. Finally, with the micro optimizations being mentioned, I'm surprised he doesn't recommend to "SET NOCOUNT OFF". Overall, some good points with some inaccuracies. I have a hard time with the closing statement "As you can see, you don’t need to abandon SQL Server to get massive performance improvements.". These are decent improvements, but this is such a simple scenario that I find it pretty optimistic to let this define whether to abandon SQL Server or not. The RDBMS problem is rarely with inserting enough data, but with reading data at the same time while avoiding locking.

koide 14y ago

For really massive data inserts, bcp is the way to go. In general avoid bulk insertions in code, if at all possible. If that's not an option then go for SqlBulkCopy or other similar approach.

sams99 14y ago
bcp and SqlBulkCopy are using the same underlying db mechanism.
- koide 14y ago
  I read somewhere I cannot find right now that bcp is faster than SqlBulkCopy. My experience tends to match this, but that very well might be due to other factors (network, wrong coding, different default values between SqlBulkCopy and bcp.) Do you have a source to back your statement up? That way I could stop looking for mine :)
  - sams99 14y ago
    I think funnily enough even though it is the same mechanism bcp is faster cause it is 100% native, I have seen native odbc outperform sqlclient by a factor of 2, would assume you would get the same for bulk operations. As always you are going to need to test. Now ... a hand coded c++ odbc implementation that uses the bulk insert services should be identical to bcp.

DonnyV 14y ago

Looking at this reminds me why I'm happy to work with MongoDB. I do not miss TSQL.

sirfried 14y ago

Well for the money I'd pay in licensing this, I can just pay for more servers and use MySQL or PostgreSQL. Not that those two are slow at all.

pwaring 14y ago
You could, but that solution isn't of much help to someone with an existing .NET/SQL Server application. Making a few tweaks is probably a lot cheaper than a wholesale change to a different database server.

Umox 14y ago

Uhm, or just use a document database without all that ugly procedural code that ruins testability and obstructs OO patterns and practices. SQL is quickly becoming a legacy product.