|
GreatInca
Posting Yak Master
102 Posts |
 Posted - 2004-11-29 : 17:22:58
|
| Notice the 'SQL Server parse and compile time: ' vs the CPU / run time of the 2 Queries. The 2nd query is the original; the 1st query is the revised & optimised (some functionality change included).SQL Server parse and compile time: CPU time = 62 ms, elapsed time = 76 ms.(1 row(s) affected)These have been run a few times so there is no physical I/O and the plan might have been in cache so it might be even 62/76ms for just parsing. Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.Table 'Table3'. Scan count 16, logical reads 39, physical reads 0, read-ahead reads 0.Table 'Table4'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0.Table 'Table5'. Scan count 117, logical reads 235, physical reads 0, read-ahead reads 0.Table 'Table6'. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0.Table 'Table7'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.(1 row(s) affected)Table 'Table1'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0.Table 'Table2'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.Table 'Table3'. Scan count 139, logical reads 304, physical reads 0, read-ahead reads 0.Table 'Table4'. Scan count 17, logical reads 17, physical reads 0, read-ahead reads 0.Table 'Table5'. Scan count 126, logical reads 253, physical reads 0, read-ahead reads 0.Table 'Table6'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0.Table 'Table7'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 12 ms.Sorry, Had to rename tables. Area 51 Top Secret Corporate edition.****************************************Check out my pictures at http://www.pbase.com/GreatIncaCheck out my company's site!http://www.jobing.com (Market picker for first visit)http://phoenix.jobing.com (corporate home market if you don't live in a covered market) |
|
Great example on why to use stored procedures