Monster SQL, 350ms vs 4.5s

gistfile1.sql

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'