

SQL Server: High performance inserts - dmoney67
http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

======
orcadk
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.Co...](https://github.com/ayende/rhino-
etl/blob/master/Rhino.Etl.Core/Infrastructure/SqlCommandSet.cs)), 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.

~~~
leif
"and if there's enough data, the relevant b-tree pages will most certainly be
hot"

That is not the case if you have indexes. Most secondary indexes are high-
entropy (if they weren't, you probably wouldn't be storing them, is an
emotional proof), so inserts on larger-than-memory data sets almost always
incur a disk I/O, in a b-tree, at least.

Shameless plug: I have a talk about how to deal with this:
<http://www.youtube.com/watch?v=q6BnG74FZMQ>

~~~
orcadk
I don't agree that most secondary indexes are high cardinality. Some
definitely are, but there are plenty of indexes with low cardinality, simply
used for quicker scanning of those relevant values.

First up, in his example we're talking about 10k rows with a size of 35 bytes.
At 8050 bytes of available space per page, that gives a fan-out of 8050/35 =
230. 10k/230*8kb = 350kb. At 350kb of inserts (hobt data, he doesn't go into
secondary indexes), memory is completely irrelevant - the only force going on
here is latency on writing the log to disk.

If we had a huge data set (as in, did not fit in memory, at all) with high
cardinality - sure, we'd have a lot of cold leaf level pages. With no further
info on his case, I can only assume most of the hobt and secondary indexes
will fit in memory. At worst we'll have to read a cold leaf level page into
memory to perform the addition in-memory.

As is there's no mention of even a clustered index, causing all of this to be
heap inserts which is arguably one of the fastest insert methods there are
(barring certain very special cases).

~~~
leif
I said most secondary indexes are high _entropy_.

If you have a large enough data set that you have to do a leaf read per insert
(or close to that often), it will kill your throughput.

------
bunderbunder
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.sqlclien...](http://msdn.microsoft.com/en-
us/library/system.data.sqlclient.sqlbulkcopy.aspx)

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

~~~
bunderbunder
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.

------
j_baker

        -- 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?

~~~
Cushman
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.

------
pikewood
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
> 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.

------
koide
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
bcp and SqlBulkCopy are using the same underlying db mechanism.

~~~
koide
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
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.

------
Umox
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.

------
sirfried
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
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.

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

