Query tuning (4b7216a)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment