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