Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: Why does a Query runs considerably faster than stored procedure?

    I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.

    All the tests where made using the Query Analizer.

    What could be wrong?

    Thanks a lot in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Check the execution plans for both and compare the differences.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It would help us considerably to see the code...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Why does a Query runs considerably faster than stored procedure?

    Originally posted by EMoscosoCam
    I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.

    All the tests where made using the Query Analizer.

    What could be wrong?

    Thanks a lot in advance.
    If you try the same query in sp - it will take the same time - problem is in parameters (check plan).

  5. #5
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    In many DBMSes, a stored procedure can run considerably slower than a query because often stored-proc execution is serialized while queries run more in parallel. Some stored-proc interpreters I've run across are also buckets-o-bolts.

    My general philosophy is that the server ought to receive only requests that are "lean and mean," and executing a stored-proc doesn't fall into that category. Let it be given a load consisting of fairly straightforward queries that don't involve a lot of tables and don't return huge result-sets. Let the other computers in the workplace or in the server-farm do more of the work. You'll get a lot more mileage out of your hardware...
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not this debate again.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    If there have been significant changes to the data (either added/deleted, indexes...) - you need to recompile the stored procedure.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I had this problem, but more severe, on my system.

    I found that the optimizer was using parallel processing when the process was run through query analyzer, but did not use parallel processing when run as a stored procedure. I was never able to pin down why, but the statement was very complicated and involved lots of subqueries. By spinning off one critical subquery as a separate statement that stored results in a temporary table I was then able to link the results back into the original query, and the optimizer resumed using parallel processing again.

    blindman

  9. #9
    Join Date
    Jan 2004
    Posts
    4
    I am having the same problem...I have recompiled and it did nothing. I have read in several places that it is probably from a bad cached plan but recompiling should fix that, right? Any other ideas would be much appreciated.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the sproc?

    Also, how did you test it? Are there any parameters?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2004
    Posts
    4
    I ran the sp in QA and then copied the query in QA and ran it. The sp took 23 seconds and the query took a split second. I also run the sp in my VB app and it takes the same time, I run the query in the same app and it takes 1 second. The execution plans were very different...The sp used table scans and hashes like crazy...The query did not. It is a pretty large query with a cursor. Not allowed to post the query...

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The developer is too embarrassed about using the cursor? ;-)

    See if you can achieve the same result without the cursor, Jill. In my experience a lot of bad performance can be traced back to a cursor. Good luck.

  13. #13
    Join Date
    Jan 2004
    Posts
    4
    I am not a fan of cursors either. I am in the works of making this free of cursors but I am more concerned about the performance of a sp compared to a query...It makes me want to see what other sp's are doing this. I'll see if any of our non-cursor sp's are having the same problem...

  14. #14
    Join Date
    Jan 2004
    Posts
    4
    I just recreated the sp and made it cursor free. I ran the sp in QA and it took 6 seconds. Ran the query in QA and it took a fraction of a second...Grrr. 6 seconds is better than 24 but I still want to know why the query is better than the sp...

    PS - I replaced the cursor with a local temp table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •