时间戳

首页 > 解决方案 > 视图上最大（日期）查询的奇怪查询计划

sql-server - 视图上最大（日期）查询的奇怪查询计划

问题描述

我有一个包含 4 个年度表格的视图：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BGT_BETWAYDETAILS]
WITH SCHEMABINDING
AS
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_ResultID] [bigint] NOT NULL,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2020]
UNION ALL
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2019]
UNION ALL
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2018]
UNION ALL
SELECT [bwd_BetTicketNr] ,
    [bwd_LineID] [int] ,
    [bwd_DateModified]  ,
    [bwd_DateModifiedTrunc]  ,
    [bwd_LineMaxPayout] 
FROM   [dbo].[BGT_BETWAYDETAILS_2017];
GO

每个表的结构如下：

CREATE TABLE [dbo].[BGT_BETWAYDETAILS_2020]
(
    [bwd_BetTicketNr] [bigint] NOT NULL,
    [bwd_LineID] [int] NOT NULL,
    [bwd_ResultID] [bigint] NOT NULL,
    [bwd_DateModified] [datetime] NULL,
    [bwd_DateModifiedTrunc] [date] NULL,
    [bwd_LineMaxPayout] [decimal](18, 4) NULL,

    CONSTRAINT [CSTR__BGT_BETWAYDETAILS_2020_CKEY] 
        PRIMARY KEY CLUSTERED ([bwd_BetTicketNr] ASC, [bwd_LineID] ASC, [bwd_ResultID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

我添加了一个非聚集索引

CREATE NONCLUSTERED INDEX [NCI__DATEMODIFIED] 
ON [dbo].[BGT_BETWAYDETAILS_2020] ([bwd_DateModifiedTrunc] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
                          ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

我正在运行以下 3 个查询：

SELECT COALESCE(MAX([bwd_DateModifiedTrunc]), '2019-01-01') AS next_date
FROM [dbo].[BGT_BETWAYDETAILS_2020]

SELECT COALESCE(MAX([bwd_DateModifiedTrunc]), '2019-01-01') AS next_date
FROM [dbo].[BGT_BETWAYDETAILS]

SELECT COALESCE(CAST(MAX([bwd_DateModified]) AS date), '2019-01-01') AS next_date
FROM [dbo].[BGT_BETWAYDETAILS]

第一个在每个年表上运行时会立即运行。

第二个，似乎需要永远。对此的查询计划，似乎很奇怪。

在此处输入图像描述

该计划显示每个年度表上的两次索引扫描。

每个年表的计划是我期望看到的：

在此处输入图像描述

最后，关于非索引日期列的计划也是我期望看到的（聚集索引扫描）。对每个表进行聚集索引扫描。此查询在约 3 分钟内运行，这是预期的。

在此处输入图像描述

这里有什么问题？我缺少一些反模式？为什么非聚集索引上的索引扫描按照live plan要做2次？我希望视图的响应速度与单个表一样快。

作为记录，我在 SQL Server 2017 上运行它。

标签： sql-serversql-execution-plan

解决方案

这看起来像是一个优化器限制。我已经提交了一个应该改进的建议。

一个更简单的例子是

CREATE TABLE T1(X INT NULL UNIQUE CLUSTERED);
CREATE TABLE T2(X INT NULL UNIQUE CLUSTERED);

INSERT INTO T1 
OUTPUT INSERTED.X INTO T2
SELECT TOP 100000 NULLIF(ROW_NUMBER() OVER (ORDER BY 1/0),1) 
FROM sys.all_objects o1, 
     sys.all_objects o2;

接着

WITH CTE AS
(
SELECT X FROM T1
UNION ALL
SELECT X FROM T2
)
SELECT MAX(X)
FROM CTE
OPTION (QUERYRULEOFF ScalarGbAggToTop)

这将禁用查询优化器规则ScalarGbAggToTop，并且查询计划MAX对每个单独的表执行 a 然后计算-esMAX的a MAX- 所以与

SELECT MAX(MaxX)
FROM 
(
SELECT MAX(X) AS MaxX FROM T1 
UNION ALL
SELECT MAX(X) AS MaxX FROM T1 
) T

在此处输入图像描述

启用规则后，计划现在ScalarGbAggToTop看起来像这样

在此处输入图像描述

它有效地执行以下操作...

SELECT MAX(MaxX)
FROM   (SELECT MAX(X) AS MaxX
        FROM   (SELECT TOP 1 X
                FROM   T1
                WHERE  X IS NULL
                UNION ALL
                SELECT TOP 1 X
                FROM   T1
                WHERE  X IS NOT NULL
                ORDER  BY X DESC) T1
        UNION ALL
        SELECT MAX(X) AS MaxX
        FROM   (SELECT TOP 1 X
                FROM   T2
                WHERE  X IS NULL
                UNION ALL
                SELECT TOP 1 X
                FROM   T2
                WHERE  X IS NOT NULL
                ORDER  BY X DESC) T2) T0

...但是以一种非常低效的方式。运行上面的 SQL 将给出一个带有 seeks 的计划，并且每个分支只读取一行。

生成的计划ScalarGbAggToTop仅对流聚合计划的更改很小。看起来它从中进行扫描并对它应用反向排序，然后对分支NOT NULL和NULL分支使用反向排序。并且不进行任何额外的探索，看看是否有更高效的访问路径。

这意味着在所有行都是扫描NULL或NOT NULL其中一个扫描的病理情况下，最终将读取表中的所有行（如果适用于所有 4 个表，则在您的情况下为 50 亿）。即使存在混合NULL并且分支正在执行向后扫描NOT NULL的事实是次优的，因为在 SQL Server 中首先排序，所以将在索引的开头。IS NULLNULL

首先添加一个NOT NULL分支似乎在很大程度上是不必要的，因为没有它查询将返回相同的结果。我想它只需要它知道是否显示消息

警告：空值被聚合或其他 SET 操作消除。

但我怀疑你是否在乎。在这种情况下，添加显式WHERE ... NOT NULL可以解决问题。

WITH CTE AS
(
SELECT X FROM T1
UNION ALL
SELECT X FROM T2
)
SELECT MAX(X)
FROM CTE
WHERE X IS NOT NULL
;

它现在NOT NULL对索引的一部分进行搜索并向后读取（在从每个表中读取第一行后停止）

在此处输入图像描述

推荐阅读