-
-
Save frioux/3bf46454730308725c07 to your computer and use it in GitHub Desktop.
Monster SQL, 350ms vs 4.5s
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 [id], [start_date], [end_date], [is_active], [status], [location_tests_count], [failed_location_tests_count], [device_tests_count], [all_computers_count], [failed_computers_count], [untested_computers_count], [succeeded_computers_count] | |
FROM ( | |
SELECT [id], [start_date], [end_date], [is_active], [status], [location_tests_count], [failed_location_tests_count], [device_tests_count], [all_computers_count], [failed_computers_count], [untested_computers_count], [succeeded_computers_count] ROW_NUMBER() OVER ( | |
ORDER BY [me].[start_date] DESC | |
) AS [rno__row__index] | |
FROM ( | |
SELECT [me].[id], [me].[start_date], [me].[end_date], [me].[is_active], [me].[status], ( | |
SELECT COUNT( * ) | |
FROM [Test_Computer] [test_computer_links_alias] | |
WHERE [test_computer_links_alias].[test_id] = [me].[id] | |
) AS [location_tests_count], ( | |
SELECT COUNT( * ) | |
FROM [Test_Computer] [test_computer_links_alias] | |
LEFT JOIN [Test_ComputerResults] [test_computer_result] | |
ON [test_computer_result].[test_computer_id] = [test_computer_links_alias].[id] | |
LEFT JOIN [Locations] [location] | |
ON [location].[test_computer_result_id] = [test_computer_result].[id] | |
WHERE [location].[corrected_location] IS NOT NULL AND [test_computer_links_alias].[test_id] = [me].[id] | |
) AS [failed_location_tests_count], ( | |
SELECT COUNT( * ) | |
FROM [Test_Device] [test_device_links_alias] | |
WHERE [test_device_links_alias].[test_id] = [me].[id] | |
) AS [device_tests_count], ( | |
SELECT COUNT( * ) | |
FROM ( | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Computer] [test_computer_links_alias] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [test_computer_links_alias].[computer_id] | |
WHERE [test_computer_links_alias].[test_id] = [me].[id] UNION | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Device] [test_device_links_alias] | |
JOIN [Devices] [device] | |
ON [device].[id] = [test_device_links_alias].[device_id] | |
JOIN [Computer_Device] [computer_device_links] | |
ON [computer_device_links].[device_id] = [device].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [computer_device_links].[computer_id] | |
WHERE [test_device_links_alias].[test_id] = [me].[id] | |
) [computer] | |
) AS [all_computers_count], ( | |
SELECT COUNT( * ) | |
FROM ( | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Computer] [test_computer_links_alias] | |
LEFT JOIN [Test_ComputerResults] [test_computer_result] | |
ON [test_computer_result].[test_computer_id] = [test_computer_links_alias].[id] | |
LEFT JOIN [Locations] [location] | |
ON [location].[test_computer_result_id] = [test_computer_result].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [test_computer_links_alias].[computer_id] | |
WHERE [location].[corrected_location] IS NOT NULL AND [test_computer_links_alias].[test_id] = [me].[id] UNION | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Device] [test_device_links_alias] | |
LEFT JOIN [TestResults] [test_result] | |
ON [test_result].[test_device_id] = [test_device_links_alias].[id] | |
LEFT JOIN [TestResult_TestResultType] [test_result_test_result_type_links] | |
ON [test_result_test_result_type_links].[test_result_id] = [test_result].[id] | |
JOIN [Devices] [device] | |
ON [device].[id] = [test_device_links_alias].[device_id] | |
JOIN [Computer_Device] [computer_device_links] | |
ON [computer_device_links].[device_id] = [device].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [computer_device_links].[computer_id] | |
WHERE [test_result_test_result_type_links].[is_success] = '0' AND [test_device_links_alias].[test_id] = [me].[id] | |
) [computer] | |
) AS [failed_computers_count], ( | |
SELECT COUNT( * ) | |
FROM ( | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Computer] [test_computer_links_alias] | |
LEFT JOIN [Test_ComputerResults] [test_computer_result] | |
ON [test_computer_result].[test_computer_id] = [test_computer_links_alias].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [test_computer_links_alias].[computer_id] | |
WHERE [test_computer_result].[id] IS NULL AND [test_computer_links_alias].[test_id] = [me].[id] UNION | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Device] [test_device_links_alias] | |
LEFT JOIN [TestResults] [test_result] | |
ON [test_result].[test_device_id] = [test_device_links_alias].[id] | |
JOIN [Devices] [device] | |
ON [device].[id] = [test_device_links_alias].[device_id] | |
JOIN [Computer_Device] [computer_device_links] | |
ON [computer_device_links].[device_id] = [device].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [computer_device_links].[computer_id] | |
WHERE [test_result].[id] IS NULL AND [test_device_links_alias].[test_id] = [me].[id] | |
) [computer] | |
) AS [untested_computers_count], ( | |
SELECT COUNT( * ) | |
FROM [Computers] [me] | |
WHERE [id] IN ( | |
SELECT [computer].[id] | |
FROM ( | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Computer] [test_computer_links_alias] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [test_computer_links_alias].[computer_id] | |
WHERE [test_computer_links_alias].[test_id] = [me].[id] UNION | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Device] [test_device_links_alias] | |
JOIN [Devices] [device] | |
ON [device].[id] = [test_device_links_alias].[device_id] | |
JOIN [Computer_Device] [computer_device_links] | |
ON [computer_device_links].[device_id] = [device].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [computer_device_links].[computer_id] | |
WHERE [test_device_links_alias].[test_id] = [me].[id] | |
) [computer] | |
) AND [id] NOT IN ( | |
SELECT [computer].[id] | |
FROM ( | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Computer] [test_computer_links_alias] | |
LEFT JOIN [Test_ComputerResults] [test_computer_result] | |
ON [test_computer_result].[test_computer_id] = [test_computer_links_alias].[id] | |
LEFT JOIN [Locations] [location] | |
ON [location].[test_computer_result_id] = [test_computer_result].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [test_computer_links_alias].[computer_id] | |
WHERE [location].[corrected_location] IS NOT NULL AND [test_computer_links_alias].[test_id] = [me].[id] UNION | |
SELECT [computer].[id], [computer].[name], [computer].[group_id], [computer].[user], [computer].[version], [computer].[is_active] | |
FROM [Test_Device] [test_device_links_alias] | |
LEFT JOIN [TestResults] [test_result] | |
ON [test_result].[test_device_id] = [test_device_links_alias].[id] | |
LEFT JOIN [TestResult_TestResultType] [test_result_test_result_type_links] | |
ON [test_result_test_result_type_links].[test_result_id] = [test_result].[id] | |
JOIN [Devices] [device] | |
ON [device].[id] = [test_device_links_alias].[device_id] | |
JOIN [Computer_Device] [computer_device_links] | |
ON [computer_device_links].[device_id] = [device].[id] | |
JOIN [Computers] [computer] | |
ON [computer].[id] = [computer_device_links].[computer_id] | |
WHERE [test_result_test_result_type_links].[is_success] = '0' AND [test_device_links_alias].[test_id] = [me].[id] | |
) [computer] | |
) | |
) AS [succeeded_computers_count] | |
FROM ( | |
SELECT [me].[id], [me].[start_date], [me].[end_date], [me].[is_active], 'completed' as status | |
FROM [Tests] [me] | |
WHERE [me].[end_date] < GETUTCDATE( ) UNION | |
SELECT [me].[id], [me].[start_date], [me].[end_date], [me].[is_active], 'in progress' as status | |
FROM [Tests] [me] | |
WHERE [me].[end_date] >= GETUTCDATE( ) AND [me].[start_date] <= GETUTCDATE( ) UNION | |
SELECT [me].[id], [me].[start_date], [me].[end_date], [me].[is_active], 'scheduled' as status | |
FROM [Tests] [me] | |
WHERE [me].[start_date] > GETUTCDATE( ) | |
) [me] | |
) [me] | |
) [me] | |
WHERE [rno__row__index] >= '1' AND [rno__row__index] <= '25' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment