I have a query I'm trying to optimize, something like this:
SELECT 1 FROM HugeView WHERE (Col1 = 'a' AND @val = 1) OR (Col2 = 'a' AND @Val = 0) (where Col1 and Col2 are on different tables)
If I hardcode the
@Val value (1/0) - SQL Server knows to build an execution plan in which it only accesses the relevant table for either
But when using a variable all the tables in
HugeView are accessed.
Can you advise on a way of doing it in a way that would help SQL Server not access the unecessary table?
- Can't use
- Can't encapsulate this code in an SP
- Can't change
Unfortunately all aforementioned limitations are part of the product's design and/or dev scope - nothing I can do about that - just work within what I have.
I do however know that
@var is either 1 or 0, and can create and query (and join on) temp or "real" tables as I like.
Also notice I don't select from the tables of
Col2 - I only use them for filtering.
I tried creating and joining on empty tables, or
top(@somevar) depending on
@var's value - but didn't help.
UNION ALL won't help as in any case the optimizer doesn't know the value of
@val. Also can't change the query - if I could then of course all would've been solved.