为什么使用临时表比嵌套查询要快? [英] Why would using a temp table be faster than a nested query?

问题描述

我们正在尝试优化一些查询.

We are trying to optimize some of our queries.

一个查询正在执行以下操作:

One query is doing the following:

SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (<complex subquery>) Date,
INTO [#Gadget]
FROM task t

SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID) as Client 
FROM [#Gadget]
order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC

DROP TABLE [#Gadget]

(我已删除了复杂的子查询.除了解释为什么要将该查询分为两个阶段进行之外，我认为这无关紧要.)

(I have removed the complex subquery. I don't think it's relevant other than to explain why this query has been done as a two stage process.)

我认为，使用以下子查询将其合并到单个查询中会更加有效:

I thought it would be far more efficient to merge this down into a single query using subqueries as:

SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID)
FROM
(
    SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (<complex subquery>) Date,
    FROM task t
) as sub    
order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC

这将为优化器提供更好的信息，以解决正在发生的事情，并避免使用任何临时表.我以为应该会更快.

This would give the optimizer better information to work out what was going on and avoid any temporary tables. I assumed it should be faster.

但是事实证明它要慢得多. 8秒而不是5秒.

But it turns out it is a lot slower. 8 seconds vs. under 5 seconds.

我不知道为什么会这样，因为我对数据库的所有知识都表明子查询总是比使用临时表更快.

I can't work out why this would be the case, as all my knowledge of databases imply that subqueries would always be faster than using temporary tables.

我想念什么?

编辑-

根据我从查询计划中看到的内容，两者基本相同，只是临时表具有额外的表插入"操作，成本为18％.

From what I have been able to see from the query plans, both are largely identical, except for the temporary table which has an extra "Table Insert" operation with a cost of 18%.

显然，由于它有两个查询，因此第二个查询中Sort Top N的成本要比Subquery方法中的Sort成本高很多，因此很难直接比较成本.

Obviously as it has two queries the cost of the Sort Top N is a lot higher in the second query than the cost of the Sort in the Subquery method, so it is difficult to make a direct comparison of the costs.

我从计划中看到的所有内容都表明子查询方法会更快.

Everything I can see from the plans would indicate that the subquery method would be faster.

推荐答案

很明显，SQL Server选择了错误的查询计划.是的，有可能发生，我有几次和您完全一样的情况.

Obviously, SQL Server is choosing the wrong query plan. Yes, that can happen, I've had exactly the same scenario as you a few times.

问题在于优化查询(提到一个复杂子查询")是一项不平凡的任务:如果您有n个表，则大约有n个！可能的加入订单-这仅仅是开始.因此，进行(a)首先是内部查询，然后(b)然后是外部查询是一个不错的选择，但是SQL Server无法在合理的时间内推论这些信息是很合理的.

The problem is that optimizing a query (you mention a "complex subquery") is a non-trivial task: If you have n tables, there are roughly n! possible join orders -- and that's just the beginning. So, it's quite plausible that doing (a) first your inner query and (b) then your outer query is a good way to go, but SQL Server cannot deduce this information in reasonable time.

您可以做的是帮助 SQL Server.正如Dan Tow在他的出色著作中所写的:" SQL调优"，键通常是联接顺序，从最有选择性的表到最不具有选择性的表.使用常识(或书中描述的方法，更好)，您可以确定哪种连接顺序最合适，然后使用

What you can do is to help SQL Server. As Dan Tow writes in his great book "SQL Tuning", the key is usually the join order, going from the most selective to the least selective table. Using common sense (or the method described in his book, which is a lot better), you could determine which join order would be most appropriate and then use the FORCE ORDER query hint.

无论如何，每个查询都是唯一的，没有使SQL Server更快的魔术按钮".如果您真的想了解发生了什么，则需要查看(或向我们展示)查询的查询计划.其他有趣的数据显示在 SET STATISTICS IO 中，它将告诉您您的查询产生了多少(成本)高昂的硬盘访问量.

Anyway, every query is unique, there is no "magic button" to make SQL Server faster. If you really want to find out what is going on, you need to look at (or show us) the query plans of your queries. Other interesting data is shown by SET STATISTICS IO, which will tell you how much (costly) HDD access your query produces.

这篇关于为什么使用临时表比嵌套查询要快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！