Sometimes you have to insert a bunch of data and you can’t use BCP or another bulk load method. When you do single row inserts, SQL Server wraps these inserts inside an implicit transaction. Did you know that if you use an explicit transaction that the inserts will be much faster? I touched upon this earlier in this post MongoDB vs. SQL Server – INSERT comparison part deux but since someone asked about this today, I decided to take another look with different run sizes as well

Let’s take a look. first create the following table

T-SQL
1
2
3
CREATE TABLE Sometest(id INT PRIMARY KEY, SomeCol VARCHAR(200), SomeDate DATETIME,SomeCol2 VARCHAR(200), SomeDate2 DATETIME,
SomeCol3 VARCHAR(200), SomeDate3 DATETIME,SomeCol4 VARCHAR(200), SomeDate4 DATETIME)
GO
CREATE TABLE Sometest(id INT PRIMARY KEY, SomeCol VARCHAR(200), SomeDate DATETIME,SomeCol2 VARCHAR(200), SomeDate2 DATETIME,
SomeCol3 VARCHAR(200), SomeDate3 DATETIME,SomeCol4 VARCHAR(200), SomeDate4 DATETIME)
GO

Now run the following code

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
TRUNCATE TABLE Sometest
DECLARE @start DATETIME = GETDATE()
SET NOCOUNT ON
--BEGIN TRAN
DECLARE @id INT =0
WHILE @id < 50000
BEGIN
    INSERT Sometest
    SELECT @id ,'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla111111',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla2222',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla3333',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla4444',GETDATE()
SET @id+=1
END
--COMMIT
SELECT DATEDIFF(ms,@start,GETDATE())
SELECT COUNT(*) FROM Sometest
TRUNCATE TABLE Sometest
DECLARE @start DATETIME = GETDATE()
SET NOCOUNT ON
--BEGIN TRAN
DECLARE @id INT =0
WHILE @id < 50000
BEGIN
	INSERT Sometest
	SELECT @id ,'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla111111',GETDATE(),
	'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla2222',GETDATE(),
	'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla3333',GETDATE(),
	'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla4444',GETDATE()
SET @id+=1
END
--COMMIT
SELECT DATEDIFF(ms,@start,GETDATE())
SELECT COUNT(*) FROM Sometest

That takes 1153 milliseconds on my machine

Run the same code but now uncomment the transaction

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
TRUNCATE TABLE Sometest
DECLARE @start DATETIME = GETDATE()
SET NOCOUNT ON
BEGIN TRAN
DECLARE @id INT =0
WHILE @id < 50000
BEGIN
    INSERT Sometest
    SELECT @id ,'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla111111',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla2222',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla3333',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla4444',GETDATE()
SET @id+=1
END
COMMIT
SELECT DATEDIFF(ms,@start,GETDATE())
SELECT COUNT(*) FROM Sometest
TRUNCATE TABLE Sometest
DECLARE @start DATETIME = GETDATE()
SET NOCOUNT ON
BEGIN TRAN
DECLARE @id INT =0
WHILE @id < 50000
BEGIN
	INSERT Sometest
	SELECT @id ,'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla111111',GETDATE(),
	'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla2222',GETDATE(),
	'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla3333',GETDATE(),
	'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla4444',GETDATE()
SET @id+=1
END
COMMIT
SELECT DATEDIFF(ms,@start,GETDATE())
SELECT COUNT(*) FROM Sometest

That is almost twice as fast (or almost half as slow), it takes 673 milliseconds

Here is what the numbers look like on my machine for different insert sizes

Inserts	no tran	explicit transaction
100000  18030   10800
50000	 9363	 5516
5000	 1130	  760
5000	 190	  103
1000	  40       23

As you can see, when you have an explicit transaction it is much faster than when you don’t specify a transaction