SQL 查询:优化查找最新执行 ID(最大值)是否为失败状态记录的方法

Question

我有一个SQL表，用于存储学生配置文件的作业同步状态。后台作业会根据作业执行状态为每个学生插入一个条目。例如，对于学生的任何属性更改，将在此表中插入一条记录。截至目前，这张桌子已经变得巨大的加班，大约有7000万条记录。

表架构如下所示

CREATE TABLE [dbo].[ProfileSync](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[StudentNumber] [int] NOT NULL,
[Success] [smallint] NOT NULL,
[EntityId] [int] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProfileSync] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ProfileSync_StudentNumber] ON [dbo].[ProfileSync]
(
[StudentNumber] ASC,
[EntityId] 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]

Success列表示三个值 1、0、-1，其中 1 表示成功状态，0 表示正在进行，-1 表示失败。EntityId是存储业务数据的 int 列。

有一个报告工具，可以从此表中获取数据并显示上次失败的学生同步配置文件的状态。查询的编写方式如下

SELECT 
PS.EntityId, PS.StudentNumber
FROM 
(
SELECT
StudentNumber, 
MAX(Id) Id
FROM [dbo].[ProfileSync] 
GROUP BY StudentNumber
) AS A INNER JOIN [dbo].[ProfileSync] PS ON A.Id = PS.Id AND Success = -1

上面的查询需要很长时间才能执行(在我的本地机器上大约需要 20 秒)，这似乎是正确的，因为它正在扫描表的所有行。有没有其他方法可以编写优化查询，使其不扫描整个表以获取所需的内容？

我也尝试过使用 CTE，但它没有提供更好的结果(更差的结果)

;WITH Results_CTE AS
(
SELECTs
StudentNumber, Id,
ROW_NUMBER() OVER (Partition By StudentNumber ORDER BY Id desc) AS RowNum
FROM [dbo].[ProfileSync] 
)
SELECT PS.SyncStatusId, PS.StudentNumber
FROM [dbo].[ProfileSync] PS
INNER Join Results_CTE R ON R.Id = PS.Id
WHERE R.RowNum = 1 AND PS.Success = -1

请建议编写查询的优化方法？

注意 - 我已将实际业务实体的名称更改为表示为 Student 表/对象。在实际的企业应用中，业务实体/表的名称完全不同。由于会有与存档此表中的记录或其他设计更改相关的建议，因此请不要偏离与其优化相关的查询。

Answer 1

您应该通过尽早筛选它们来减少聚合操作的记录。 试试这样的事情——

SELECT PS.SyncStatusId, PS.StudentNumber
FROM 
(
Select StudentNumber, MAX(ID) id from [dbo].[ProfileSync]
WHERE  StudentNumber IN (
SELECT
StudentNumber
FROM [dbo].[ProfileSync] 
WHERE Success = -1
)
GROUP BY StudentNumber
) AS A INNER JOIN [dbo].[ProfileSync] PS ON A.Id = PS.Id and Success = -1

Answer 2

我想我完全错了......试试这个修改后的查询：

SELECT TOP 1 WITH TIES 
PS.SyncStatusId
,PS.StudentNumber 
FROM [dbo].[ProfileSync] PS 
WHERE PS.Success=-1
ORDER BY ROW_NUMBER() OVER (PARTITION BY PS.StudentNumber ORDER BY LastModifiedDate DESC)

TOP 1 WITH TIES与ORDER BY ROW_NUMBER() OVER(PARTITION BY xyz ORDER BY abc)的组合将返回所有行，并得到1row_number。

Success索引将有助于快速减少这种情况，LastModifiedDate索引将支持排序。