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

User defined function to split string by delimiter

by Amol 10. September 2009 11:53

In SQL Server, there is no inbuilt function to split the string by delimiter. If there is need to split string, user defined function has to be created for this task.

Main purpose of split function is to split given string by given delimiter. Here is the user defined SQL Server split function.

CREATE FUNCTION [dbo].[Split]
(   
    @InputString VARCHAR (MAX), 
    @Delimiter VARCHAR (10)
)
RETURNS @TempTable TABLE ([Id] INT IDENTITY (1, 1) NOT NULL,
                          [Values] VARCHAR (8000) NULL)
AS
BEGIN
    DECLARE  @Length INT
            ,@Index INT
            ,@LastIndex INT
            ,@Counter INT
    SET @InputString = @InputString + @Delimiter
    SET @Length = len(@InputString)
    SET @Index = 1
    SET @Counter = 1
    WHILE (@Counter < @Length)
    BEGIN
        IF charindex(@Delimiter, @InputString, @Index) > 0 
        BEGIN
            SET @lastIndex = charindex(@Delimiter, @InputString, @Index) - @Index
            INSERT INTO @TempTable ([Values]) 
            SELECT substring(@InputString, @Index, @LastIndex)
            SET @Index = charindex(@Delimiter, @InputString, @Index) + len(@Delimiter)
        END
        SET @Counter = @Counter + 1
    END
RETURN
END

 

Tags: ,

SQL Server Scripts

Update column in sequential order

by Amol 16. August 2009 11:59

This example can be very useful if you would want to add sequence number to a column.Below example will update the table with sequence number even if the table contains duplicate values.

See the example below:

--Create the sequence table

CREATE TABLE Sequence (ID INT, [Name] VARCHAR(100))

GO

--Insert the data into Sequence table

INSERT INTO Sequence (ID, [Name])

SELECT NULL, 'xyz1' UNION ALL
SELECT NULL, 'xyz2' UNION ALL
SELECT NULL, 'xyz3' UNION ALL
SELECT NULL, 'xyz4' UNION ALL
SELECT NULL, 'xyz5' UNION ALL
SELECT NULL, 'xyz6' UNION ALL
SELECT NULL, 'xyz6' UNION ALL
SELECT NULL, 'xyz6' 

 GO

 

--Update the Sequence table with the value of ID

DECLARE @ID INT
SET @ID =
UPDATE Sequence SET @ID = ID = @ID + 1

--Query Sequence table

SELECT *
FROM Sequence;


Tags: , ,

SQL Server Scripts

Tag cloud