Thursday, July 2, 2015

Transaction in SQL Server


Transaction in SQL Server

Executing below piece of SQL Code, we will know why we should use Transaction in SQL Server.
according to below code when we execute insert tSQL script with transaction it gives around 25 times more performance comparing to without Transaction tSQL Script.

we should always have Transaction scope AS SMALL AS POSSIBLE based on requirements, as it locks the resources which are part of the transactions till the Transaction is COMMIT/ROLLBACK.

having the transaction with small scope help us to get better performance, fewer locking/blocking and better data/referential integrity.


CREATE TABLE TestTran
(
TranId INT IDENTITY(1, 1),
TranName VARCHAR(100)
)

SET NOCOUNT ON

DECLARE @StartTime1 DATETIME
DECLARE @StartTime2 DATETIME
DECLARE @EndTime1 DATETIME
DECLARE @EndTime2 DATETIME

/*Start SQL Code With Transaction*/
SET @StartTime1= Getdate()
DECLARE @WithTranI INT =1
BEGIN TRAN

WHILE ( @WithTranI <= 100000 ) /*>*/
BEGIN
INSERT INTO TestTran
(TranName)
SELECT 'Transaction ' + Cast(@WithTranI AS VARCHAR)

SET @WithTranI =@WithTranI + 1
END
COMMIT
SET @EndTime1= Getdate()

SELECT Datediff(millisecond, @StartTime1, @EndTime1) AS TimediffWithTranInMilliSecond
SELECT Datediff(second, @StartTime1, @EndTime1) AS TimediffWithTranInSecond
/*End SQL Code With Transaction*/


/*Start SQL Code Without Transaction*/
SET @StartTime2= Getdate()
DECLARE @WithOutTranI INT =1

WHILE ( @WithOutTranI <= 100000 ) /*>*/
BEGIN
INSERT INTO TestTran
(TranName)
SELECT 'Transaction ' + Cast(@WithOutTranI AS VARCHAR)

SET @WithOutTranI =@WithOutTranI + 1
END

SET @EndTime2= Getdate()

SELECT Datediff(millisecond, @StartTime2, @EndTime2) AS TimediffWithOutTranInMilliSecond
SELECT Datediff(second, @StartTime2, @EndTime2) AS TimediffWithOutTranInSecond
/*End SQL Code Without Transaction*/


DROP TABLE TestTran

GO


Note: Please execute this script in development/test environment and use transaction scope wisely.

No comments :

Post a Comment