Skip to content

Instantly share code, notes, and snippets.

@frioux frioux/query.sql
Last active Jan 4, 2016

Embed
What would you like to do?
Query tuning (4b7216a)
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
You can’t perform that action at this time.