This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:optimization:plans:reference [2012/02/02 21:45] doc |
admin:optimization:plans:reference [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 21: | Line 21: | ||
| 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. | 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: | + | 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 warnings, 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> | <code> | ||
| Line 28: | Line 28: | ||
| Version: <Omnidex version> | Version: <Omnidex version> | ||
| - | Optimization: <Optimization flags> | ||
| Warnings: <Warning flags> | Warnings: <Warning flags> | ||
| Notes: <Optimization notes> | Notes: <Optimization notes> | ||
| Line 41: | Line 40: | ||
| === The SUMMARY Section === | === 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. | + | The Summary section describes the SQL Statement and provides warnings and notes about eh optimization of the query. The Summary section is the first place to look when evaluating the optimization of a query. |
| == SQL Statement == | == SQL Statement == | ||
| Line 50: | Line 49: | ||
| The version of Omnidex, including the compilation timestamp. This is important as optimization can be different between versions of Omnidex. | The version of Omnidex, including the compilation timestamp. This is important as optimization can be different between versions of Omnidex. | ||
| - | |||
| - | == 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: | ||
| - | |||
| - | |< 100% 25% 75% >| | ||
| - | ^ 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. | | ||
| == Warning Flags == | == Warning Flags == | ||
| Line 92: | Line 70: | ||
| === The DETAILS Section === | === The DETAILS Section === | ||
| + | |||
| + | The Details section describes the processing steps needed to process the query. Steps are processed in the order shown, and steps that occur in a loop are indented if needed to show the processing flow. Ultimately, these processing steps are the key to fully understanding the optimization of a query. | ||
| + | |||
| == Query processing steps == | == Query processing steps == | ||
| + | |||
| + | The following processing steps are show in this section: | ||
| + | |||
| + | |<100% 25% 75% >| | ||
| + | ^ Step ^ Description ^ | ||
| + | | Aggregate | Aggregate rows using one or more Omnidex indexes, honoring grouped counts, distinct counts, sums, averages, mins and maxes. | | ||
| + | | Build cache | Create a memory-resident Hashed Data Cache for a result set to be referenced later in subsequent steps. | | ||
| + | | Build xref | Create a cross-reference file to aid in the processing of a many-to-many table relationship. | | ||
| + | | Create segment | Create a data or index segment from the current results to be reused in subsequent steps. | | ||
| + | | Create table | Create a table in the underlying database to receive the result set of the query. | | ||
| + | | Export | Export data values or index pointers into a file to be reused in subsequent steps. | | ||
| + | | Fetchkeys | Fetch primary keys or rowids from the indexes for use in retrieve data from the underlying database. | | ||
| + | | Filter | Process criteria using in-memory comparisons rather than using an Omnidex index. | | ||
| + | | Insert | Insert rows into a table produced by an earlier Create Table step. | | ||
| + | | Join | Join from one table to another table using the Omnidex indexes. | | ||
| + | | Merge queues | Combine and merge two intermediate queues generated by earlier Pass to Queue steps. | | ||
| + | | Pass to queue | Pass intermediate data into a queue for sorting or use in subsequent steps. | | ||
| + | | Processing subquery | Begin processing of a subquery. | | ||
| + | | Qualify | Process criteria against an Omnidex index. | | ||
| + | | Read from xref | Read entries from a previously created cross-reference file while processing a many-to-many table relationship. | | ||
| + | | Retrieve | Retrieve data from the underlying database. | | ||
| + | | Retrieve from index | Retrieve select items from a query or subquery using an Omnidex index, rather than the underlying database. | | ||
| + | | Return | Return the result set of the query. | | ||
| + | | Returning from subquery | End processing of a subquery, returning results for use in subsequent steps. | | ||
| + | | Sample | Reduce the result set using an Omnidex index to a certain number of rows per control break, optionally favoring the min or max of a column. | | ||
| + | | Sort | Sort a queue for use in subsequent steps. | | ||
| Line 98: | Line 105: | ||
| ---- | ---- | ||
| \\ | \\ | ||
| - | **[[admin:optimization:home|Prev]]** | | + | **[[admin:optimization:plans:home|Prev]]** | |
| - | **[[admin:optimization:example|Next]]** | + | **[[admin:optimization:plans:example|Next]]** |
| ====== Additional Resources ====== | ====== Additional Resources ====== | ||
| See also: | See also: | ||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | + | {{page>:admin:optimization:see_also&nofooter&noeditbtn}} |
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||