SQL Server —insert performance

When you deal with a 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 a TABLOCKX hint.

Our experimental table contains 0.5 billion rows and 5 columns.

A script bellow has been used to move data between two tables. In this case, TABLOCKX hint is used. A second script differs only with a lack of WITH (TABLOCKX) code.

USE [Stage_v2]
DECLARE @Start DATETIME2 = GETDATE()
DECLARE @Stop DATETIME2
INSERT 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, a clustered and a 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 which contains testing scenarios. As you see, sometimes there is a big difference between log size and insert time depending on the used index or a hint.