SQL Server Performance

Using OR and In in a where clause

Discussion in 'T-SQL Performance Tuning for Developers' started by argturus, Aug 5, 2005.

  1. argturus New Member

    I am trying to optimise a query for reporting but cannot seem to get rid of a Bookmark lookup in the execution plan.

    The were clause is as follows :

    from
    ODS_Income_Statement_Report_Table T1

    Where
    T1.Line_Of_Business_Code = @Line_Of_Business and
    T1.Ledger_Type_Code = @Ledger_Type and
    T1.Level_1_Code = 'IS0' and
    (@Company_Code = 'All' or T1.Company_Code = @Company_Code) AND
    (@Contract_Director = 'All' or T1.Contract_Director_Code = @Contract_Director) AND
    (@Legal_Entity = 'All' or T1.Ultimate_Owner_Code = @Legal_Entity) AND
    (T1.Level_2_Code = 'IS01' OR T1.Level_2_Code = 'IS02' OR
    T1.Level_3_Code IN ('IS00012','IS00016','IS00018'))

    I have a covering Index on all the foelds in the where clause and the index seek is used but the bookmark lookup is added and that takes almost 100% of the cost.

    Any help would be appreciated

    Arg
  2. joechang New Member

    what is your index for this table.
    i hope it is a covered index something like
    Line_Of_Business_Code, Ledger_Type_Code, Level_1_Code, Level_2_Code, Level_3_Code, Company_Code, Contract_Director_Code,
    Ultimate_Owner_Code

    depending on how selective the SARG on Line_Of_Business_Code, Ledger_Type_Code, Level_1_Code, Level_2_Code, Level_3_Code is, and how much more selective the additional optional arguments are,
    it may be necessary to use an IF block,
    IF (@Company_Code = 'All' AND @Contract_Director = 'All' AND @Legal_Entity = 'All' )
    Query 1
    ELSE IF xxx
    Query 2
    ELSE IF xxx
    Query 3

    there 2**X possible statements
    since you have 3 optional arguments, there are 8 separate queries.
    what is comes down to is that the form:
    (@Company_Code = 'All' or T1.Company_Code = @Company_Code)
    makes it very difficult for the optimizer, as it does not understand optional arguments, so it cannot use an index seek on this portion.
    the cost of evaluating the extra logic is also to be considered (see recent posts of datetime conversions)
  3. akus New Member

    If you need quick fix try separating the or statements with unioning the whole query. Result is ugly and replicated code but will buy you time to figure out how to reduce number of conditions.

    also comparing against strings like " T1.Level_2_Code = 'IS01' OR T1.Level_2_Code = 'IS02' " is not good for performance. Try normalising those into int. I suspect that tables are large?
  4. rpzhao New Member

    Arg,

    I had a similar problem some time ago. Changing the order of the conditions reduced the cost significantly, ie

    use
    (T1.Company_Code = @Company_Code or @Company_Code = 'All')

    instead of
    (@Company_Code = 'All' or T1.Company_Code = @Company_Code)

    Hope this help for you as well.


  5. Twan New Member

    Hi ya,

    you say that the covered index is used as an index seek, but then a bookmark lookup is issued. Are you sure that the covered index is actually a covered index? i.e. there should be no reference to any other columns anywhere in the SQL statement...? I've never know SQL to issue a bookmark lookup unless additional columns were required...

    Cheers
    Twan

Share This Page