“With”命令比使用临时表慢 Select * into #table1 from

debugcn 发表于 Dev

约翰尼·贝兹爵士

为什么以下查询要快得多？

查询 1：

    select distinct ID mid
INTO #t1
from A_Position a where a.situationdate=@SituationDate and 
    a.Portfolio_Name=@portfolio and a.Purpose=@purpose and ID!='TOTAL'

select distinct ID gid 
INTO #t2
from B_Position a where a.situationdate=@SituationDate and 
    a.Purpose=@purpose

select @check=COUNT(mid) from #t1 A INNER JOIN #t2 B ON A.mid = 
    B.gid

查询 1 比查询 2 快得多。

查询 2：

;With 
A as (
select distinct ID mid 
    from A_Position a where 
    a.situationdate=@SituationDate and a.Portfolio_Name=@portfolio and 
    a.Purpose=@purpose and ID!='TOTAL'), 

    B as(
select distinct ID gid 
    from B_Position a where 
    a.situationdate=@SituationDate and a.Purpose=@purpose)

select @check=COUNT(mid) from A INNER JOIN B ON A.mid = 
    B.gid

查询 3：

select @check=COUNT(*)  
        from (
    select distinct ID mid 
        from A_Position a where a.situationdate=@SituationDate and 
        a.Portfolio_Name=@portfolio and a.Purpose=@purpose and 
        ID!='TOTAL') A
    inner join (  select distinct ID gid 
        from B_Position a where 
        a.situationdate=@SituationDate and a.Purpose=@purpose) B on mid=gid

基本上，所有三个查询都有相同的结果，但查询 1 只需要 1-2 秒来执行。另一方面，查询 2 或 3 的执行时间超过 10 分钟。为什么代码的编写方式会有如此巨大的差异？（为什么“With”变慢了）

戈登·利诺夫

这是一个优化问题。如果您查看执行计划，您就会明白为什么其中一个计划比其他计划快得多。

首先，后两者是相同的。将子查询表示为 CTE 或子查询不会更改 SQL Server 中的执行计划。

为什么临时表版本更快？简单的答案是它获得了更好的执行计划。

但这引出了一个问题。原因是因为用于连接两个表的算法。在 CTE/子查询版本中，SQL Server 必须猜测生成了多少行。根据这个数字，它选择它认为最好的算法。

在临时表版本中，数据已经在一个表中，所以 SQL Server 不需要猜测。

因此，临时表可以产生更好的执行计划。不过，让我警告一些事情。使用临时表有更多的开销——数据实际上需要存储在某个地方。它还限制了优化的可能性（在这种情况下恰好是好的，但在其他情况下可能不是）。

您应该能够添加提示以加速其他版本。我猜想像OPTION (HASH JOIN).

您还可以设置索引来优化所有三个版本。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。