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 explicit 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
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.
Enjoy!