Lesson Learned #201: Performance comparison using INSERT with and without Begin Transaction

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a case that our customer is testing a bechmark script to measure the INSERT operation in an Azure SQL Database. This process will take different time depending on the database service tier selected, but, we could improve the performance reducing the roundtrips and also using an implicit transaction. 

 

We have the following script, deleting and creating the table every time and run multiple inserts in small batches.

SET NOCOUNT ON; IF OBJECT_ID('dbo.InsertTest') IS NOT NULL DROP TABLE dbo.InsertTest; GO CREATE TABLE dbo.InsertTest (c1 int) GO declare @i int; set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'first 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'second 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'third 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'fourth 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'fifth 10000 rows inserted'; IF OBJECT_ID('dbo.InsertTest') IS NOT NULL DROP TABLE dbo.InsertTest; GO

 

Using the query that we could find out in this article we could see that the wait stat is WRITE_LOG 

 

SELECT req.session_id , req.start_time , cpu_time 'cpu_time_ms' , object_name(st.objectid,st.dbid) 'ObjectName' , substring (REPLACE (REPLACE (SUBSTRING (ST.text , (req.statement_start_offset/2) + 1 , ( (CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST

 

Jose_Manuel_Jurado_0-1650959711851.png

 

However, what happen if we add an implict transaction in this script.

 

SET NOCOUNT ON; IF OBJECT_ID('dbo.InsertTest') IS NOT NULL DROP TABLE dbo.InsertTest; GO CREATE TABLE dbo.InsertTest (c1 int) GO begin transaction declare @i int; set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'first 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'second 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'third 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'fourth 10000 rows inserted'; ------------------------------------- set @i = 0 WHILE @i < 10000 BEGIN SET @i = @i + 1; INSERT INTO dbo.InsertTest(c1) VALUES(@i); END; print 'fifth 10000 rows inserted'; commit transaction IF OBJECT_ID('dbo.InsertTest') IS NOT NULL DROP TABLE dbo.InsertTest; GO

 

In this case, the WRITE_LOG will change to ASYNC_NETWORK_IO due to I'm running this script locally from my PC and not running in Azure Windows Machine, reducing the execution time significantly. 

Jose_Manuel_Jurado_1-1650959877299.png

 

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.