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
>

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
RegardsI'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards|||Michael Hotek wrote:[vbcol=seagreen]
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.[/vbco
l]
I'd settle for dead code coverage. Anyway, sounds like a weekend project.|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
RegardsI'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards|||Michael Hotek wrote:
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.
I'd settle for dead code coverage. Anyway, sounds like a weekend project.|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
>> I'm not aware of any. It would be interesting, but you'd have to first
>> define what you mean by code coverage. To me, it is a testing algorithm
>> which determins what percentage of the possible code paths have been
>> tested.
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.

Code coverage tools

Do code coverage tools exist for SQL. I am not talking about really
complicated one. Just perhaps a tool that will scan a stored proc, then
mark/remove unused variables, for instance.
Regards
I'm not aware of any. It would be interesting, but you'd have to first
define what you mean by code coverage. To me, it is a testing algorithm
which determins what percentage of the possible code paths have been tested.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HJp0HpJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Do code coverage tools exist for SQL. I am not talking about really
> complicated one. Just perhaps a tool that will scan a stored proc, then
> mark/remove unused variables, for instance.
> Regards
|||Michael Hotek wrote:
> I'm not aware of any. It would be interesting, but you'd have to first
> define what you mean by code coverage. To me, it is a testing algorithm
> which determins what percentage of the possible code paths have been tested.
I'd settle for dead code coverage. Anyway, sounds like a weekend project.
|||It's something that is nice in theory, but again, it comes down to how you
define it. Finding unused variables is pretty straightforward. How do you
figure out whether a code path can be taken, no so trivial? How do you
figure out if a piece of code within a stored procedure is executed, again,
not so trivial. You'd need a much better definition of what you mean by
"code coverage" (a better term would be code validity) before trying to
create something.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Frank Rizzo" <none@.none.com> wrote in message
news:OUp9F%230JGHA.2248@.TK2MSFTNGP15.phx.gbl...
> Michael Hotek wrote:
> I'd settle for dead code coverage. Anyway, sounds like a weekend project.