1.9 million line transaction in profiler with no begin end using WAITFOR

1.9 million line transaction in profiler with no begin end using WAITFOR

Post by cheetah2 » Thu, 07 Sep 2006 01:41:51


Need some help...

Server is WinOS 2000 Advanced Server
SQL is 2000 sp3a collation 437_CI_AS

I have a developer who is using the following code (this is only a
subset and all confidential data has been changed) to move members
around. DBCC OPENTRAN shows 1,2,1,2,1,2... for this spid. Profiler
only listed one record with 1.9 million lines of code attached to it.
There are no begin..ends in there. We are trying to figure out how
opentran is showing two open transactions. I would think that it would
show 0,1,0,1,0,1.... I understand that the waitfor's are their own
transaction.

Any help on this would be greatly appreciated.

set nocount on

declare @company_new smallint
set @company_new = 1

---- AH_MEMBER ----
declare @AH_MEMBER__ACCOUNT__map table (ACCOUNT_old BigInt, ACCOUNT_new
BigInt)

declare @AH_MEMBER__ACCOUNT BigInt

exec dbo.spv_GetNewAcctNum @company_new,
@AH_MEMBER__ACCOUNT output

select @AH_MEMBER__ACCOUNT ACCOUNT_new

insert AH_MEMBER (
[COMPANY], [ACCOUNT], [PAYOR_ACCOUNT], [FIRST_NAME] , [LAST_NAME])
values (
@company_new, @AH_MEMBER__ACCOUNT, '0000000', 'DOE', 'JOHN')

if @@error = 0 insert @AH_MEMBER__ACCOUNT__map values (0000000,
@AH_MEMBER__ACCOUNT)

WAITFOR DELAY '0:00:00.000'

---- AH_ACTIVITY_LOG ----
declare @AH_ACTIVITY_LOG__Account BigInt

select @AH_ACTIVITY_LOG__Account = ACCOUNT_new from
@AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000
if @@rowcount = 0 set @AH_ACTIVITY_LOG__Account = 0

insert AH_ACTIVITY_LOG (
[Company] , [Account], [UserID], [DateTimeIn], [DateTimeOut]) values (
@company_new, @AH_ACTIVITY_LOG__Account, 000, '01/01/2004
01:00:00.000', '01/01/2004 01:00:00.001')

WAITFOR DELAY '0:00:00.000'

---- AH_MEMBER_OPEN ----
declare @AH_MEMBER_OPEN__hs_refnbr__map table (hs_refnbr_old BigInt,
hs_refnbr_new BigInt)

declare @AH_MEMBER_OPEN__account BigInt
declare @AH_MEMBER_OPEN__inscode_id Int

select @AH_MEMBER_OPEN__account = ACCOUNT_new from
@AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000
if @@rowcount = 0 set @AH_MEMBER_OPEN__account = 0

select @AH_MEMBER_OPEN__inscode_id = inscode_id_new from
#inscode_id_map where inscode_id_old = 000
if @@rowcount = 0 set @AH_MEMBER_OPEN__inscode_id = 000

insert AH_MEMBER_OPEN (
[company] , [account], [inscode_id]) values (
@company_new, @AH_MEMBER_OPEN__account, @AH_MEMBER_OPEN__inscode_id)

if @@error = 0 insert @AH_MEMBER_OPEN__hs_refnbr__map values (000000,
scope_identity())

WAITFOR DELAY '0:00:00.000'

set nocount off
 
 
 

1.9 million line transaction in profiler with no begin end using WAITFOR

Post by John Bel » Thu, 07 Sep 2006 02:51:15

Hi

With 1.9 million lines of code it could probably do with refactoring! If
this is a stored procedure have you looked at using the de *** in QA to
run this, then you can have a watch on @@TRANCOUNT.

At a guess you have a trigger that has incremented the TRANCOUNT.

John

 
 
 

1.9 million line transaction in profiler with no begin end using WAITFOR

Post by cheetah2 » Thu, 07 Sep 2006 03:04:25

his is not a stored procedure but generated code from an application.
We have no triggers in our databases.

Thanks, though.


John Bell wrote:

 
 
 

1.9 million line transaction in profiler with no begin end using WAITFOR

Post by John Bel » Thu, 07 Sep 2006 17:31:33

i

You could always post the statements into QA and add some SELECT
@@TRANCOUNTs after each statement. It seems an awful lot to be doing as a
single batch, it seems that you are effectively using a cursor to process
items one at a time rather than using a set based solution. You may also
want to look at using identity values to generate your manufactured keys.

John

< XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

1.9 million line transaction in profiler with no begin end using WAITFOR

Post by cheetah2 » Thu, 07 Sep 2006 22:07:21

nfortunately, we are in an environment where the DBAs are not part of
the architecture piece. We just get to fix the problems after the
fact, as in this case.
I agree that it is not written in the best way. I will try the QA
thing and see what I get.

Thanks

John Bell wrote: