This shows you the differences between two versions of the page.
|
admin:optimization:plans:reference [2012/02/02 21:20] doc |
admin:optimization:plans:reference [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Administration: Optimizing Queries ====== | ||
| - | |||
| - | ===== Query Plans ===== | ||
| - | |||
| - | |||
| - | [[admin:optimization:plans:home|Overview]] | | ||
| - | **[[admin:optimization:plans:reference|Anatomy of a Query Plan]]** | | ||
| - | [[admin:optimization:plans:example|Reading a Query Plan]] | | ||
| - | [[admin:optimization:plans:configuration|Configuring Query Plans]] | | ||
| - | [[admin:optimization:plans:optimization|Optimizing Queries]] | ||
| - | |||
| - | ---- | ||
| - | \\ | ||
| - | |||
| - | ==== Anatomy of a Query Plan ==== | ||
| - | |||
| - | An Omnidex query plan is designed to provide all of the essential information needed to understand how a query will be processed, as well as basic suggestions about how to further optimize the query. A great deal can be learned by reviewing the query plan, and it is always the first step in understanding Omnidex query performance. | ||
| - | |||
| - | A query plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, any optimization settings or warnings that affect the query, and even occasional suggestions for optimizing the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and are indented as needed to show loops of instructions. The example below shows the skeleton of a query plan: | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- SUMMARY ----------------------------------- | ||
| - | <SQL Statement> | ||
| - | |||
| - | Version: <Omnidex version> | ||
| - | Request Opt: <Requested optimization settings> | ||
| - | Optimization: <Actual optimization settings> | ||
| - | Warnings: <Warning flags> | ||
| - | Notes: <Optimization notes> | ||
| - | ----------------------------------- DETAILS ----------------------------------- | ||
| - | <Query processing step 1> | ||
| - | <Query processing step 2> | ||
| - | <Query processing step 3> | ||
| - | ... | ||
| - | ------------------------------------------------------------------------------- | ||
| - | </code> | ||
| - | |||
| - | === The SUMMARY Section === | ||
| - | |||
| - | The Summary section describes the SQL Statement and provides general notes and flags about the query. These included optimization settings and recommendations. The Summary section is the first place to look when evaluating the optimization of a query. | ||
| - | |||
| - | == SQL Statement == | ||
| - | |||
| - | The SQL Statement being explained is shown, formatted for easy viewing. | ||
| - | |||
| - | == Version == | ||
| - | |||
| - | The version of Omnidex, including the compilation timestamp. This is important as optimization can be different between versions of Omnidex. | ||
| - | |||
| - | == Required Optimization == | ||
| - | |||
| - | Required optimizations will put requirements on the optimization of the query, and will error if these requirements cannot be met. These required optimizations can be controlled using the REQUIRED_OPTIMIZATION option of the SELECT statement. | ||
| - | |||
| - | The specific optimizations that can be required are: | ||
| - | |||
| - | ^ Setting ^ Description ^ | ||
| - | | NO_XREF_FILES | Do not create Cross-Reference files when processing this query. Cross-Reference files are sometimes used to optimize complex, many-to-many table relationships, but they generally cause a query to take a lot of time, especially on large databases. | | ||
| - | | NO_UNOPTIMIZED_CRITERIA | Do not allow any criteria to be processed without the use of Omnidex indexing. | | ||
| - | | NO_CARTESIAN_PRODUCTS | Do not allow Cartesian Joins. Cartesian Joins occur when tables are joined together without any join criteria. While there are rare cases when Cartesian Joins are desireable, they are usually an indication of missing join criteria. | | ||
| - | | NO_UNOPTIMIZED_AGGREGATIONS | Do not allow any aggregations to be processed without the use of Omnidex indexing. | | ||
| - | | NO_UNOPTIMIZED_SORTS | Do not allow any sorts to be processed without the use of Omnidex indexing. | | ||
| - | | NO_SEQUENTIAL_SCANS | Do not allow any sequential table scans. | | ||
| - | | NO_SEQUENTIAL_TABLE_JOINS | Do not allow any sequential table joins. A sequential table join indicates that all rows in the second table will be scanned sequentially for each row in the first table. This is the most inefficient way to perform a join, and is only necessary when Omnidex indexing, underlying database indexing, table caching and sort/merging cannot be used. | | ||
| - | | NO_GRID_AGGREGATIONS | Do not allow any aggregations to be performed on the grid controller that require reaggregating data on the grid controller. | | ||
| - | |||
| - | |||
| - | |||
| - | == Optimization Flags == | ||
| - | |||
| - | Optimization flags report the classes of optimization that were performed for this query. These optimizations can be controlled using the OPTIMIZATION option of the SELECT statement, or using the SET OPTIMIZATION command. | ||
| - | |||
| - | The classes of optimization are: | ||
| - | |||
| - | ^ Setting ^ Description ^ | ||
| - | | QUAL | Omnidex indexes were used to perform qualifications, usually to process criteria in the WHERE clause. | | ||
| - | | AGGREGATION | Omnidex indexes were used to perform aggregations, including grouped counts, distinct counts, sums, averages, mins and maxes. | | ||
| - | | ORDERBY | Omnidex indexes were used to perform ordering as required by the ORDER BY clause. | | ||
| - | | IDXRETRIEVAL | Omnidex indexes were used to retrieve select items for the result set, avoiding access to the database. | | ||
| - | | CACHEQUAL | Omnidex recognized that previous index qualifications on this connection could be reused in this query. | | ||
| - | | DISTINCT_KEY | Omnidex performed aggregations against a QuickText index, and the result set represents an entry per keyword rather than an entry per row. This will only happen if directly requested.| | ||
| - | | ROLLUP | Omnidex was able to redirect the query to a Rollup Table for improved performance. | | ||
| - | | SORTMERGE | Omnidex performed a sort/merge join operation between two tables in order to improve performance. | | ||
| - | | HDC | Omnidex use a dynamic Hashed Data Cache for improved performance. | | ||
| - | | LINK | Omnidex indexes were used to perform table joins. | | ||
| - | | PRIKEY | Omnidex favored using the underlying databases primary key indexes rather than using Omnidex indexes. | | ||
| - | | STARSCHEMA | Omnidex performed intermediate aggregations in the fact table of a star schema prior to accessing the dimension tables. | | ||
| - | | UNRESTRICTEDQUAL | Omnidex removed normal restrictions on special characters, embedded operators and other rules when performing qualifications. This will only happen if directly requested. | | ||
| - | | NONE | All optimization was disabled by request. | | ||
| - | | NO_ODX | All optimization that would involve Omnidex indexes was disabled by request. | | ||
| - | |||
| - | == Warning Flags == | ||
| - | == Notes == | ||
| - | |||
| - | Notes describing optimization decisions or suggestions that were made during the analysis of the query. These notes often contain valuable instructions about how to improve the optimization of the query, and are usually the most valuable aspect of the query plan when tuning a query. | ||
| - | |||
| - | === The DETAILS Section === | ||
| - | == Query processing steps == | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | ---- | ||
| - | \\ | ||
| - | **[[admin:optimization:home|Prev]]** | | ||
| - | **[[admin:optimization:example|Next]]** | ||
| - | |||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | See also: | ||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||