正确索引UNPIVOT SQL查询

Question

如果我有一个表，比如：

CREATE TABLE Students
(
Id INT PRIMARY KEY IDENTITY,
StudentName VARCHAR (50),
Math INT,
English INT,
History INT,
Science INT
)
GO

以及CCD_ 1查询，例如：

SELECT StudentName, Course, Score
FROM Students
UNPIVOT
(
Score
FOR Course in (Math, English, History, Science)
) AS SchoolUnpivot

最佳指数是什么样子的？

Answer 1

我将1.000.000个测试数据填充到Students表中，然后开始测试以下查询；

注意不要在生产环境中使用DBCC DROPCLEANBUFFERS语句。

测试环境：

Microsoft SQL Server 2019(RC1(-15.0.1900.25(X64(2019年8月16日14:20:53版权所有(C(2019 Microsoft Corporation Developer Edition(64位(在Windows 10 Pro 10.0上(内部版本17763:(

测试-1:

以下查询需要34秒。

DBCC DROPCLEANBUFFERS
GO
SELECT StudentName, Course, Score
FROM Students
CROSS APPLY (
VALUES 
('Math', Math),
('English', English),
('History', History),
('Science', Science)
) x(Course, Score)
WHERE Score IS NOT NULL
OPTION (MAXDOP 1)

测试-2:
以下查询需要40秒。

DBCC DROPCLEANBUFFERS
GO
SELECT StudentName, Course, Score
FROM Students
CROSS APPLY (
VALUES 
('Math', Math),
('English', English),
('History', History),
('Science', Science)
) x(Course, Score)
WHERE Score IS NOT NULL
OPTION (MAXDOP 1)

测试-3:

以下查询在创建索引后需要32秒，而且执行计划在执行计划中使用创建的索引。

CREATE NONCLUSTERED INDEX [PerformanceIndex] ON [dbo].[Students]
(
[Id] ASC,
[Math] ASC,
[English] ASC,
[History] ASC,
[Science] ASC
)
INCLUDE([StudentName])
GO
DBCC DROPCLEANBUFFERS
GO
SELECT StudentName, Course, Score
FROM Students
UNPIVOT
(
Score
FOR Course in (Math, English, History, Science)
) AS SchoolUnpivo
OPTION (MAXDOP 1)

因此，在非聚集索引中使用unpivot列可以帮助我们提高查询性能，尤其是在这种情况下。

Answer 2

我不确定哪些索引可以用于优化查询。需要查看查询的执行计划，才能提出有根据的建议。

但是，在进行索引之前，您可以尝试优化查询。加快性能的一个选项是使用CROSS APPLY VALUES而不是UNPIVOT。下面的博客文章详细介绍了它可以提供的性能改进。

因此，考虑以下查询：

SELECT StudentName, Course, Score
FROM Students
CROSS APPLY (
VALUES 
('Math', Math),
('English', English),
('History', History),
('Science', Science)
) x(Course, Score)
WHERE Score IS NOT NULL

我很想知道你是否能获得博客作者报道的同样的性能优势。