602SQL Documentation Index  

Information about Query Optimization and Evaluation

SQL server tries to optimize each query before it's evaluated, i.e. transform it, so the evaluation is most effective.

Developer may get information about the way the server will evaluate the query and get data about the complexity of evaluation of individual query parts. The Optimization action in the popup menu for queries serves for this purpose. This action opens a dialog window containing the tree of the query evaluation.

The tree describing the query evaluation may have two information extents. The basic information about the evaluation structure is displayed in the window initially. Detailed information about the count of passes through individual evaluation branches and the number of processed records in the individual branches is displayed after clicking the Evaluate query button. Full evaluation of the query is necessary for displaying this information which may take some time.

The tree branches correspond with the JOIN, OUTER JOIN, UNION, INTERSECT, EXCEPT operations and creation of the record groups (GROUP BY). Each table has stated, whether all records belong to it (exhaustive pass) or whether they are chosen using indexes. Record conditions are displayed in the window according to where they're evaluated.

Query optimization information allows the developer to find and modify the places, where the evaluation lingers. Searching a big set of records using conditions is not effective - it's better to design indexes that directly allow you to find record.