none
Is it true that a stored procedure is faster than an SQL command from c# even for a simple select? RRS feed

  • Question

  • Is it true that a stored procedure (which is pre-compiled) executes faster than an SQL command from c# even for a simple "select * from tableName where id = value"?
    Friday, May 15, 2009 8:48 PM

Answers

  • Hi

    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 provided there is no parameter for the query. 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.

    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.


    Thanks
    Sreekar
    Saturday, May 16, 2009 3:19 PM

All replies

  • I have found it "depends". Sorry. You will have those that argue SP's are faster but I cannot confirm that.
    Friday, May 15, 2009 9:26 PM
  • Hi

    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 provided there is no parameter for the query. 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.

    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.


    Thanks
    Sreekar
    Saturday, May 16, 2009 3:19 PM