sql - 子查询 vs MAX 聚合函数性能

Question

MAX 聚合与子查询:

这似乎是我最近写的查询中反复出现的问题，我想发现哪种查询样式是:

最高效(时间和资源)

更可靠、更易于维护

使用最有意义

更多信息:

我正在编写的查询往往总是从一个基表中提取，并且还会连接到其他几个表；然而，连接表通常具有垂直方向，其中外键被多次引用，具有唯一的“描述符”和“响应”。 (有关示例，请参见表 #MovieDescriptions。)

请使用以下 SQL 查询作为测试场景:

 -- Drop temp tables if exist

IF OBJECT_ID('TempDB..#Movies','U') IS NOT NULL
     DROP TABLE #Movies

IF OBJECT_ID('TempDB..#MovieDescriptions','U') IS NOT NULL
     DROP TABLE #MovieDescriptions

-- Creating temp tables

CREATE TABLE #Movies
(
     MovieID int IDENTITY(1,1),
     MovieName varchar (100),
     ReleaseYear datetime,
     Director varchar (100)
)

CREATE TABLE #MovieDescriptions
(
     MovieDescID int IDENTITY(1,1),
     FK_MovieID varchar(100),
     DescriptionType varchar(100),
     DescriptionResponse varchar(100)
)

-- Inserting test data

INSERT INTO #Movies (MovieName, ReleaseYear, Director) VALUES ('Gone With the Wind', CONVERT(datetime,'12/15/1939'), 'Victor Fleming')
INSERT INTO #Movies (MovieName, ReleaseYear, Director) VALUES ('2001: A Space Odyssey', CONVERT(datetime,'01/01/1968'), 'Stanley Kubrick')


INSERT INTO #MovieDescriptions (FK_MovieID, DescriptionType, DescriptionResponse) VALUES ('1', 'Written By', 'Sideny Howard')
INSERT INTO #MovieDescriptions (FK_MovieID, DescriptionType, DescriptionResponse) VALUES ('1', 'Genre', 'Drama')
INSERT INTO #MovieDescriptions (FK_MovieID, DescriptionType, DescriptionResponse) VALUES ('1', 'Rating', 'G')

INSERT INTO #MovieDescriptions (FK_MovieID, DescriptionType, DescriptionResponse) VALUES ('2', 'Written By', 'Standley Kubrick')
INSERT INTO #MovieDescriptions (FK_MovieID, DescriptionType, DescriptionResponse) VALUES ('2', 'Genre', 'Sci-Fi')
INSERT INTO #MovieDescriptions (FK_MovieID, DescriptionType, DescriptionResponse) VALUES ('2', 'Rating', 'G')

-- Using subqueries

SELECT
     MovieName,
     ReleaseYear,
     (SELECT DescriptionResponse
      FROM #MovieDescriptions
      WHERE FK_MovieID = #Movies.MovieID AND DescriptionType = 'Genre'
      ) AS Genre,
     (SELECT DescriptionResponse
      FROM #MovieDescriptions
      WHERE FK_MovieID = #Movies.MovieID AND DescriptionType = 'Rating'
      ) AS Rating
FROM #Movies

-- Using aggregate functions

SELECT
     MovieName,
     ReleaseYear,
     MAX(CASE WHEN md.DescriptionType = 'Genre' THEN DescriptionResponse END) AS Genre,
     MAX(CASE WHEN md.DescriptionType = 'Rating' THEN DescriptionResponse END) AS Rating
FROM #Movies m
     INNER JOIN #MovieDescriptions md
     ON m.MovieID = md.FK_MovieID
GROUP BY MovieName, ReleaseYear

此外，如果有更好的方法来选择这些数据，那也会很有帮助。

Answer 1

假设一个更正常的设置，您的表被正确索引，并且外键关系列具有匹配的数据类型(提示提示:它们当前不匹配， int 与 varchar )，那么您应该总是发现您的第二个查询(连接 + 聚合)优于第一个(select 子句中的子查询)。对于少量数据，它可能并不明显。但是您的基表中的数据越多( #Movies )，差异就越明显。

原因很简单。在您的第一个查询中:

SELECT
     MovieName,
     ReleaseYear,
     (SELECT DescriptionResponse
      FROM #MovieDescriptions
      WHERE FK_MovieID = #Movies.MovieID AND DescriptionType = 'Genre'
      ) AS Genre,
     (SELECT DescriptionResponse
      FROM #MovieDescriptions
      WHERE FK_MovieID = #Movies.MovieID AND DescriptionType = 'Rating'
      ) AS Rating
FROM #Movies

如 #Movies包含 1000 行，那么 SQL Server 真的别无选择，只能对 #Movies 执行一次全表扫描，并且，对于 1000 行中的每一行，它需要对 #MovieDescriptions 执行 2 个额外的查询。 .实际上，您总共执行了 2001 个查询。因为您的子查询在 SELECT 中子句，SQL Server 只能以这种方式执行查询。

另一方面，您的第二个查询:

SELECT
     MovieName,
     ReleaseYear,
     MAX(CASE WHEN md.DescriptionType = 'Genre' THEN DescriptionResponse END) AS Genre,
     MAX(CASE WHEN md.DescriptionType = 'Rating' THEN DescriptionResponse END) AS Rating
FROM #Movies m
     INNER JOIN #MovieDescriptions md
     ON m.MovieID = md.FK_MovieID
GROUP BY MovieName, ReleaseYear

因为您在这里使用联接，这使 SQL Server 可以灵活地找出联接来自 #Movies 的数据的最有效方法。和 #MovieDescriptions .根据您的索引、过滤器、行数等，它可能会决定进行哈希连接，也可能会使用嵌套循环等。关键是 SQL Server 有更多选择，现在可以找出最好的方法减少从 2 个表(和索引)读取的数据块数量。

编辑 :我还应该补充一点，以上假设您正在获取 每从查询返回的行。如果查询返回数千行，但您只获取前 10 行，那么在某些情况下，第一个查询实际上可能优于第二个查询。这是因为子查询只会在行被选中或获取时执行。如果您从不提取某些行，则可能永远不会产生对那些未提取的行执行子查询的成本。需要考虑的事情。