code getting progressively slower within a transaction

Hi guys,
I've got a stored proc running in a transaction, it does a lot of
complicated processing in terms of selecting from just about every
table in the db, and making a variety of updates. I can't post the DDL
or code, but hopefully it will suffice to say that it selects, updates,
deletes and inserts into lots of tables, and all runs wrapped up within
just 1 transaction (there are no nested transactions - at least not
explicit ones).
The code loops through with a cursor, running another stored procedure
to actually then process the row.
Each iteration takes longer and longer, but if we take it out of the
outer transaction then it's fine. What might be building up that could
cause this to happen? I assume it's not locking as this would cause
things to just deadlock rather than slow wouldn't it? My only guess is
that it's not able to clear something in the transaction log, and it is
having to grow the file which is taking time, but that would not get
progressively worse.
Cheers
WillI forgot to say, this job runs during maintenance, so there are no
other processes to lock with or compete with|||Will
u might be having a small transaction log file with an autogrowth by
a few percent. The process might get slowed if the transaction log grows
frequenlty. try to increase the size of the transaction log file and run the
proc. There might also be a problem with the tempdb overuse. Try to put the
tempdb and the transaction log file in seperate disks if u can. hope this
helps|||Autogrow will take longer each time if you have a % defined. The key is to
always ensure there is enough free space before you start the process. What
kind of cursors are you using? Try to declare them as Static and see if
that helps.
Andrew J. Kelly SQL MVP
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144830633.350622.218100@.z34g2000cwc.googlegroups.com...
> Hi guys,
> I've got a stored proc running in a transaction, it does a lot of
> complicated processing in terms of selecting from just about every
> table in the db, and making a variety of updates. I can't post the DDL
> or code, but hopefully it will suffice to say that it selects, updates,
> deletes and inserts into lots of tables, and all runs wrapped up within
> just 1 transaction (there are no nested transactions - at least not
> explicit ones).
> The code loops through with a cursor, running another stored procedure
> to actually then process the row.
> Each iteration takes longer and longer, but if we take it out of the
> outer transaction then it's fine. What might be building up that could
> cause this to happen? I assume it's not locking as this would cause
> things to just deadlock rather than slow wouldn't it? My only guess is
> that it's not able to clear something in the transaction log, and it is
> having to grow the file which is taking time, but that would not get
> progressively worse.
> Cheers
> Will
>