如何强制sql server先执行子查询并过滤'where'语句 [英] How can i force sql server to execute subquery first and filter the 'where' statement

问题描述

我有一个这样的查询:

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

实际的子查询更复杂.当我单独执行子查询时，它可能会快速返回 200 行，但是当我执行整个查询时，等待时间太长.

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.

我知道 sql server 在这里进行了一些优化并将 where 语句合并到子查询中，并生成了效率不高的新执行计划.虽然我可以深入研究执行计划并分析原因，例如索引丢失，统计数据陈旧.

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.

但我肯定知道，作为 BaseTable 的我的子查询只会返回一小部分数据，所以我希望所有进一步的过滤或连接都只发生在这一小部分数据中.

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.

我的问题是，我可以强制 sql server 先执行子查询，而不关心外部的 where 语句吗?(顺便说一句，TempTable 是我最后的选择，CTE 不起作用)

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)

推荐答案

如果您的 TableX 中有一个关键列，您可以使用自联接:

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 必须先执行内部查询才能匹配第二个条件.

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

你也可以使用 TOP(MaxInt) 技巧:

You could use also the TOP(MaxInt) trick:

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

在应用外部 WHERE 过滤器之前会强制获取子查询

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

这篇关于如何强制sql server先执行子查询并过滤'where'语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！