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
SET NOCOUNT ON
GO

DECLARE @counter AS INT = 0

WHILE @counter < 1000000

  BEGIN

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

    SET @counter = @counter + 1
  END

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

BEGIN TRANSACTION

  WHILE @counter < 1000000
    BEGIN

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

      SET @counter = @counter + 1
    END

COMMIT

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!

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s