arnipetursson (11/12/2013)
When I run the two queries (outer join vs NOT EXISTS),the outer join query runs in 4-9ms (69 logical IO n the temp tables and 0 scans and 0 logical IO on the work table)
and the not exists query runs in 28-32ms (109 logical IO on the temp tables and 50 scans and 5079 logical IO on the work table).
It looks like the #CourseCompleted table is scanned 50 times in the NOT EXISTS query, whereas only 30 times in the outer join query.
I wonder if these results hold over larger data sets.
After adding UPDATE STATISTICS on the #tables + dropping the result in a temp-table and putting FREEPROCCACHE & DROPCLEANBUFFERS in front of each run, the results are returned in something like 14ms vs 13ms here respectively.
Even if it would be the other way around, in such 'close-cases' readability easily trumps any (small) performance hit IMHO.
When upping the rowcount dramatically the difference grows a little it seems; but relatively spoken they stick together quite well.
DECLARE @NumberOfCourse INT = 3000;
DECLARE @NumberOfEmployees INT = 50000;
=> (55181680 row(s) affected)
[run 1] SQL Server Execution Times: CPU time = 106922 ms, elapsed time = 109345 ms.
[run 2] SQL Server Execution Times: CPU time = 110672 ms, elapsed time = 113376 ms.
vs
[run 1] SQL Server Execution Times: CPU time = 101938 ms, elapsed time = 104593 ms.
[run 2] SQL Server Execution Times: CPU time = 99031 ms, elapsed time = 101675 ms.
Mind you that this is on a laptop that has plenty of stuff running in the background so don't take these numbers as 'hard evidence', they're a guideline at best.
In my experience I've always found both approaches to be equivalent as far as execution times go.