Overhead for transactions

Hi all,
I am looking at the following codes. I thought that
defining the transaction is redundant. Delete by itself
IS a transaction. But what I don't know is: how much
extra overhead is added by explicitly defining the
tranaction?
Thanks in advance, Anna
--
BEGIN TRAN Del_C_ActiveBenefits
DELETE dbo.C_ActiveBenefits
WHERE ClientCode = @.ClientCode
AND PlanCode = @.PlanCode
AND ValDate = @.ValDate
If (@.@.ERROR <> 0)
BEGIN
Rollback TRAN Del_C_ActiveBenefits
--initialize Error Num from global variable
SET @.Error_Num = @.@.ERROR
-- initialize the Error description
SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
Data From Target Table'
GOTO Error_Handler -- trap & handle the Error
generated
END
Commit TRAN Del_C_ActiveBenefits
--There's no extra overhead. You might end up in carrying locks over longer time, so you can
experience higher risk of blocking of course. Also, grouping several DML commands in one transaction
can increase performance as it will reduce I/O (each commit, implicit or explicit result in an I/O
to the t-log file). However, you don't want to do too many DML's in one transaction, say in a back
job, perhaps limit to 10000 DML's in one transaction.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all,
> I am looking at the following codes. I thought that
> defining the transaction is redundant. Delete by itself
> IS a transaction. But what I don't know is: how much
> extra overhead is added by explicitly defining the
> tranaction?
> Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode = @.ClientCode
> AND PlanCode = @.PlanCode
> AND ValDate = @.ValDate
> If (@.@.ERROR <> 0)
> BEGIN
> Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num = @.@.ERROR
> -- initialize the Error description
> SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
> Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error
> generated
> END
> Commit TRAN Del_C_ActiveBenefits
> --|||In addition to Tibor's comments may I suggest you review the error =handling code.
The line SET @.Error_Num =3D @.@.ERROR is after the rollback and hence =@.@.error will have been rest to zero by the rollback, so whatever is =using @.error_num is going to get zero. Safest way normally is imediately =after the statement you want to trap include:
Set @.errno =3D @.@.error,@.rcount =3D @.@.rowcount -- or similar names for =the variables
Then check @.rcount and/or @.errno for whatever you need.
Mike John
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message =news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all, > I am looking at the following codes. I thought that > defining the transaction is redundant. Delete by itself > IS a transaction. But what I don't know is: how much > extra overhead is added by explicitly defining the > tranaction?
> > Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode =3D @.ClientCode
> AND PlanCode =3D @.PlanCode
> AND ValDate =3D @.ValDate
> > If (@.@.ERROR <> 0)
> BEGIN > Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num =3D @.@.ERROR > -- initialize the Error description > SET @.Error_Desc =3D 'Error Deleting C_ActiveBenefits > Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error > generated > END > > Commit TRAN Del_C_ActiveBenefits
> --