代码之家 › 专栏 › 技术社区 › womp

为什么这个(不相关的)子查询会导致这样的问题?

subquery tsql sql-server-2005 sql-server sql

womp · 技术社区 · 15 年前

我有一个大查询,其中一个简单的子查询优化将它从8分钟减少到20秒。我不太明白为什么优化会产生如此剧烈的效果。

本质上,问题在于:

SELECT  (bunch of stuff)
FROM
  a LEFT OUTER JOIN b ON a.ID = b.a
  LEFT OUTER JOIN c ON b.ID = c.b
  ...
  ...
      INNER JOIN veryLargeTable 
      ON a.ID = veryLargeTable.a 
         AND veryLargeTable.PetID = 
             (SELECT id from Pets WHERE Pets.Name = 'Something')    /* BAD! */
  ...
  ...

总共有16个联接表。如果我替换 veryLargeTable 使用包含petID的预填充变量连接(而不是使用子查询),整个查询会加快速度 戏剧性地 :

AND veryLargeTable.PetID = @petID   /* Awesome! */

(SELECT id from Pets WHERE Name = 'Something') 正在为每一行执行。有两件事我还不完全明白:

据我所知,这是一个不相关的子查询。Pets表根本不是外部查询的一部分。不相关的子查询不是独立评估(并因此优化)的吗?为什么这里不是这样?

感谢您的真知灼见!

4 回复 | 直到 15 年前

Philip Kelley 15 年前

根据我的经验,查询越复杂,SQL优化器创建灵活计划的能力就越弱。这里有16个连接,一些或大部分是外部连接,至少有一个子查询。。。投入足够多的索引、基数、视图、外部应用程序,谁知道还有什么,没有人,甚至连微软的工程师*都无法找出能够统一、定期地生成最佳计划的例程。

正如你所描述的,我经历过无数次——在一个混乱的查询中改变一件简单的事情,一切都会快一个数量级(或者,咬牙切齿,慢一点)。我没有办法确定什么时候复杂太复杂,这更像是一种感觉。我的一般经验法则是,如果它看起来太长或太复杂,请简化您可以简化的地方—例如预先选择的单个嵌套值,或者分解查询的一部分总是使用一个小的结果集快速运行,首先运行它并将结果存储在临时表中。

(*请注意这是温和的)

Joe Stefanelli 15 年前

作为替代方案,我认为您可以通过以下方式消除子查询:

...
INNER JOIN veryLargeTable vLT
    ON a.ID = vLT.a 
INNER JOIN Pets p
    ON vLT.PetID = p.id
        and p.Name = 'Something'
...

vaso 15 年前

我个人认为,如果没有指数,结果也就不足为奇了宠物。名字. 如果在上创建唯一索引宠物。名字你可能会看到更好的结果。如果从服务器的角度看没有索引,子查询可能返回多行或空。也许乐观主义者可以做得更好;它经常需要帮助。

T. Webster 14 年前

原因正如您所指出的,根据我的经验,即使是最简单的不相关子查询,也常常由SQLServer的查询优化器重新计算。

例如,您可以查看以下查询的执行计划,并查看是否重新计算了不相关的子查询。

SELECT ID
FROM #table1
WHERE ID in (SELECT ID from #table1)
UNION ALL
SELECT ID
FROM #table1
WHERE ID in (SELECT ID from #table1)

在本例中,在属性“ID”上有或没有聚集索引。正如有人指出的,您可以重写这个查询以使用连接而不是子查询。但是,在许多情况下,如果子查询返回聚合标量,则可以这样做。

where ID = (select MAX(ID) from #table1)

那么join重写可能就不那么容易了。