Post reply

Stored Procedures Efficiencies vs Query Analyzer

  • November 17, 2006 at 3:20 pm

    #170485

    I am testing a stored procedure compared with the same code in Query analyzer to verify the speed improvements of a Stored Procedure.  What I am finding though is the S.P. is running slower than the code in Q. A. .  The code is accesssing several tables which are used by many other users.  I am thinking that may be I am using the Stored Procedure incorrectly in that it should be used for simple snippets of code (i.e. to do one logical action) as opposed to large pieces doing several different things i.e. building tables-- removing duplicates -- qualifying against several other tables by deleting records etc.

    I have run the S.P. 4 times with run times of 22:57 first run   29:14 second run  52:14 third run 48:27 forth run.  I have run the code in Q.A. once and it took  28:36 first run.  I have not had a chance to run any more times as it is end of day but the run times from the S.P. I expected to go down not up.  Keep in mind these were run over a 2 day period (if that makes a difference).

    Hopefully I have provided enough info for some one to offer suggestions.

    I will probably have to check the forum Monday for responses as it is end of day (unless your really fast )

  • November 18, 2006 at 9:53 pm

    #672822

    what is your question exactly? I don't see a question in this post anywhere.

    ---------------------------------------
    elsasoft.org

  • November 20, 2006 at 6:09 am

    #672912

    Are my expectations of faster speed with a stored procedure incorrect in this instance?  Are there additional things I need to keep in mind when moving manually run code in Q.A. to a S.P. to optomize speed?

    Hopefully that's a bit clearer.

  • November 20, 2006 at 8:04 am

    #672964

    I seem to remember that the first time a query is run is typically the longest... SQL server builds an execution plan and saves it on the first time; subsequent calls, would use the cached execution plan, and be faster....that's the built in functionality of sql server.

    But if it does not get called for a long time, it's going to fall out of the cache, right? as well as being lost on stop start events. stored procs are compiled, and their execution plan is compiled into it? is that right? 

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 20, 2006 at 8:58 am

    #672991

    Congrats on the 600 Lowell... you got there at the same time as I hit [7]900 .

     

    To complete your post, the 2nd run is also faster because the data is now in cache, which gives the biggest boost of performance.  Compiling can take time but it is not measured in minutes .

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply