SQL Server Performance

query performance tuning

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ashwin, Dec 18, 2007.

  1. EduardoS New Member

    ashwin,
    When using an index the sql requires that one side of comparison being the column with index and the other being a constant value, ie, a literal, a variable or a deterministic function (or the case statement) with just constant values, other columns aren't a constant value so sql wouldn't use indexes, functions receiving parameters from columns aren't constant either, in your last try the left side is the column with the index, but the right side isn't constant, since there is a column name in the case statement. So:
    a.city = ISNULL(@parameter, '') --Column on one side constant on the other, will use index
    a.city = ISNULL(@parameter, a.city) --Column on one side, a column on the other side, will NOT use index
    ISNULL(a.city, '') = @parameter --No side with column, will NOT use index
    In your case a solution could be:

    select
    *
    from Test1 a , Test2 b
    WHERE (@user IS NULL OR a.user = @user)
    AND (@city IS NULL OR a.city = @city)
    AND (@color IS NULL OR b.color = @color)
  2. ashwin New Member

    Thanks for your suggestion, I modified my query to the following:
    select
    *
    from Test1 a , Test2 b
    where
    a.user = isnull(@user,a.user)
    and a.city = isnull(@city,a.city)
    and b.color = isnull(@color,b.color)
    This works for me and the performance has improved considerably, but since there are lot of null values in city and color columns it takes lot of time for
    table scan. I did indexing on those two columns, but that didnt work too. Could you suggest something to make joins on the null columns faster? Please
    suggest.
  3. EduardoS New Member

    See my previous post...
    Note a.user = isnull(@user,a.user) is diferent from (@user IS NULL OR @user = a.user), the first won't use indexes, the last will.
  4. ashwin New Member

    Thanks again, but what are the performance concerns involved in using these functions ISNULL VS COALESCE. Please give details.
    thanks

Share This Page