SQL Server Performance

Speed of If Else

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by matt3.5, Oct 21, 2008.

  1. matt3.5 New Member

    Hi folks,
    I have a procedure for custom/sorting paging a datagrid on a web page. It uses a sortparameter which (unfortunately) I have to pass in to some dynamic sql and execute using sp_executesql - not ideal, but it works and FAST. However, I have to do an IF.....OR ....(run it) ELSE (don't run sproc) type checking at the start of the sproc against the sortparam that is passed in. This is simply to check that the sortparameter passed in in an expected column name and not sqlinjection. I was just wondering if this is going to cause any performance hit on the query? I'm not sure how to use query analyzer when the sproc uses sp_executesql.
    It is only a fairly small if or else statement, but was just wondering if this is something to worry about performance-wise?
  2. Adriaan New Member

    With dynamic SQL, as long as the resulting query statement follows the standard recommendations for query performance, you should be fine - especially if you use sp_ExecuteSQL with proper parameters.
    If the IF evaluations are simple ones against static values, and without subqueries, and there are not too many of them, then you can ignore them as far as execution time is concerned.
  3. matt3.5 New Member

    I have found another way to solve my problem using a Case When statement - I found out that SQL Server is unlikely to cache query plans for sprocs containing if blocks - especially when those if blocks lead to different begin - end routines i.e. different queries. So I have done the same thing with CASE When, in Profiler it runs faster, but will SQL Server cache the plan? I am assuming so...but not sure..
  4. Adriaan New Member

    You could use EXEC sp_ExecuteSQL and use the IF blocks for compiling dynamic SQL. This should help your chances of getting execution plan re-used.
  5. Madhivanan Moderator

    Also make sure you read this
    www.sommarskog.se/dynamic_sql.html

Share This Page