SQL Server Performance Forum – Threads Archive

OR clause substantially slows query

I have a funny situation. The following OR clause takes 20 seconds: and ((a.schd_eff_date_time <= @flow_date_time and a.nomn_end_date_time > @flow_date_time)
or ( @cycle = ‘T’ and a.schd_eff_date_time = @flow_date_time and a.nomn_end_date_time = @flow_date_time))
But if I comment out either side of the OR clause, the query takes about 1 second. I could do a UNION and take abut 2 seconds total, but, clearly, something is amiss here. Any suggestions? TIA, Jim

the optimizer does not always know how to handle an OR clause, thinking that a table scan is required is what you really have 2 queries?
@cylce = ‘T’ and not? the union does fix this, but did you really mean UNION ALL, which is less expensive, but requires that each statement generate distinct rows, which appears to be the case

Well, you know the workaround … This is just typical T-SQL behaviour when using OR criteria, somewhat aggravated by the fact that you have criteria that match a variable against a constant expression. I would be interested to know if it improves when you make the two sets of criteria more similar, like and ((@cycle LIKE ‘%’ and a.schd_eff_date_time <= @flow_date_time and a.nomn_end_date_time > @flow_date_time)
or (@cycle LIKE ‘T’ and a.schd_eff_date_time = @flow_date_time and a.nomn_end_date_time = @flow_date_time)))

Joe – U R rite, UNION ALL Spoke with the DBA’s, they are analyzing clusters and DB activity now. Adriaan – tried your solution, it *significantly* slowed the query. Nice suggestion, though. I’ve used SQL Server for quite a while – though not exactly an expert – and I’ve never seen anything like this at all. Anyway, I’ll let you know what, if anything, we find. May just do the UNION ALL.

Adriaan – Actually, performance was the same with your LIKE ‘% suggestion. Also, I did the UNION ALL on the production database, it is subsecond response with the UNION ALL vs. 17 seconds with the OR clause. That is some serious difference. DBAs really scratching their heads on this one.

This is totally expected behaviour though… the OR statement makes the statement an order of magnitude more complex for the optimiser to work out…? in general you’ll find in most cases that an OR statement is not anywhere near as efficient as a union, except in cursors where the opposite is often the case. NOTE that I’m talking about multi-clause OR statements here not simple list of col1 = x or col2 = y As Joe mentioned though it seems like you are catering for two cases within a single SQL statement which isn’t always a good idea for performance…
Cheers
Twan

Thanks for confirming the non-improvement – always good to know.[8D] And what about indexes on the date fields? If the queries against this table are always filtering for ranges of dates, you might consider adding a clustered index on one of those date columns.

]]>