Forcing a UNION on an OR for optimization in SQL Server 2000

Question

How can I get a query which uses an OR in the WHERE clause to split itself into two queries with a UNION during compilation? If I manually rewrite it, the query using the UNION is 100x faster than the single query, because it can effectively use different indices in each query of the union. Is there any way I can make the optimizer use this approach?

I have a query that looks something like this:

select columnlist
from table1
join table2 on joincond2
join table3 on joincond3
where conditions1 
    and ((@param1 is null and cond3a) or (cond3b))

Where columnlist, joincond2, joincond3, and conditions1 are all longer expressions. The kicker is that only one of the conditions in the OR is ever true.

I first thought I could just rewrite it to do the union, but then I am repeating columnlist, joincond2, joincond3, and conditions1, which is 20 or so lines of SQL that might need a lot of maintenance in the future. Is there a hint I can supply or some better way to write the WHERE clause? Thanks in advance.

Answer 1

You can group

select columnlist
from table1
join table2 on joincond2
join table3 on joincond3

into a view, and then use union.

but if you can migrate to sql2005/8, you can use common table expression.

with cte ( columnlist )
as (
    select columnlist
    from table1
    join table2 on joincond2
    join table3 on joincond3 )
select columnlist from cte where ...
union
select columnlist from cte where ...

Answer 2

Try adding OPTION (RECOMPILE) to the query. If it's in a stored procedure then add WITH RECOMPILE to that as well. It may be that the first time that you run the query SQL Server comes up with a plan and caches it but then the second time through it's still using the old (and now poor) query plan.

You will take a minor hit because it will need to recompile every time you use the query, but it will be minuscule in comparison to the use of a poor plan.

EDIT: I've read that using WITH RECOMPILE in a stored procedure in SQL 2000 doesn't always work properly. The bug was supposedly fixed in SQL 2005. I've never encountered the bug personally though, so I don't know what the exact deal is with it. Give it a try though.