SQL Server Performance

if else vs OR

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Shobin Mathew, May 20, 2009.

  1. Shobin Mathew New Member

    I have a query,SELECT
    TOP (@BatchCount)column1,column2, column3 FROM [table]
    WITH (NOLOCK)
    WHERE[column1] > @var
    OR@var
    is Null ORDER BY column1;
    If i write it as IF exists (@var )SELECT
    TOP (@BatchCount)column1,column2, column3 FROM [table]
    WITH (NOLOCK)
    WHERE[column1]
    > @var
    ORDER BY column1;
    ELSE SELECT TOP (@BatchCount)
    column1
    ,column2, column3 FROM
    [table]
    WITH (NOLOCK)
    ORDER BY column1;
    Which will be better, there will be lakhs of records. I am using sql server 2008
  2. Adriaan New Member

    If you want to use @var to pass a query statement, then please note that your IF EXISTS (@var) syntax will always give you a runtime error. The complete statements have to be executed as a single dynamic SQL statement to make that work.
    In these types of cases, if there is a performance problem with this type of criteria, we generally suggest to use dynamic SQL to compile the statement with criteria if the parameter is given, or without criteria if the parameter is null.
  3. FrankKalis Moderator

    Additionally surf over to http://www.sommarskog.se and read Erland's article on Dynamic Search Conditions.
  4. Shobin Mathew New Member

    The query is working any way correctly. It is only a aprt of a big query. @var will either contain null or will contain a sequential guid.
    I just want to have a performance based analysis
  5. Sandy New Member

    Hi Shobin Mathew,
    As per your subject line I can go with "OR" rather than using "If else" keyword.
    Thanks,
    Sandy.

Share This Page