1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How can I make this nested query more efficient?

Discussion in 'Programming/Internet' started by Jez, Oct 8, 2018.

  1. Jez

    Jez Guest

    I have 3 tables: Room, Conference, and Participant. Room has many Conferences, and Conference has many Participants. 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 Participants 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:

    [​IMG]

    Is there a way I can make this kind of query - where I calculate an aggregate of a subquery's aggregate - more efficient?

    Login To add answer/comment
     

Share This Page