Performance, SQL Server

SQL Server: Inserting test data FAST

You’ll sometimes want to create a table quickly in SQL Server to test some concept or feature. If you’re anything like me you’ll quite often want to do so with a reasonable amount of data.

The brute force approach to loading a table in this scenario might be to use simple while loop. When you’re starting out with SQL Server you might be tempted to do something like this:

-- Disable notifications of row inserts for a small performance boost

DECLARE @counter AS INT = 0

WHILE @counter < 1000000


    INSERT INTO tblData (Data, MoreData)
    VALUES ( 'Data',  'MoreData')

    SET @counter = @counter + 1

Can’t argue with that for simplicity. This approach is effective, but won’t get you finished in a hurry. On my laptop, a while loop insert of 1 million rows takes around 4 minutes 50 seconds. 

Now I like reasonable test volumes – but I don’t like waiting the best part of 5 minutes to get them.

There are a few more adventurous measures you can take to improve this dramatically, but in terms of keeping things simple and effective you can’t beat simply wrapping the WHILE loop in an explicit transaction:

DECLARE @counter AS INT = 0


  WHILE @counter < 1000000

      INSERT INTO tblData (Data, MoreData)
      VALUES ( 'Data', @counter + 1)

      SET @counter = @counter + 1


With that tiny change, you have turned a 1 million transaction operation into a single transaction. 

In my environment that drops the operation from approaching 5 minutes down to 18 seconds

I know it seems obvious but I’ve seen numerous developers forget about the advantages of an explicit vs an implicit transaction at the right moment

Not bad for adding 3 words!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s