Transaction causing procedure to get progressively slower

microsoft.public.sqlserver.programming

23 Jun 2006 12:16 PM

Someone Else

Hi Guys,

I've got a procedure that does a lot of work on a database. When run
within a transaction it gets progressively slower. It uses lots of
nasties like nested cursors down to about 5 levels, and temporary
tables all over the place.

If we run it in separate chunks it gets through the whole lot in about
10 minutes, when left to loop (yes, loop - cursors galore) through the
same data it takes over 2 hours.

I've tried a variety of things with no improvement:

Set all cursors to read_only

Made sure the db is in simple recovery model and that all files in the
db and in the tempdb have loads of space so won't need to grow.

switched between @table variables and #temp tables

set the transaction isolation level to read uncommited

I've got a few theories as to what could be doing it, and was hoping
that someone could give more advice on them (or any other things it
could be).

1) I believe that sql server uses lazy spooling to write the data from
a transaction, could it be that because so much data is being changed,
this is getting too big?

2) There are a lot of messages of 1 row affected etc. being returned to
the client, could this actually be taking up resources to report this -
how can I turn it off?

3) there are a lot of stored procedures nested within each other
(within the cursors) could this be an issue?

I've discounted locking issues as it's running in a read uncommited
transaction - is this a fair assumption?

Does anyone have any other ideas on this?

Thanks
Someone

23 Jun 2006 2:23 PM

simonjw83

> 1) I believe that sql server uses lazy spooling to write the data from
> a transaction, could it be that because so much data is being changed,
> this is getting too big? - Not sure what you mean by this
> 2) There are a lot of messages of 1 row affected etc. being returned to
> the client, could this actually be taking up resources to report this -
> how can I turn it off? This will use resources but probably not major - to turn it off start your proc with this :

SET NOCOUNT ON

> 3) there are a lot of stored procedures nested within each other
> (within the cursors) could this be an issue? Most definitely the use of cursors will be an issue (rather than the nesting of stored procs) - avoid looping wherever possible by using set-based code ie processing occurs within one 'hit' rather than many which the SQL Server engine is designed for.

Cheers

Someone Else wrote:
Show quote

> Hi Guys,
>
> I've got a procedure that does a lot of work on a database. When run
> within a transaction it gets progressively slower. It uses lots of
> nasties like nested cursors down to about 5 levels, and temporary
> tables all over the place.
>
> If we run it in separate chunks it gets through the whole lot in about
> 10 minutes, when left to loop (yes, loop - cursors galore) through the
> same data it takes over 2 hours.
>
> I've tried a variety of things with no improvement:
>
> Set all cursors to read_only
>
> Made sure the db is in simple recovery model and that all files in the
> db and in the tempdb have loads of space so won't need to grow.
>
> switched between @table variables and #temp tables
>
> set the transaction isolation level to read uncommited
>
>
>
> I've got a few theories as to what could be doing it, and was hoping
> that someone could give more advice on them (or any other things it
> could be).
>
> 1) I believe that sql server uses lazy spooling to write the data from
> a transaction, could it be that because so much data is being changed,
> this is getting too big?
>
> 2) There are a lot of messages of 1 row affected etc. being returned to
> the client, could this actually be taking up resources to report this -
> how can I turn it off?
>
> 3) there are a lot of stored procedures nested within each other
> (within the cursors) could this be an issue?
>
> I've discounted locking issues as it's running in a read uncommited
> transaction - is this a fair assumption?
>
> Does anyone have any other ideas on this?
>
> Thanks
> Someone

23 Jun 2006 2:37 PM

Someone Else

Hi Simon,

Thanks for replying,

what I mean is - is there a limit to the amount of changes that can be
"held" in a transaction before the amount of data has a significant
impact on the performance of the commands running in the transaction.

> > 2) There are a lot of messages of 1 row affected etc. being returned to
> > the client, could this actually be taking up resources to report this -
> > how can I turn it off? This will use resources but probably not major - to turn it off start your proc with this :
> SET NOCOUNT ON

Thanks for this, I thought one existed, just couldn't remember it

> > 3) there are a lot of stored procedures nested within each other
> > (within the cursors) could this be an issue? Most definitely the use of cursors will be an issue (rather than the nesting of stored procs) - avoid looping wherever possible by using set-based code ie processing occurs within one 'hit' rather than many which the SQL Server engine is designed for.

I know that cursors are bad, if I'd had my way they wouldn't be in
there at all, but as is always the case with the people who use
cursors, it's not their job to clean up the mess, they're busy writing
cursors into the new project after the last one was so successful
(because there was no data in it at that point)

What I mean't was does the use of stored procedures to encapsulate the
cursors present an issue with regard to sql needing to repeatedly seek
for and run all the SPs? Would this issue be worse in a transaction?

Thanks for your help

Cheers
Someone

simonjw83 wrote:
Show quote

> > 1) I believe that sql server uses lazy spooling to write the data from
> > a transaction, could it be that because so much data is being changed,
> > this is getting too big? - Not sure what you mean by this
> > 2) There are a lot of messages of 1 row affected etc. being returned to
> > the client, could this actually be taking up resources to report this -
> > how can I turn it off? This will use resources but probably not major - to turn it off start your proc with this :
> SET NOCOUNT ON
>
> > 3) there are a lot of stored procedures nested within each other
> > (within the cursors) could this be an issue? Most definitely the use of cursors will be an issue (rather than the nesting of stored procs) - avoid looping wherever possible by using set-based code ie processing occurs within one 'hit' rather than many which the SQL Server engine is designed for.
>
>
> Cheers
>
> Someone Else wrote:
> > Hi Guys,
> >
> > I've got a procedure that does a lot of work on a database. When run
> > within a transaction it gets progressively slower. It uses lots of
> > nasties like nested cursors down to about 5 levels, and temporary
> > tables all over the place.
> >
> > If we run it in separate chunks it gets through the whole lot in about
> > 10 minutes, when left to loop (yes, loop - cursors galore) through the
> > same data it takes over 2 hours.
> >
> > I've tried a variety of things with no improvement:
> >
> > Set all cursors to read_only
> >
> > Made sure the db is in simple recovery model and that all files in the
> > db and in the tempdb have loads of space so won't need to grow.
> >
> > switched between @table variables and #temp tables
> >
> > set the transaction isolation level to read uncommited
> >
> >
> >
> > I've got a few theories as to what could be doing it, and was hoping
> > that someone could give more advice on them (or any other things it
> > could be).
> >
> > 1) I believe that sql server uses lazy spooling to write the data from
> > a transaction, could it be that because so much data is being changed,
> > this is getting too big?
> >
> > 2) There are a lot of messages of 1 row affected etc. being returned to
> > the client, could this actually be taking up resources to report this -
> > how can I turn it off?
> >
> > 3) there are a lot of stored procedures nested within each other
> > (within the cursors) could this be an issue?
> >
> > I've discounted locking issues as it's running in a read uncommited
> > transaction - is this a fair assumption?
> >
> > Does anyone have any other ideas on this?
> >
> > Thanks
> > Someone