sql server - Query performance comparision -
on mssql server, ran 2 queries returned same records different statistics. can guide me on 1 better use reporting purpose, following statistics:
query 1:
sql server parse , compile time: cpu time = 359 ms, elapsed time = 602 ms. (5517 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 'table1'. scan count 5972, logical reads 37742, physical reads 87, read-ahead reads 32, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table2'. scan count 1, logical reads 3061, physical reads 2, read-ahead reads 3084, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table3'. scan count 1, logical reads 4171, physical reads 2, read-ahead reads 4210, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table4'. scan count 1, logical reads 843, physical reads 3, read-ahead reads 874, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table5'. scan count 1, logical reads 6899, physical reads 2, read-ahead reads 6898, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table6'. scan count 1, logical reads 3176, physical reads 2, read-ahead reads 3207, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table7'. scan count 2, logical reads 35, physical reads 2, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table8'. scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. sql server execution times: cpu time = 484 ms, elapsed time = 16415 ms. query 2:
sql server parse , compile time: cpu time = 312 ms, elapsed time = 618 ms. (5517 row(s) affected) table 'worktable'. scan count 11655, logical reads 807704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table8'. scan count 0, logical reads 11452, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table1'. scan count 5976, logical reads 24654, physical reads 15, read-ahead reads 3348, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table2'. scan count 1, logical reads 3061, physical reads 2, read-ahead reads 3084, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table3'. scan count 1, logical reads 4171, physical reads 2, read-ahead reads 4210, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table4'. scan count 1, logical reads 843, physical reads 3, read-ahead reads 874, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table5'. scan count 1, logical reads 6899, physical reads 2, read-ahead reads 6898, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table6'. scan count 1, logical reads 3176, physical reads 2, read-ahead reads 3207, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'table7'. scan count 2, logical reads 35, physical reads 2, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. sql server execution times: cpu time = 1342 ms, elapsed time = 15448 ms. where: table 7 , 8 look-up tables, while others transaction tables.
from execution plan - differs format: attribute - query 1/query2
**select node** cached plan size - 200/168 kb memory grant - 32088/38816 estimated subtree cost - 26.1473/24.4291 estimated no. of rows - 104.17/13848.8 **last node/operator** estimated operator cost - 0.0125/0.8858 estimated subtree cost - 26.1473/24.4291 estimated cpu cost - 0.0011897/0.874608 estimated i/o cost - 0.00112613/0.0112613 estimated no. of rows - 104.17/13848.8 actual no. of rows - 5517/5517
well, after waiting quite long , analyzing both queries based on different performance statistics, couldn't find way use 1 on another. finally, ran both queries data set of 55k records, , query 1 returned results in 13sec, while query 2 took around 18sec. used benchmark move forward query 1.
Comments
Post a Comment