Strored procedure within SQL transaction executes much longer

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Krzysztof Rozmus
    #1

    Strored procedure within SQL transaction executes much longer

    Hi,

    I have stored procedure (MS SQL Server 2000) which operates
    on around 600 000 rows (SELECT, UPDATE, INSERT)
    and executes in 5 minutes,

    when I put it in SQL transaction it slows down to more than 5 hours (!!)

    I have to admit that it is not problem with data locks (beside that
    procedure
    nothing else is executed on db),
    It is not also problem with that exact procedure, other procedures
    also slow down heavily when wrapped by SQL transaction

    very very seldom stored procedure within transaction executes
    comparably long that its copy without transaction

    I guess it could be MS SQL Server 2000 configuration/tuning problem.

    Any ideas ?

    Chris




    Tags: None
    • Simon Hayes
      #2
      Re: Strored procedure within SQL transaction executes much longer


      "Krzysztof Rozmus" <rozmus@kr.onet .pl> wrote in message
      news:bo88m2$o0p $1@news.onet.pl ...[color=blue]
      > Hi,
      >
      > I have stored procedure (MS SQL Server 2000) which operates
      > on around 600 000 rows (SELECT, UPDATE, INSERT)
      > and executes in 5 minutes,
      >
      > when I put it in SQL transaction it slows down to more than 5 hours (!!)
      >
      > I have to admit that it is not problem with data locks (beside that
      > procedure
      > nothing else is executed on db),
      > It is not also problem with that exact procedure, other procedures
      > also slow down heavily when wrapped by SQL transaction
      >
      > very very seldom stored procedure within transaction executes
      > comparably long that its copy without transaction
      >
      > I guess it could be MS SQL Server 2000 configuration/tuning problem.
      >
      > Any ideas ?
      >
      > Chris
      >
      >
      >
      >[/color]

      This is a guess, but it may be that your transaction log is set to
      autoshrink and autogrow. If you have a single large transaction, SQL Server
      will have to add space to the log all the time, and this could become very
      slow. If the log is also set to autoshrink, then after the large transaction
      completes it will shrink back down again, and so the same problem can
      repeat. You could avoid this by making the log larger, and disabling
      autoshrink.

      If this isn't the problem, then perhaps you can give some more information -
      how big is the database and how big is the log; how much free space do you
      have in the database; how many rows are inserted/updated by the proc; what
      does the execution plan show is taking most time; what does Profiler show is
      taking most time, etc.

      Simon


        Comment

        Previous template Next
        Working...