Query tuning (4b7216a)

query.sql

SELECT [me].[id], [me].[site], [me].[channel], [me].[subject], [me].[message], [me].[date], [me].[description], [me].[context], ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] = '0' AND [children_alias].[parent_id] = [me].[id]
   ), ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] = '5' AND [children_alias].[parent_id] = [me].[id]
   ), ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] = '1' AND [children_alias].[parent_id] = [me].[id]
   ), ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] = '2' AND [children_alias].[parent_id] = [me].[id]
   ), ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] = '3' AND [children_alias].[parent_id] = [me].[id]
   ), ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] = '4' AND [children_alias].[parent_id] = [me].[id]
   ), ( 
    SELECT COUNT( * ) 
      FROM [LogChildren] [children_alias] 
      JOIN [LogStatus] [statuses] 
        ON [statuses].[child_id] = [children_alias].[id] 
    WHERE [statuses].[success] IS NULL AND [children_alias].[parent_id] = [me].[id]
   ), [destinations].[id], [destinations].[name], [destinations].[parent_id], [destinations].[description] 
  FROM ( 
    SELECT TOP 2147483647 [me].[id], [me].[site], [me].[channel], [me].[subject], [me].[message], [me].[date], [me].[description], [me].[context], [success_count], [info_count], [escalated_count], [failed_count], [skipped_count], [expanded_count], [unknown_count] 
      FROM ( 
        SELECT [me].[id], [me].[site], [me].[channel], [me].[subject], [me].[message], [me].[date], [me].[description], [me].[context], [success_count], [info_count], [escalated_count], [failed_count], [skipped_count], [expanded_count], [unknown_count],  ROW_NUMBER
() OVER ( 
            ORDER BY [date] DESC
           ) AS [rno__row__index] 
          FROM ( 
            SELECT [me].[id], [me].[site], [me].[channel], [me].[subject], [me].[message], [me].[date], [me].[description], [me].[context], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] = '0' AND [children_alias].[parent_id] = [me].[id]
               ) AS [success_count], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] = '5' AND [children_alias].[parent_id] = [me].[id]
               ) AS [info_count], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] = '1' AND [children_alias].[parent_id] = [me].[id]
               ) AS [escalated_count], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] = '2' AND [children_alias].[parent_id] = [me].[id]
               ) AS [failed_count], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] = '3' AND [children_alias].[parent_id] = [me].[id]
               ) AS [skipped_count], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] = '4' AND [children_alias].[parent_id] = [me].[id]
               ) AS [expanded_count], ( 
                SELECT COUNT( * ) 
                  FROM [LogChildren] [children_alias] 
                  JOIN [LogStatus] [statuses] 
                    ON [statuses].[child_id] = [children_alias].[id] 
                WHERE [statuses].[success] IS NULL AND [children_alias].[parent_id] = [me].[id]
               ) AS [unknown_count] 
              FROM [LogParents] [me] 
            WHERE [me].[channel] != '000' AND ( [site] IN ( 
                    SELECT [me].[serialnumber] 
                      FROM [Equipment] [me] 
                    WHERE [me].[user] = 'test'
                   ) OR [site] = 'Interactive/test' )
           ) [me]
       ) [me] 
    WHERE [rno__row__index] >= '1' AND [rno__row__index] <= '25'
   ) [me] 
  LEFT JOIN [LogDestinations] [destinations] 
    ON [destinations].[parent_id] = [me].[id] 
WHERE [me].[channel] != '000' AND ( [site] IN ( 
        SELECT [me].[serialnumber] 
          FROM [Equipment] [me] 
        WHERE [me].[user] = 'test'
       ) OR [site] = 'Interactive/test' ) 
ORDER BY [date] DESC
SELECT COUNT( * ) 
  FROM ( 
    SELECT [me].[id] 
      FROM [LogParents] [me] 
    WHERE [me].[channel] != '000' AND ( [site] IN ( 
            SELECT [me].[serialnumber] 
              FROM [Equipment] [me] 
            WHERE [me].[user] = 'test'
           ) OR [site] = 'Interactive/test' ) 
    GROUP BY [me].[id]
   ) [me]
SELECT [me].[channel] 
  FROM ( 
    SELECT [me].[id], [me].[site], [me].[channel], [me].[subject], [me].[message], [me].[date], [me].[description], [me].[context] 
      FROM [LogParents] [me] 
      JOIN [Equipment] [equipment] 
        ON [equipment].[serialnumber] = [me].[site] 
    WHERE [equipment].[user] = 'test' UNION 
    SELECT [me].[id], [me].[site], [me].[channel], [me].[subject], [me].[message], [me].[date], [me].[description], [me].[context] 
      FROM [LogParents] [me] 
    WHERE [me].[site] = 'Interactive/test'
   ) [me] 
GROUP BY [channel]

-- times: 2.41s, 858ms, 892ms