SQL Server Performance Forum – Threads Archive
Using OR and In in a where clause
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 : fromODS_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
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)
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?
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.
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
]]>