是否应该将所有子查询替换为临时表? [英] Should all sub queries be replaced with temporary tables?

问题描述

我一直在研究一个解决方案(在 SQL Server 中)，其中所有子查询无一例外都用临时表重写以提高性能.

I have been working on a solution (in SQL Server) where all the sub queries with no exception have been rewritten with temp tables in order to boost performance.

举个例子，所有的查询都是这样的:

To give an example, all the queries like this:

SELECT something 
FROM (SELECT * FROM T1 WHERE condition1) 
JOIN ...

被改写成这样:

SELECT * 
INTO #tempTable 
FROM T1 
WHERE condition1

SELECT something 
FROM #tempTable  
JOIN ...

还建议此处应避免所有子查询，而应使用临时表.

It has also been suggested here that all sub queries should be avoided in favor of temp tables.

根据给定的事实，是否应该将所有子查询替换为临时表?如果不是，什么时候应该考虑一个?

Based on the given facts, Should all sub queries be replaced with temp table? If not when should one be considered over the other?

推荐答案

这太荒谬了.一个笑话.

That is ridiculous. A joke.

您的子查询"没有问题.SQL Server 只是忽略它.您可以将其重写为:

Your "subquery" is fine as is. SQL Server just ignores it. You could rewrite it as:

SELECT something
FROM T1 JOIN . . .
WHERE condition1

SQL Server 应该正确优化这一点.

SQL Server should optimize this correctly.

根据我使用 SQL Server 的经验，很少有需要创建临时表来优化查询的情况.更频繁地，我使用查询提示来避免嵌套循环连接.

In my experience with SQL Server, there have been very few cases where creating a temporary table is needed for optimizing a query. A bit more often, I use query hints to avoid nested loop joins.

如果需要一个临时表，那么该表上几乎总会有索引.这是使用临时表的关键原因之一.(另外两个是因为通过一个查询或多个查询重复了同一个查询块).

If a temporary table is needed, then there would almost always be indexes on the table. That is one of the key reasons for using a temporary table. (The two others are because the same query block is repeated through one query or multiple queries).

这篇关于是否应该将所有子查询替换为临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！