sql - 使用 top 属性查询速度更快

Question

为什么此查询在 SQL Server 2008 R2(版本 10.50.2806.0)中更快

    SELECT
        MAX(AtDate1),
        MIN(AtDate2)
    FROM
    (
        SELECT TOP 1000000000000
            at.Date1 AS AtDate1,
            at.Date2 AS AtDate2
        FROM
            dbo.tab1 a
        INNER JOIN
            dbo.tab2 at
        ON
            a.id = at.RootId
        AND CAST(GETDATE() AS DATE) BETWEEN at.Date1 AND at.Date2
        WHERE
            a.Number = 223889
    )B  

然后

    SELECT
        MAX(AtDate1),
        MIN(AtDate2)
    FROM
    (
        SELECT 
            at.Date1 AS AtDate1,
            at.Date2 AS AtDate2
        FROM
            dbo.tab1 a
        INNER JOIN
            dbo.tab2 at
        ON
            a.id = at.RootId
        AND CAST(GETDATE() AS DATE) BETWEEN at.Date1 AND at.Date2
        WHERE
            a.Number = 223889
    )B  

？

带有 TOP 属性的第二条语句速度快了六倍。

内部子查询的count(*)为9280行。

我可以使用 HINT 来声明 SQL Server 优化器使其正确吗？

Answer 1

我看到您现在发布了 the plans 。抽奖只是运气。

您的实际查询是 16 个表连接。

SELECT max(atDate1)  AS AtDate1,
       min(atDate2)  AS AtDate2,
       max(vtDate1)  AS vtDate1,
       min(vtDate2)  AS vtDate2,
       max(bgtDate1) AS bgtDate1,
       min(bgtDate2) AS bgtDate2,
       max(lftDate1) AS lftDate1,
       min(lftDate2) AS lftDate2,
       max(lgtDate1) AS lgtDate1,
       min(lgtDate2) AS lgtDate2,
       max(bltDate1) AS bltDate1,
       min(bltDate2) AS bltDate2
FROM   (SELECT TOP 100000 at.Date1  AS atDate1,
                          at.Date2  AS atDate2,
                          vt.Date1  AS vtDate1,
                          vt.Date2  AS vtDate2,
                          bgt.Date1 AS bgtDate1,
                          bgt.Date2 AS bgtDate2,
                          lft.Date1 AS lftDate1,
                          lft.Date2 AS lftDate2,
                          lgt.Date1 AS lgtDate1,
                          lgt.Date2 AS lgtDate2,
                          blt.Date1 AS bltDate1,
                          blt.Date2 AS bltDate2
        FROM   dbo.Tab1 a
               INNER JOIN dbo.Tab2 at
                 ON a.id = at.Tab1Id
                    AND cast(Getdate() AS DATE) BETWEEN at.Date1 AND at.Date2
               INNER JOIN dbo.Tab5 v
                 ON v.Tab1Id = a.Id
               INNER JOIN dbo.Tab16 g
                 ON g.Tab5Id = v.Id
               INNER JOIN dbo.Tab3 vt
                 ON v.id = vt.Tab5Id
                    AND cast(Getdate() AS DATE) BETWEEN vt.Date1 AND vt.Date2
               LEFT OUTER JOIN dbo.Tab4 vk
                 ON v.id = vk.Tab5Id
               LEFT OUTER JOIN dbo.VerkaufsTab3 vkt
                 ON vk.id = vkt.Tab4Id
               LEFT OUTER JOIN dbo.Plu p
                 ON p.Tab4Id = vk.Id
               LEFT OUTER JOIN dbo.Tab15 bg
                 ON bg.Tab5Id = v.Id
               LEFT OUTER JOIN dbo.Tab7 bgt
                 ON bgt.Tab15Id = bg.Id
                    AND cast(Getdate() AS DATE) BETWEEN bgt.Date1 AND bgt.Date2
               LEFT OUTER JOIN dbo.Tab11 b
                 ON b.Tab15Id = bg.Id
               LEFT OUTER JOIN dbo.Tab14 lf
                 ON lf.Id = b.Id
               LEFT OUTER JOIN dbo.Tab8 lft
                 ON lft.Tab14Id = lf.Id
                    AND cast(Getdate() AS DATE) BETWEEN lft.Date1 AND lft.Date2
               LEFT OUTER JOIN dbo.Tab13 lg
                 ON lg.Id = b.Id
               LEFT OUTER JOIN dbo.Tab9 lgt
                 ON lgt.Tab13Id = lg.Id
                    AND cast(Getdate() AS DATE) BETWEEN lgt.Date1 AND lgt.Date2
               LEFT OUTER JOIN dbo.Tab10 bl
                 ON bl.Tab11Id = b.Id
               LEFT OUTER JOIN dbo.Tab6 blt
                 ON blt.Tab10Id = bl.Id
                    AND cast(Getdate() AS DATE) BETWEEN blt.Date1 AND blt.Date2
        WHERE  a.Nummer = 223889) B

无论是好计划还是坏计划，执行计划都将“语句优化提前终止的原因”显示为“超时”。

这两个计划的加入顺序略有不同。

计划中唯一不满足索引查找的连接是 Tab9 上的连接。有 63,926 行。

执行计划中缺少索引详细信息建议您创建以下索引。

CREATE NONCLUSTERED INDEX [miising_index]
ON [dbo].[Tab9] ([Date1],[Date2])
INCLUDE ([Tab13Id])

在 SQL Sentry Plan Explorer 中可以清楚地看到不良计划的问题部分

SQL Server 估计，之前的连接将返回 1.349174 行，进入 Tab9 上的连接。因此，嵌套循环连接的成本就好像需要对内部表执行扫描 1.349174 次。

事实上，有 2,600 行输入到该连接中，这意味着它对 Tab9 进行了 2,600 次完整扫描(2,600 * 63,926 = 164,569,600 行。)

碰巧的是，在好的计划中，进入连接的估计行数是 2.74319。这仍然是错误的三个数量级，但稍微增加的估计意味着 SQL Server 更倾向于使用散列连接。哈希联接仅通过 Tab9

进行一次传递

我首先尝试在 Tab9 上添加缺少的索引。

另外/相反，您可以尝试更新所有涉及的表的统计信息(特别是那些带有日期谓词的表，例如 Tab2 Tab3 Tab7 Tab8 Tab6)，看看这是否可以在某种程度上纠正计划左侧的估计行和实际行之间的巨大差异。

还将查询分解为更小的部分，并将它们具体化为具有适当索引的临时表可能会有所帮助。然后，SQL Server 可以使用这些部分结果的统计信息来为计划中稍后的连接做出更好的决策。

只有作为最后的手段，我才会考虑使用查询提示来尝试使用散列连接强制执行计划。您可以选择使用 USE PLAN 提示(在这种情况下，您可以准确指定所需的计划，包括所有连接类型和顺序)，或者通过声明 LEFT OUTER HASH JOIN tab9 ....第二个选项还具有修复计划中所有连接顺序的副作用。两者都意味着 SQL Server 根据数据分布的变化调整计划的能力将受到严重限制。