Saturday, December 09, 2006

The price of BEGIN TRANSACTION

A question came up in a newsgroup:
Is there any performance penalty for explicitly wrapping a single DML statement in BEGIN TRANSACTION / COMMIT brackets?

I did a quick benchmark:

CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE(@i < 10000) BEGIN
INSERT aa(i) VALUES(@i)
SET @i = @i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE(@i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@i)
SET @i = @i + 1
IF @@ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go

I ran the tests 3 times and I did not notice any significant differences in
neither of 4 counters. I would not worry about performance penalties of BEGIN TRAN in my environment.

0 Comments:

Post a Comment

<< Home