SQL Server Performance

60 times slower to call SP from DTS

Discussion in 'SQL Server DTS-Related Questions' started by Dave Wells, Oct 29, 2003.

  1. Dave Wells New Member

    I have a stored procedure which takes about 5 seconds to run. If I call it from a DTS step it takes about 5 minutes to run.

    The code is like this. (except that I have three loops through the cursor calling 3 different stored procedures) The stored procedures perform updates / inserts and deletes

    <code>
    DECLARE CursorName CURSOR FOR
    SELECT Name
    FROM Table

    OPEN CursorName

    FETCH NEXT FROM CursorName
    INTO @name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @Output = @Name + 'stuff'
    EXEC (@Output)

    FETCH NEXT FROM CursorName
    INTO @name
    END

    CLOSE CursorName
    DEALLOCATE CursorName

    </code>

    From my initital use of performance monitoring it's showing very few log flushes when run from the DTS package (I get a burst then nothing for 30 seconds or so). I also get 180k lock requests/ sec. I'm not geting any lock timeouts or waits or deadlocks.

    Anyone got an idea as to what might be happening here?
  2. satya Moderator

    In general execution from DTS is slow compared to run from QA.
    HOw about execution plan for the code?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. Dave Wells New Member

    Everything is using indexes as you'd expect. Is there a way to find the execution plan of code run from within DTS using Execute Package.

    Why is code slower to run using Execute Package from with a DTS rather than within Query Analyzer?
  4. Dave Wells New Member

    More information.

    If I run the Stored Procedure from a web page it acts in the same way as running from a DTS Package. (i.e. very slow).
  5. satya Moderator

    As the performance of cursor is slower than executing the query directly. Because the ability of the optimal query plan is not used while using cursor.

    And on top on that executing this from DTS.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. Dave Wells New Member

    <i> As the performance of cursor is slower than executing the query directly. Because the ability of the optimal query plan is not used while using cursor.</i>

    I don't understand. I have a stored procedure (call it Fred) which uses a cursor to execute a number of other stored procedures. What do you mean?

    I execute Fred from Query Analyzer and it's quick. I execute Fred from a web page (using ADO.NET connection) or from a DTS package and it's slow.

  7. Dave Wells New Member

    I've found the cause of the problem but I don't understand why it happens the way it does.

    When my SP was executed from within DTS or using ADO.NET it was using different cached execution plans (for the SPs called within the cursor) to when it was executed from Query Analyzer.

    I've added WITH RECOMPILE to all of the SPs and it's showing the same performance no matter where it runs.

    Now of my next question is how and why does this happen?

    In my initial test (before I found a solution) I was connecting in Query Analyer using Windows Authentication, DTS using UserA, and ADO.NET using UserB. I change to connect using UserA in Query Analyzer and that made no difference. So I think I can eliminate that as a cause.



  8. satya Moderator

    Your question is the answer, being SPs called within cursor doesn't use optimal query plan wher it lacks performance.

    And due to the RECOMPILE option it takes every action to refresh the plan.

    I suggest you to refer to INSIDE SQL Server 2000 book by Kalen Delaney for more information or recent SQLMAG articles refers the similar execution plan discussion.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. Jeremy Selby New Member

    I am also experiencing slow execution of stored procedures executed from DTS. The problem does not appear to be due to the fact that the procedure call is coming from DTS, but more due to the way the call is integrated into the package.

    8 sec completion when the Execute SQL task is executed as an independent step from within the package

    480 sec completion for the Execute SQL task when the package as a whole is run

    The Execute SQL task is started conditionally on the success of some preceeding Transform Data tasks. It appears to start immediately on completion of the prior tasks, but then hangs...and takes...a long time.

    Is there anything I need to be aware of when creating a package which includes calls to stored procedures?

    Dave, you said 'I've added WITH RECOMPILE to all of the SPs and it's showing the same performance no matter where it runs'.
    Do you mean that your proc is now executing in 5 secs via both DTS and QA, or 5 minutes? I tried your suggestion and I still get good performance using QA and poor performance via DTS. I would not have thought forcing a recreate of the execution plan could account for the big performances differences we have been seeing though.
  10. satya Moderator

    Check recent article in SQLMag about DTS.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. Dave Wells New Member

    quote:Originally posted by Jeremy Selby
    Dave, you said 'I've added WITH RECOMPILE to all of the SPs and it's showing the same performance no matter where it runs'.
    Do you mean that your proc is now executing in 5 secs via both DTS and QA, or 5 minutes? I tried your suggestion and I still get good performance using QA and poor performance via DTS. I would not have thought forcing a recreate of the execution plan could account for the big performances differences we have been seeing though.
    It's now executing in 6 or 7 seconds no matter where it is called from. Takes a little longer than the 5 in order to work out the execution plan. Remember, I had the same poor performance calling the SP from a web page (using ADO.NET) so my problem had nothing to do with DTS but was down to cached execution plans being wrong.
  12. Jeremy Selby New Member

    Hi Satya,

    Can you please be more specific. A search on DTS in SQL Mag returns thousands of hits. What recent article?

  13. satya Moderator

  14. Existence New Member

  15. iansr New Member

    Hi!

    Sorry for the cross-post, but thought you might find this useful.
    I encountered a similar problem recently. I had the exact same stored proc run fine in QA but it took a while in my asp.net web page.

    Check my post on this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41378&whichpage=2

    Hope this helps, also please let me know if you can think of a better implementation.

    Thanks,
    Ian

Share This Page