Interesting - Huge performance gain while inserting data

by Amol 2. November 2009 12:34

Initially I was populating date dimension in usual manner. It tooks lot of time to insert 4 years of date information in date dimension. I has to populate date dimension in loop as I wanted to populate various attributes for for it like year, month, week, fiscal week, date etc. I started investigating and found a solution to boost the performance. Here I am demonstrating the same situation by using sample table to insert the data.

 

This is optimum way of populating date dimension or any table which you are populating by running loop.

 

Try to insert the records in loop with and without transactions.


I tried this and got the huge performance difference. 

With transaction, execution completes with a second. And without transaction it take 659 seconds (11 minutes). 


GO

IF OBJECT_ID ('TempTable') IS NOT NULL
     DROP TABLE TempTable

 

GO

CREATE TABLE TempTable (ID INT IDENTITY(1,1), Name VARCHAR(1000))

GO

/********* Without transaction ***********/

GO

TRUNCATE TABLE TempTable

GO

DECLARE @Counter INT = 0

WHILE (@Counter <= 100000)
BEGIN

      INSERT INTO TempTable
      SELECT 'Name ' + CAST(@Counter AS VARCHAR)

      SET @Counter = @Counter + 1

END

GO

 

/********* With transaction – run this once above statement completed ***********/

TRUNCATE TABLE TempTable

DECLARE @Counter INT = 0

BEGIN TRANSACTION

WHILE (@Counter <= 100000
BEGIN

      INSERT INTO TempTable
      SELECT 'Name ' + CAST(@Counter AS VARCHAR)

      SET @Counter = @Counter + 1

END

COMMIT TRANSACTION

GO


 

Tags: , ,

SQL Server Tips & Tricks

Category

Recent Posts

Tag cloud