SQL Server AUTOCOMMIT within a while loop
Published: 3rd May 2013
This is just a little tip in case you ever have to make a lot of updates or inserts using a while loop and don’t mind a short amount of blocking while you do it.

Basically within SQL Server the default is for the database engine to use AUTOCOMMIT. So, in the following example, there are 3 inserts into the temp table @temp… after each insert SQL Server will autocommit the transaction. Therefore, due to autocommit, this yields 3 commits…

use boredDBA
go

set nocount on

create table dbo.commitTest
(
            pk     integer primary key,
            s        varchar(100),
            i        integer
)

declare @counter int
set @counter = 1

while @counter < 4
begin
            insert into dbo.commitTest(pk, s, i)
            values (@counter, 'qwertyuiopasdfghjklzxcvbnmqwertyuio', @counter)
            set @counter = @counter + 1
end

drop table dbo.commitTest


However, if you put an explicit transaction around the loop then this bypasses autocommit and therefore the exact same code will only commit once. Therefore this is much faster, although it does maintain a lock on the inserted table until the transaction is committed.

To test the impact of this let’s use the same code, add a simple time indicator, and increase the number of inserts…

use boredDBA
go

set nocount on

declare @start datetime = current_timestamp

create table dbo.commitTest
(
            pk     integer primary key,
            s        varchar(100),
            i        integer
)

declare @counter int
set @counter = 1

while @counter < 100000
begin
            insert into dbo.commitTest(pk, s, i)
            values (@counter, 'qwertyuiopasdfghjklzxcvbnmqwertyuio', @counter)
            set @counter = @counter + 1
end

select DATEDIFF(ms, @start, current_timestamp)

drop table dbo.commitTest


This gives an output of having taken 25,453ms on my test machine…

Now run the same command but in an explicit transaction…

use boredDBA
go

set nocount on

declare @start datetime = current_timestamp

create table dbo.commitTest
(
            pk     integer primary key,
            s        varchar(100),
            i        integer
)

begin transaction

declare @counter int
set @counter = 1

while @counter < 100000
begin
            insert into dbo.commitTest(pk, s, i)
            values (@counter, 'qwertyuiopasdfghjklzxcvbnmqwertyuio', @counter)
            set @counter = @counter + 1
end

commit transaction

select DATEDIFF(ms, @start, current_timestamp)

drop table dbo.commitTest


In comparison, this only took 1,340ms to complete. This is a vast difference in time, all down to SQL Server committing all 100,000 transactions compared to just the one transaction.

Something worth considering if you have a similar while loop scenario in place anywhere in your system.
Comments:
NB: Comments will only appear once they have been moderated.