sql server execution plan - which query performance well

Question

I am learning SQL Server 2008 execution plan and for testing I have run two queries and tried to compare the results.

Is first query running faster than the second one? I normally check the execution cost (performance and cost).

First Query

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(931 row(s) affected)
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 'Visits'. Scan count 2, logical reads 1235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Assignments'. Scan count 1, logical reads 4657, 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 = 203 ms,  elapsed time = 198 ms.

Second Query

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)
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 'Visits'. Scan count 1, logical reads 19335, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Assignments'. Scan count 1, logical reads 4657, 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 = 109 ms,  elapsed time = 250 ms.

Answer 1

The first one is faster, according to the statistics output. Things to look for in there include the elapsed time, and number of reads/writes.

1st query:

SQL Server Execution Times: CPU time = 203 ms, elapsed time = 198 ms.

2nd query:

SQL Server Execution Times: CPU time = 109 ms, elapsed time = 250 ms.

The first query performs slightly faster. Milliseconds might seem trivial, but consider that for a query that is run literally thousands of times a day, and all these tiny bits of seconds will start adding up quickly.

Another thing to look for is the reads and writes (which will represent the amount of work that has to be done to get the resultset retrieved.

1st query:

(931 row(s) affected) 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 'Visits'. Scan count 2, **logical reads 1235**, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Assignments'. Scan count 1, logical reads 4657, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

2nd query:

(1 row(s) affected) 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 'Visits'. Scan count 1, **logical reads 19335**, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Assignments'. Scan count 1, logical reads 4657, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Logical reads in this case represent data retrieved from the buffer cache, which is less expensive than physical reads, where the data is retrieved from the physical disk (and that's expensive). Still, less is better.

In this case, your first query runs in less time (elapsed time = 198ms vs 250ms), and requires less reads than the second query, (1235 vs 19335), and therefore is preferred over the second one.

One additional tip, in the execution plan, thicker bars represent more data being passed. However, while they can give you a visual clue, be sure to validate the results of it, as I've experienced situations where the displayed execution plan was not accurate.