如何强制子查询执行与#temp表一样好? [英] How can I force a subquery to perform as well as a #temp table?

问题描述

我再次重申Mongus Pong提出的问题

I am re-iterating the question asked by Mongus Pong Why would using a temp table be faster than a nested query? which doesn't have an answer that works for me.

我们大多数人都发现，当嵌套查询达到某种复杂度时，需要将其拆分为临时表以保持其性能. 荒谬，这可能是最实际的前进方式，这意味着这些过程不再能被视为一种观点.通常，第三方BI应用程序只能在视图上很好地播放，因此这很关键.

Most of us at some point find that when a nested query reaches a certain complexity it needs to broken into temp tables to keep it performative. It is absurd that this could ever be the most practical way forward and means these processes can no longer be made into a view. And often 3rd party BI apps will only play nicely with views so this is crucial.

我相信必须有一个简单的queryplan设置，以使引擎从内到外依次对每个子查询进行后台处理.无需再猜测它如何使子查询更具选择性(有时它会非常成功地完成)，并且没有关联子查询的可能性.程序员打算通过括号之间的独立代码返回的数据栈.

I am convinced there must be a simple queryplan setting to make the engine just spool each subquery in turn, working from the inside out. No second guessing how it can make the subquery more selective (which it sometimes does very successfully) and no possibility of correlated subqueries. Just the stack of data the programmer intended to be returned by the self-contained code between the brackets.

对我来说，发现从子查询更改为#table的时间通常从120秒变为5，这是很常见的.本质上，优化程序在某处犯了一个主要错误.当然，我可能会花很多时间来诱使优化器以正确的顺序查看表，但是即使这样也无法提供保证.我并不是在这里要求理想的2秒执行时间，只是临时制表为我提供了 view 的灵活性之内的速度.

It is common for me to find that simply changing from a subquery to a #table takes the time from 120 seconds to 5. Essentially the optimiser is making a major mistake somewhere. Sure, there may be very time consuming ways I could coax the optimiser to look at tables in the right order but even this offers no guarantees. I'm not asking for the ideal 2 second execute time here, just the speed that temp tabling offers me within the flexibility of a view.

我以前从未在这里发布过文章，但是多年来我一直在编写SQL，并阅读了其他有经验的人的评论，这些人也刚刚开始接受这个问题，现在我想请合适的天才向前迈进并说特殊提示是X ...

I've never posted on here before but I have been writing SQL for years and have read the comments of other experienced people who've also just come to accept this problem and now I would just like the appropriate genius to step forward and say the special hint is X...

推荐答案

对于为什么会出现此行为，有一些可能的解释.一些常见的是

There are a few possible explanations as to why you see this behavior. Some common ones are

1. 子查询或CTE可能会被反复重新评估.
2. 将部分结果材料化为#temp表可能会通过从等式中删除一些可能的选项来强制计划中该部分计划的最佳联接顺序.
3. 将部分结果材料化为#temp表可能会通过纠正差的基数估计来改善计划的其余部分.

1. The subquery or CTE may be being repeatedly re-evaluated.
2. Materialising partial results into a #temp table may force a more optimum join order for that part of the plan by removing some possible options from the equation.
3. Materialising partial results into a #temp table may improve the rest of the plan by correcting poor cardinality estimates.

最可靠的方法就是简单地使用#temp表并自己实现它.

The most reliable method is simply to use a #temp table and materialize it yourself.

关于第1点的操作失败，请参见提供提示以强制中间实现CTE或派生表.使用TOP(large_number) ... ORDER BY通常可以鼓励假脱机处理结果，而不是反复进行重新评估.

Failing that regarding point 1 see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ... ORDER BY can often encourage the result to be spooled rather than repeatedly re evaluated.

即使可以，但是阀芯上也没有统计信息.

Even if that works however there are no statistics on the spool.

对于第2点和第3点，您需要分析为什么没有得到理想的计划.可能重写查询以使用可修饰的谓词，或者更新统计信息可能会得到更好的计划.否则，您可以尝试使用查询提示来获取所需的计划.

For points 2 and 3 you would need to analyse why you weren't getting the desired plan. Possibly rewriting the query to use sargable predicates, or updating statistics might get a better plan. Failing that you could try using query hints to get the desired plan.

这篇关于如何强制子查询执行与#temp表一样好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！