Sep 17, 2021
2 min read
SQL Server —insert performance
When you deal with large amount of data, every detail is important in terms of performance. Today I want to show you how insert time and log size changes depending on factors like indexes and TABLOCKX hint.
Our test table contains 0.5 billion rows and 5 columns.
Script bellow has been used to move data between two tables. In this case TABLOCKX hint is used. The second script differs only with lack of WITH (TABLOCKX) code.
USE [Stage_v2]
DECLARE @Start DATETIME2 = GETDATE()
DECLARE @Stop DATETIME2INSERT INTO dbo.GHCN_06 WITH (TABLOCKX) ([StationID], [Date], [Type], [Value], [Alpha2])
SELECT [StationID], [Date], [Type], [Value], [Alpha2]
FROM [Stage_v1].[dbo].[GHCN_01]
WHERE [Date] BETWEEN ‘1900–01–01’ AND ‘1910–12–31’SET @Stop = GETDATE()PRINT(CONCAT(‘Start: ‘, @Start))
PRINT(CONCAT(‘Stop: ‘, @Stop))
PRINT(CONCAT(‘Sec.: ‘, DATEDIFF(ss, @Start, @Stop)))
In two cases I’ve created indexes, clustered and non clustered.
CREATE INDEX IX_Date ON [dbo].[GHCN_05] ([Date])
CREATE CLUSTERED INDEX IX_Date ON [dbo].[GHCN_06] ([Date])Here is a summary table contains testing scenarios. As you see sometimes there is a big difference between log size and insert time depending on used index or hint.
