SQL Server Performance

Parameter causing query plan to scan instead of seek

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Rinsitah, Mar 10, 2009.

  1. Rinsitah New Member

    I have a query that has a where clause similar to: WHERE
    ISNULL(A1.AccountHolderID,'') = COALESCE(@acccountholderid, ISNULL(A1.AccountHolderID,''))
    @acccountholderid was set to 12345. When the query is run, it takes about 18 seconds, and uses a clustered index scan
    If I specify the @acccountholderid in the where clause however, it comes back sub 1 second, with a clustered index seek
    eg: WHERE ISNULL(A1.AccountHolderID,'') = COALESCE(12345, ISNULL(A1.AccountHolderID,''))
    Why is the query optimizer chooing a scan over a seek when the parameter is not indivually specified?
    And is there any way to make it always do the index seek?
    Many thanks in advance!
  2. moh_hassan20 New Member

    -Don't Use function (fieldname) in the leftside of where condition
    instead use
    WHERE A1.AccountHolderID = COALESCE(@acccountholderid, "")
    -The parameter should not be a variable , it should be a parameter passed in stored procedure , or
    use sp_executesql and pass variables.

  3. Adriaan New Member

    Good suggestion, but if AccountHolderID is a nullable column, you should use
    WHERE (A1.AccountHolderID = @acccountholderid)
    OR (@accountholderid is null and A.AccountHolderID IS NULL)
    ... to get the correct results.
    Also if this is a stored procedure, and it isn't performing as well as you expect, then you could try using a local variable in the actual statement, instead of the parameter.
  4. FrankKalis Moderator

    These constructs usually tend to cause a scan anyway. Especially when the rest of the query itself is not very complex with only a few tables involved. Im under the impression that the more complex the query is and the more tables are involved (or the more rows tehre are in the tables) the more likely you are to get seeks instead of scans when using something like WHERE... = ISNULL(@blahblah, something else). At least I observe this here for queries using 10-15 way joins, but still need to investigate more why this appears to be so.
  5. Adriaan New Member

    Frank's comment reminds me - this type of query usually benefits from using dynamic SQL so you can compile a simple WHERE clause. If the parameter is not null, you use sp_ExecuteSQL (with a proper parameter) to increase chances of execution plan re-usage.

Share This Page