About the SQL Execution Plan
-
1、How to know which SQL statement runs faster than the other SQL statement by contrasting the being displayed execution plan? 2、Could you please tell me the meaning of I/O Cost、CPU Cost、Subtree Cost and Cost? and what is the measuring unit of these costs? and if there is a mathematical expression among the cost,say Cost=I/O Cost+Cpu Cost+subtree cost? 3、Is it possible to compute the accurate time of one SQL statements execution costed by execution plan?
-
1、How to know which SQL statement runs faster than the other SQL statement by contrasting the being displayed execution plan? 2、Could you please tell me the meaning of I/O Cost、CPU Cost、Subtree Cost and Cost? and what is the measuring unit of these costs? and if there is a mathematical expression among the cost,say Cost=I/O Cost+Cpu Cost+subtree cost? 3、Is it possible to compute the accurate time of one SQL statements execution costed by execution plan?
-
1、How to know which SQL statement runs faster than the other SQL statement by contrasting the being displayed execution plan? 2、Could you please tell me the meaning of I/O Cost、CPU Cost、Subtree Cost and Cost? and what is the measuring unit of these costs? and if there is a mathematical expression among the cost,say Cost=I/O Cost+Cpu Cost+subtree cost? 3、Is it possible to compute the accurate time of one SQL statements execution costed by execution plan?
I think you may have the wrong handle on what to use the execution plan for, it is not for timing parts of a query, more for identifying high cost points of your query. Do study the MSDN stuff if you need more detailed information but I look for the highest % nodes and make sure they are for index scans. This is a VERY crude use of the tool but a good first step.
Never underestimate the power of human stupidity RAH
-
I think you may have the wrong handle on what to use the execution plan for, it is not for timing parts of a query, more for identifying high cost points of your query. Do study the MSDN stuff if you need more detailed information but I look for the highest % nodes and make sure they are for index scans. This is a VERY crude use of the tool but a good first step.
Never underestimate the power of human stupidity RAH
I agree. I typically use the execution plan to make sure I can reduce the number of I/O's; no full table scans ... It has been my experience that if you reduce (or limit) the I/O's your application will respond quickly. Regarding, timing of SQL: Why don't you build your own timer logic and try various SQL statements out? Keep it simple, something like this: Begin timer1 Execute SQL1 End timer1 Begin timer2 Execute SQL2 End timer2 Now check which timer is the smallest. You will have to collect these statistics multiple times to ensure that you are not getting skewed results because the data happens to be in cache.