Tuning Tip: Always create the index after dumping the data in to temporary tables. Loading the data after index creation takes more time and causes index fragmentation
Let’s create two similar temporary tables.
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL DROP TABLE #Temp1 CREATE TABLE #Temp1 ( ID VARCHAR(30) ,VAL VARCHAR(50) ) IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL DROP TABLE #Temp1 CREATE TABLE #Temp2 ( ID VARCHAR(30) ,VAL VARCHAR(50) )
For #Temp1 : we will first create the index and then load the table with data
For #Temp2 : we will first load the data, then create the Index.
I am also going to run profiler to capture the stats.
CREATE INDEX IX_Temp ON #Temp1 ( ID ,VAL ) DECLARE @ID INT = 1 WHILE @ID <= 10000 BEGIN INSERT INTO #Temp1 VALUES ( RAND() ,RAND() ) SET @ID = @ID + 1 END GO DECLARE @ID INT = 1 WHILE @ID <= 10000 BEGIN INSERT INTO #Temp2 VALUES ( RAND() ,RAND() ) SET @ID = @ID + 1 END CREATE INDEX IX_Temp ON #Temp2 ( ID ,VAL )
Let’s look at the profiler data.
It is clear from profiler that when we are creating the index after loading the data in to temporary table, then it is taking less time in comparison to first creating the index then load the data.
Also, let’s check the Index fragmentation.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,avg_fragmentation_in_percent ,page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) WHERE i.NAME = 'IX_Temp' ORDER BY avg_fragmentation_in_percent DESC
The index on #Temp1 table is completely fragmented as data were loaded after index creation, so the index created on temp1 will not perform efficiently .
We should always create index after loading the data into temp table . It will make the insert faster and fragmentation on temporary table index low