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.