Consider the following setup. There are three tables involved #CCP_DETAILS_TEMP,Period and ACTUALS_DETAILS
#CCP_DETAILS_TEMP will have 50000 records, ACTUALS_DETAILS can have 5000000 records and period table will have 2000 records
Index details:
I have a requirement for which I wrote three different ways to achieve the result. Now I want to know which one is better.
All three queries are running more in more or less in same time. I need some experts advice on which one will perform better. Is there any disadvantage in any of the approach
Approach 1: Outer Apply
Time taken: 4615 Milli Seconds
Query statistics:
Table 'PERIOD'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#CCP_DETAILS_TEMP'. Scan count 16, logical reads 688, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 16, logical reads 807232, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'ACTUALS_DETAILS'. Scan count 1200000, logical reads 3859053,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times: CPU time = 36796 ms, elapsed time =
4615 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Approach 2: Left Join
Time taken: 4293 Milli Seconds
Query statistics:
Table 'ACTUALS_DETAILS'. Scan count 17, logical reads 37134, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'PERIOD'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#CCP_DETAILS_TEMP'. Scan count 16, logical reads 688, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 16, logical reads 807232, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times: CPU time = 7983 ms, elapsed time =
4293 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Approach 3: Aggregating first and Left join:
Time taken: 4200 Milli Seconds
Query statistics:
Table 'ACTUALS_DETAILS'. Scan count 17, logical reads 37134, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 16, logical reads 807232, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'PERIOD'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#CCP_DETAILS_TEMP'. Scan count 16, logical reads 688, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times: CPU time = 7731 ms, elapsed time =
4200 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.