性能优化 Row_Number() 通过分区：

Question

我一直在尝试优化表（Table1）和子查询之间存在左连接的存储过程的性能。当部门表中的记录增加时，速度会变慢；我认为，这是由于 ROW_NUMBER 函数造成的。它适用于部门表中记录数量较少的情况。 Table1 和存储过程末尾的子查询之间有一个左联接。

下面是示例表 Table1、Employee 和 Department，涉及运行缓慢的查询。

CREATE TABLE [Table1]
(
    [ID] [int] NOT NULL PRIMARY KEY, 
    [Name] VARCHAR(250) NOT NULL,
    [DepartmentID] int 
) ON [PRIMARY] 

INSERT [Table1] ([ID], [Name], [DepartmentID])  
VALUES (1, N'A', 1), (2, N'D', 2),  
       (3, N'C', 3), (4, N'E', 4),
       (5, N'D', 5), (6, N'A', 6),
       (7, N'B', 7)  
GO 

CREATE TABLE [Department]
(
    [DepartmentID] [int] NOT NULL PRIMARY KEY, 
    [Name] VARCHAR(250) NOT NULL, 
) ON [PRIMARY] 

INSERT [Department] ([DepartmentID], [Name])  
VALUES (1, N'Engineering'), (2, N'Administration'),  
       (3, N'Sales'), (4, N'Marketing'), 
       (5, N'Finance') 
GO 

CREATE TABLE [Employee]
( 
    [EmployeeID] [int] NOT NULL PRIMARY KEY, 
    [FirstName] VARCHAR(250) NOT NULL, 
    [LastName] VARCHAR(250) NOT NULL, 
    [DepartmentID] [int] NOT NULL 
         REFERENCES [Department](DepartmentID), 
) ON [PRIMARY] 
GO

INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (1, N'Orlando', N'Gee', 1), (2, N'Keith', N'Harris', 2), 
       (3, N'Donna', N'Carreras', 3), (4, N'Janet', N'Gates', 3) 

运行缓慢的示例查询。

SELECT 
    *
FROM
    Table1 AS t
LEFT JOIN 
    (SELECT 
         D.Name,
         E.DepartmentID,
         ROW_NUMBER() OVER (PARTITION BY E.DepartmentID
                            ORDER BY D.DepartmentID ASC,
                                     CASE WHEN D.Name IS NULL THEN 1 ELSE 0 END ASC, D.Name ASC) AS ord_index
     FROM 
         Department AS D
     INNER JOIN 
         Employee AS E ON D.DepartmentID = E.DepartmentID) AS x ON x.DepartmentID = t.DepartmentID
WHERE 
    x.ord_index = 1 OR x.ord_index IS NULL

我尝试根据调整顾问的建议添加索引，但它并没有提高性能。我也尝试使用 APPLY，但由于 ROW_NUMBER 函数而无法弄清楚。不过，我一直在尝试浏览平台上的相关标题。

我很感谢任何优化此类查询的指南。

Answer 1

您可以重构查询以使用外部应用，这可能会产生更好的执行计划（取决于支持的索引），例如：

select * from Table1 t 
outer apply (
    select D.Name,
        Row_Number() over (partition by E.DepartmentID order by D.DepartmentID asc, 
        case when D.Name is null then 1 
        else 0 end asc, D.Name asc) as ord_index 
    from Department D 
    join Employee E on D.DepartmentID = E.DepartmentID
    where E.DepartmentID = t.DepartmentID 
) x 
where x.ord_index = 1 or x.ord_index is null

请参阅DB<>Fiddle 显示更好的计划

Answer 2

在查询中，难以索引的部分是CASE WHEN D.Name IS NULL THEN 1 ELSE 0 END。但是，如果您愿意在 Department 表中添加额外的生成列，则可以对其建立索引。例如：

alter table Department add name_flag as 
CASE WHEN D.Name IS NULL THEN 1 ELSE 0 END;

create index ix1 on Department (DepartmentID, name_flag, name);

有了新列和索引，查询可以改写为：

SELECT * 
FROM Table1 t 
LEFT JOIN (
  SELECT D.Name, D.DepartmentID,  
    ROW_NUMBER() OVER (
      PARTITION BY D.DepartmentID  ORDER BY name_flag, D.Name
    ) AS ord_index
  FROM Department D 
  INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID
) x ON x.DepartmentID = t.DepartmentID and x.ord_index = 1