3

This question already has an answer here:

I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on:

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

(11063 row(s) affected)
Table 'Worktable'. Scan count 49743, logical reads 296781, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableA'. Scan count 7, logical reads 23690, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableB'. Scan count 18832, logical reads 38207, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableC'. Scan count 6, logical reads 5458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableD'. Scan count 2, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableE'. Scan count 2, logical reads 184, 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 = 688 ms,  elapsed time = 881 ms.

The results of SELECT TOP 25 * FROM MyView ORDER BY MyPrimaryKey:

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

(25 row(s) affected)
Table 'Worktable'. Scan count 48353, logical reads 801727, 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 'MyTableB'. Scan count 9360, logical reads 20006, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableA'. Scan count 4, logical reads 7359, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableD'. Scan count 1, logical reads 17944, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableE'. Scan count 2, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableC'. Scan count 5, logical reads 226, 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 = 29765 ms,  elapsed time = 30865 ms.

Why on earth are the execution times so drastically different?

marked as duplicate by Paul White, ypercubeᵀᴹ, RLF, Martin Smith, RolandoMySQLDBA Dec 15 '14 at 16:00

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 3
    Actual execution plans would be more beneficial than statistics I/O output. – Aaron Bertrand Dec 12 '14 at 19:13