I have a write-heavy application in which my initial testing has shown that one large insert into a table with four "large" columns is significantly faster than four inserts (in a single transaction) into four separate tables, each having one "large" column. For example,

  id int, 
  col1 varbinary(8000),
  col2 varbinary(8000),
  col3 varbinary(8000),
  col4 varbinary(8000)


CREATE TABLE LittleTable1 (id int, col varbinary(8000))
CREATE TABLE LittleTable2 (id int, col varbinary(8000))
CREATE TABLE LittleTable3 (id int, col varbinary(8000))
CREATE TABLE LittleTable4 (id int, col varbinary(8000))

where the id field in each of the above tables is auto-increment and a clustered index and the tables are modified only by INSERTs. I am seeing something on the order of 3x slower insert speeds for the four tables despite the fact that the amount of data written in both cases is nearly identical. I am not surprised that four separate inserts are slower than one large insert, but 3x slower? Is this a fundamental characteristic of SQL Server or should I be looking for some kind of flaw in either my table structure or testing methodology?

EDIT: Adding more detail about my testing methodology. The inserts for both sets of tables were contained in stored procedures. For the one big table, the procedure was a fairly trivial:

  @val1 varbinary(8000), @val2 varbinary(8000), @val3 varbinary(8000), @val4 varbinary(8000)
) AS
    INSERT INTO BigTable (col1, col2, col3, col4) VALUES (@val1, @val2, @val3, @val4);

For the smaller tables, the procedure was not much more complicated:

CREATE PROCEDURE InsertMultipleSmall (
  @val1 varbinary(8000), @val2 varbinary(8000), @val3 varbinary(8000), @val4 varbinary(8000)
) AS
    INSERT INTO LittleTable1 (col) VALUES (@val1);
    INSERT INTO LittleTable2 (col) VALUES (@val2);
    INSERT INTO LittleTable3 (col) VALUES (@val3);
    INSERT INTO LittleTable4 (col) VALUES (@val4);

I did not collected wait statistics or track log autogrow events. I did perform the test with 10k-50k iterations 5-10 times and saw a very consistent performance differential between the two each time.

  • 2
    So in your tests, do you loop through and insert a row into A, a row into B, a row into C, etc.? That could be very different than 1000 rows into A, 1000 rows into B, 1000 rows into C, etc. And 1000 individual inserts is much different from insert with 1000 VALUES() or various bulk insert methodologies. Could you share a little more about how you're actually testing this? Have you collected wait statistics to see where the time is being spent? Have you tracked things like discrepancies in frequency of log autogrow events? And have you thought about separate transactions? – Aaron Bertrand Nov 14 '13 at 3:02
  • @AaronBertrand, I edited my question to add more detail about the way I did my testing. While I did consider transactions, I did not collect wait stats or check log growth events. – Dan Nov 14 '13 at 3:28
  • 4
    Well, for one, the second procedure is going to touch at least 4 pages every time (more if any of the inserts cause page splits), while the first may only touch one (depending on how much data is actually in the values and whether they fit on an existing page). This is not counting for indexes and also the fact that for every four values, in the first case, you are generating/writing one 4-byte integer, and in the second case, you are generating 4 of them. Typically splitting out into separate tables will help only if you can put them on different physical disks and process them in parallel... – Aaron Bertrand Nov 14 '13 at 3:33
  • @AaronBertrand Thanks, very helpful. I'll see if it runs any faster with the tables allocated to filegroups on different disks. – Dan Nov 14 '13 at 3:35
  • 1
    it may run a little faster, but I suspect you'll still be contending with bottlenecks such as a single transaction and writing to a single transaction log. I really think if you're looking for speed you should be looking at ways to avoid inserting 50,000 rows, one (or four) row(s) at a time. – Aaron Bertrand Nov 14 '13 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.