@@TRANCOUNT = 0, Begin Transaction, and Performance

Question

@@TRANCOUNT = 0, Begin Transaction, and Performance

Boise

SSC Veteran

Points: 266
More actions
May 28, 2008 at 4:14 pm

#207825

I am told that Proc #2 is more efficient over Proc #1 due to the Begin/Commit when Proc(#1 or #2) is being called and is not part of a larger transaction (Only a singe resord is to be inserted).
The Scenario is that the Proc (#1 or #2) is called from a WebPage/WinForm and is only inserting one record. A Begin Transaction is **NOT** done by the WebPage/WinForm so Proc #1 will have @@TRANCOUNT = 0 when called.
The goal is to enable the the procedure to have the capability of being part of a larger transaction, one that is started by the webpage/winform of another proc.
Implicit Transactions Off / Auto Commits are on.
Can someone shed some light on this.
Proc #1:
*******************************
SET NOCOUNT ON;
Declare @TranStarted bit
Set @TranStarted = 0
If ( @@TRANCOUNT = 0 )
Begin
BEGIN TRANSACTION
Set @TranStarted = 1
End
Else
Set @TranStarted = 0
INSERT INTO [dbo].[XXXXX]
(
[XXXXX].[ID]
,[XXXXX].[Lname]
,[XXXXX].[Fname]
)
VALUES
(
@d
,@Lname
,@Fname
)
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
*******************************
Proc #2:
*******************************
SET NOCOUNT ON;
INSERT INTO [dbo].[XXXXX]
(
[XXXXX].[ID]
,[XXXXX].[Lname]
,[XXXXX].[Fname]
)
VALUES
(
@d
,@Lname
,@Fname
)
*******************************

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

If you're just doing a single insert, there's no need for an explicit transaction. All statements run within a transaction that starts when th statement starts and autocommits if the statement succeeds.

You need explicit transactions when you need more than 1 statement to run as atomic operations

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Boise SSC Veteran Points: 266 More actions · Answer 2

Thanks for the reply Gail.

The question: "Is there a Performance Difference?"

I am looking to shed light on the performance end not on whether you should have the @@TransCount Logic in a Proc that does only one transaction. I realize that it would not be needed if auto commits are on (which they normally are for most environments); for simplicity, I used an example that only had one statement which would start a transaction (Insert). This is not a question of best/preferred practices. If this was the case I could ask 3 people and get 7 different answers .

What I was told is that proc #1 takes a performance hit because it will be doing a Begin Transaction and Commit Transaction when @@TransCount = 0

For Proc #2, with the Auto Commit On, Begin Transaction and a Commit Transaction will still occur (implicitly - behind the scenes) and therefore the there should not be any meaningful, if any, performance difference between the two procedures.

I believe you can have IMPLICIT_TRANSACTIONS OFF (which isn't the default and I haven't seen any place set up SQL that way yet); if this were the case, my understanding is that proc #1 will work, proc #2 will not. So it could be argued that Proc #2 is a better practice, but this is not a best practices question.

The @@TransCount logic is simply preventing nested transactions (yes the proc would probably have more than one Insert).

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Best way to see if there's a performance difference - test and measure.

Boise (5/29/2008)
I believe you can have IMPLICIT_TRANSACTIONS OFF (which isn't the default and I haven't seen any place set up SQL that way yet);

Implicit transacions is by default off. Implicit transactions means that any data modification starts a transaction but does not commit it. You have to explicitly write the commit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 4

In my tests, the explicit begin/commit won't add any performance overhead, but you should test both and see. I don't think I've ever relied on someone else's performance tests without confirming them myself.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

noeld SSC Guru Points: 96590 More actions · Answer 5

I have had this kind of questions in the past and the answer has always been (so far) transaction handling code overhead is negligible!!!

Just my $0.02

* Noel