I have 3 tables: Room
, Conference
, and Participant
. Room
has many Conference
s, and Conference
has many Participant
s. I need my query to display the fields from Room
, as well as the number of associated Conferences
it has, and the sum of the number of associated Participant
s each Conference
has. Here's a cut-down version of the SELECT
query I wrote to get this info; first, I just selected the room ID:
SELECT TOP(1000)
rm.[Id]
FROM
[Room] rm
LEFT JOIN (
SELECT
conf.[Id] AS [ConferenceId],
MIN(conf.[Name]) AS [ConferenceName],
MIN(conf.[RoomId]) AS [RoomId],
COUNT(part.[Id]) AS CalcConferenceParticipantCount
FROM
[Conference] conf
LEFT JOIN
[Participant] part on part.[ConferenceId] = conf.[Id]
GROUP BY
conf.[Id]
) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
rm.[Id]
This was very fast as SQL Server was able to just pull the data from Room
and pretty much ignore the subquery (see Trial 1 - Trial 4 in image below). Then I added in the ConferenceName
field from the subquery, as well as a count of the number of conferences per room:
SELECT TOP(1000)
rm.[Id],
COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
MIN(confData.[ConferenceName])
FROM
[Room] rm
LEFT JOIN (
SELECT
conf.[Id] AS [ConferenceId],
MIN(conf.[Name]) AS [ConferenceName],
MIN(conf.[RoomId]) AS [RoomId],
COUNT(part.[Id]) AS CalcConferenceParticipantCount
FROM
[Conference] conf
LEFT JOIN
[Participant] part on part.[ConferenceId] = conf.[Id]
GROUP BY
conf.[Id]
) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
rm.[Id]
This slowed down the query quite a bit, by a factor of about 100 (see Trial 5 - Trial 7 in image below). I then added in the participant count from the subquery, meaning there were 2 levels of aggregate functions being used:
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],
MIN(conf.[Name]) AS [ConferenceName],
MIN(conf.[RoomId]) AS [RoomId],
COUNT(part.[Id]) AS CalcConferenceParticipantCount
FROM
[Conference] conf
LEFT JOIN
[Participant] part on part.[ConferenceId] = conf.[Id]
GROUP BY
conf.[Id]
) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
rm.[Id]
This further slowed down the query by a factor of about 4 (see Trial 8 - Trial 10 in image below). Here's the client statistics with data on the 10 trials:
Here's the query plan of the slow query: https://www.brentozar.com/pastetheplan/?id=SJpyeec5Q
Is there a way I can make this kind of query - where I calculate an aggregate of a subquery's aggregate - more efficient?