How can i force sql server to execute subquery first and filter the 'where' statement

Question

i have a query like this:

select * from (
    select * from TableX
    where col1 % 2 = 0
) subquery
where col1 % 4 = 0

The actual subquery is more complicated. when i execute the subquery alone it returns maybe 200rows quickly, but when i execute the whole query, it takes too long to wait.

I know sql server takes some optimization here and merge the where statement into the subquery, and produce the new execution plan which is not that efficient. Althought i can dive into the execution plan and analyze why, like index missing, statistics stale.

But i surely know that, my subquery which serves as a BaseTable will only return a small portion of data, so i want all further filtering or joining will only take place in these small portion of data.

My question is, can i force sql server to execute the subquery first, without caring about the outer where statement? (btw, TempTable is my last option, CTE is not working)

Answer 1

If you have a key column in your TableX you can use a Self Join:

select x1.* 
from TableX x1
inner join  (
    select x.IdColumn 
    from TableX x
    where x.Col1 % 2 = 0
) x2 on x1.IdColumn = x2.IdColumn
where x1.Col1 % 4 = 0

Sql server will have to execute the inner query before to match the second condition.

You could use also the TOP(MaxInt) trick:

select * 
from (
    select top (9223372036854775807) * 
    from TableX
    where Col1 % 2 = 0
) subquery
where Col1 % 4 = 0

It will force to get subquery before apply the outer WHERE filter