Post reply

stored procedure Vs select statement

  • March 18, 2008 at 12:40 am

    #188059

    Hi All,

    I have a following situation for you.

    I have a query which eventually select a sets of rows..

    select a1,a2,a3 from tab1 where ...... some filters go here....

    I embed the same stuff in a stored procedure with input parameters

    that form filters for my select statement above.

    I wish to know how is it going to effect the performance in either way

    described above.

    secondly , i have a bit more complicated stuff where i use 'UNION'

    in my select statement above.

    select a1,a2,a3 from tab1 where ...... some filters go here....

    UNION

    select a1,a2,a3 from tab1 where ...... some more condtions and filters go here....

    Now again if i embed this into a stored proc what is the impact on performance.

    Thanks

  • March 18, 2008 at 10:15 am

    #790959

    If my understanding is correct, you would like to compare which is better, an ad hoc query or a stored procedure.

    If so, adding your query in your stored procedure is better because the estimation plan will be saved on server side. When you run it next time, the system will call the saved execution plan and the process will be faster.

  • March 18, 2008 at 11:11 am

    #791002

    SQL ORACLE (3/18/2008)

    If my understanding is correct, you would like to compare which is better, an ad hoc query or a stored procedure.

    If so, adding your query in your stored procedure is better because the estimation plan will be saved on server side. When you run it next time, the system will call the saved execution plan and the process will be faster.

    USUALLY but not in every case. Depending on the parameters you use, SQL may have to recreate the execution plan. It just depends on the parameters used and how often they change.

    For Example: If your WHERE clause was something like this...

    WHERE

    (field1 = @param1 AND @param1 IS NOT NULL)

    OR

    (field2 = @param2 AND @param2 IS NOT NULL)

    Passing @param1 will most definately cause a recompilation if the plan was created with @param2.

    Make sense?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • March 18, 2008 at 11:46 am

    #791021

    And, the "re-used" execution plan for 1 set of parameters might be absolutely terrible with another set or sometimes a thing called "parameter sniffing" kicks in and the whole server takes a holiday on your code.

    The answer is, as always, it depends and you have to try things out. I've even used WITH RECOMPILE and Dynamic SQL to get around parameter sniffing problems and bad reuse of execution plans.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 19, 2008 at 12:07 am

    #791288

    Thanks Gentlemen..!!! for all response

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

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