Como posso tornar essa consulta aninhada mais eficiente?

Eu zombei de dados observando as contagens de linhas em suas tabelas, dando a elas uma distribuição de dados uniforme e fazendo suposições sobre o esquema:

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);

A suposição mais importante que fiz sobre o esquema é que a Idcoluna é a chave primária da [Conference]tabela. Isso parecia razoável, dado o plano de consulta e os nomes de índice envolvidos.

Na minha máquina, recebo o mesmo plano de consulta que você, mas minha consulta inicial leva apenas 163 ms de CPU. Presumo que as diferenças se resumem a diferenças de hardware, distribuição de dados e ao fato de não estar retornando dados ao cliente.

A primeira coisa que me chamou atenção é o desnecessário GROUP BYem sua confDatatabela derivada. Idé a chave primária da tabela para que você não precise de todos os agregados. Com os índices certos (que você já tem para este caso em particular), as subconsultas não são necessariamente uma coisa ruim. Reescrevendo o que você precisa remover o GROUP 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'));

Isso resulta na agregação de fluxo sendo empurrada ainda mais para o plano:

O plano carregado leva 113 ms de CPU. Os mesmos operadores estão presentes, mas alguns deles processam menos linhas, o que economiza tempo. Você pode tornar essa consulta mais eficiente definindo um índice de cobertura no [Conference]com Idcomo a chave de índice. Isso pode parecer uma coisa estranha de se fazer, mas sua verificação de índice clusterizado leva 10% do tempo geral de consulta e provavelmente inclui colunas que você não precisa.

Se você deseja tornar a consulta mais rápida, também pode considerar uma exibição indexada. Por que realizar a agregação sempre que você pode definir uma exibição indexada simples para fazer isso por você?

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]);

Isso resultará em um pouco mais de espaço e um pouco de sobrecarga ao fazer DML na mesa. No geral, eu diria que é um bom caso de uso para uma exibição indexada. Reescrevendo a consulta novamente:

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'));

O SQL Server concorda com minha avaliação de que é uma boa ideia e o tempo de CPU cai para 78 ms .

Na minha máquina consegui deixar a consulta ainda mais rápida, mas isso está começando a entrar em otimizações que são um pouco arriscadas porque podem exigir uma LOOP JOINdica. Essa dica pode não ser uma boa ideia, pois sua consulta ou os dados na tabela mudam. Também pode não ser um bom ajuste para o seu hardware. A ideia por trás dessa abordagem é criar um índice adequado [Conference]e aproveitar ao máximo o TOPcom um plano que só faz loops aninhados. Aqui está o índice que eu adicionei:

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

A execução da mesma consulta de antes com uma LOOP JOINdica me deu o seguinte plano:

Essa consulta levou apenas 58 ms de tempo de CPU. Vale a pena mencionar que notei que solicitar o plano real adiciona um pouco de sobrecarga relativa neste estágio. Todas as outras otimizações possíveis que me vêm à mente não são seguras para produção, então vou parar por aqui.

Como pensamento final, você realmente deseja retornar 1.000 linhas arbitrárias e o nome mínimo da conferência? Essas informações são úteis para seus usuários finais?