我怎样才能使这个嵌套查询更有效率？

我通过查看表中的行数模拟数据，为它们提供均匀的数据分布，并对模式进行猜测：

DROP TABLE IF EXISTS [Room];

CREATE TABLE [Room] (
    [Id] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Room] WITH (TABLOCK)
SELECT TOP (3088) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 200)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS [Conference];

CREATE TABLE [Conference] (
    [Id] BIGINT NOT NULL,
    [Name] VARCHAR(30) NOT NULL,
    [RoomId] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);


INSERT INTO [Conference] WITH (TABLOCK)
SELECT RN
, 'MY FAVORITE MEETING ROOM'
, 1 + RN % 3088
, REPLICATE('Z', 200)
FROM
(
    SELECT TOP (97413) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS [Participant];

CREATE TABLE [Participant] (
    [Id] BIGINT NOT NULL,
    [ConferenceId] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);


INSERT INTO [Participant] WITH (TABLOCK)
SELECT RN
, 1 + RN % 97413
, REPLICATE('Z', 200)
FROM
(
    SELECT TOP (235323) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);


CREATE INDEX NCI_Part ON [Participant] ([ConferenceId]) INCLUDE (Id);

我对架构所做的最重要的假设是Id列是[Conference]表的主键。考虑到查询计划和涉及的索引名称，这似乎是合理的。

在我的机器上，我获得了与您相同的查询计划，但我的起始查询仅占用 163 毫秒的 CPU。我假设差异归结为硬件、数据分布的差异，以及我没有将数据返回给客户端这一事实。

我首先想到的是派生表GROUP BY中的不必要项。是表的主键，因此您不需要所有聚合。有了正确的索引（对于这种特殊情况，您已经有了），子查询不一定是坏事。重写你必须删除的内容：confDataIdGROUP BY

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    conf.[Name] AS [ConferenceName],
    conf.[RoomId] AS [RoomId],
    (
        SELECT COUNT(part.[Id])
        FROM [Participant] part
        WHERE part.[ConferenceId] = conf.[Id]
    ) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

这导致流聚合被进一步下推到计划中：

上传的计划占用 113 毫秒的 CPU。存在相同的运算符，但其中一些运算符处理的行数较少，从而节省了时间。您可以通过在[Conference]with上定义覆盖索引Id作为索引键来提高此查询的效率。这似乎是一件奇怪的事情，但您的聚簇索引扫描占用了总查询时间的 10%，并且可能包括您不需要的列。

如果你想让查询更快，你也可以考虑索引视图。当您可以定义一个简单的索引视图来为您执行聚合时，为什么每次都执行聚合？

CREATE VIEW IndexedViewOnParticipant WITH SCHEMABINDING
AS
SELECT [ConferenceId], COUNT_BIG([Id]) CntId, COUNT_BIG(*) Cnt
FROM dbo.[Participant]
GROUP BY [ConferenceId];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON IndexedViewOnParticipant ([ConferenceId]);

这将导致在表上执行 DML 时会多一点空间和一点点开销。总的来说，我认为这是索引视图的一个很好的用例。再次重写查询：

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM 
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    conf.[Name] AS [ConferenceName],
    conf.[RoomId] AS [RoomId],
    (
        SELECT CntId
        FROM IndexedViewOnParticipant part WITH (NOEXPAND)
        WHERE part.[ConferenceId] = conf.[Id]
    ) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

SQL Server 同意我的评估，这是一个好主意，CPU 时间下降到 78 ms。

在我的机器上，我能够使查询更快，但是这开始进入有点冒险的优化，因为它可能需要LOOP JOIN提示。当您的查询或表中的数据发生变化时，该提示可能不是一个好主意。它也可能不适合您的硬件。这种方法背后的想法是创建一个合适的索引[Conference]并充分利用TOP只执行嵌套循环的计划。这是我添加的索引：

CREATE INDEX NCI_Conf ON [Conference] ([RoomId]) INCLUDE ([Name]);

运行与之前相同的查询并LOOP JOIN提示我得到以下计划：

该查询只占用了 58 毫秒的 CPU 时间。值得一提的是，我注意到在这个阶段请求实际计划会增加相当多的相对开销。我想到的所有其他可能的优化对于生产来说都是不安全的，所以我会在这里停下来。

最后想一想，您真的要返回 1000 行任意行和最小会议名称吗？这些信息对您的最终用户有用吗？