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 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, 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.

--

--

--

Data | BI Engineer (Microsoft Stack)

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to enable access logs by default for ELB in ElasticBeanstalk deployments

Scaling Python Asyncio with Ray

LeetCode 53. Maximum Subarray — Python Solution

Python Basics: 03 introduction variable

IT Leadership, COVID is the catalyst to changing your business.

The Exothermic Project | Part 1

DevOps Engineering Concepts for Beginners

Another One Of My Favorite Leet Code Question

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michal Molka

Michal Molka

Data | BI Engineer (Microsoft Stack)

More from Medium

Basic PostgreSQL Commands

Connecting to Visual Studio Code with PostgreSQL : Running Queries

SQL Fundamental

Containers everywhere: how to run Microsoft SQL Server, SQL tools and Azure Data Studio with…