sql - 即使有索引，对数百万条记录的 SUM 查询也很慢，我该如何优化？

Question

我有一个包含大约 350 万条记录的数据库表。该表保存契约(Contract)数据记录，有金额、日期和一些与其他表相关的ID(VendorId、AgencyId、StateId)，这是数据库表:

CREATE TABLE [dbo].[VendorContracts]
(
    [Id] [uniqueidentifier] NOT NULL,   
    [ContractDate] [datetime2](7) NOT NULL,
    [ContractAmount] [decimal](19, 4) NULL, 
    [VendorId] [uniqueidentifier] NOT NULL,
    [AgencyId] [uniqueidentifier] NOT NULL,
    [StateId] [uniqueidentifier] NOT NULL,

    [CreatedBy] [nvarchar](max) NULL,
    [CreatedDate] [datetime2](7) NOT NULL,
    [LastModifiedBy] [nvarchar](max) NULL,
    [LastModifiedDate] [datetime2](7) NULL,
    [IsActive] [bit] NOT NULL,

    CONSTRAINT [PK_VendorContracts] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我的网站上有一个页面，我希望用户能够在其中选择供应商，并查看按州划分的契约(Contract)总金额。

我有这个问题:

SELECT SUM(ContractAmount), StateId
FROM [dbo].[VendorContracts]
WHERE VendorId = '...'
GROUP BY StateId

我创建了这个索引来帮助解决这个问题:

CREATE NONCLUSTERED INDEX [IX_VendorContracts_VendorId] ON [dbo].[VendorContracts]
(
    [VendorId] ASC
)
INCLUDE([Id],[StateId],[ContractAmount],[ContractDate],[AgencyId]) 
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

这对我的大多数供应商都非常有效，我可以在一两秒内很快得到结果。但是有几个供应商，每个供应商都有大约 100 万条记录，而这些需要很长时间，通常是几分钟。

所以我想知道是否有任何方法可以通过修改索引来进一步优化？还是其他方式？

或者我可能做错了？不确定对 100 万条记录运行如此庞大的 SUM 查询是否不是执行此操作的好方法。记录不会经常添加到该表中，我们可能每月添加几千条记录一次或两次，所以我在考虑可能有一个夜间进程来获取 SUM 数据并将它们存储在一个单独的表，然后在我需要运行报告时查询它。

Answer 1

你应该接受查询...

SELECT SUM(ContractAmount) AS TotalContractAmount,
       VendorId,
       StateId
FROM   [dbo].[VendorContracts]
GROUP  BY VendorId,
          StateId

... 并将其变成 indexed view因此结果由系统自动预先计算和维护(在 VendorId, StateId 上具有唯一的聚集索引)。

索引 View 可能有数据修改的开销，但你说

Records aren't added very frequently to this table, we maybe add afew thousand once or twice a month,

SQL Server 应该在 Azure SQL 数据库上自动匹配索引 View ，并识别原始查询可以由索引 View 提供服务。

如果您发现这种匹配没有发生(验证执行计划)，那么您可能需要求助于从索引 View 中明确选择并使用 NOEXPAND 提示。