This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:optimization:config:optimizations [2012/02/07 19:27] doc created |
admin:optimization:config:optimizations [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 18: | Line 18: | ||
| ==== Optimization Settings ==== | ==== Optimization Settings ==== | ||
| - | There are several ways that Omnidex can be configured. Most of these configuration options affect the way that the Omnidex SQL Engine processes queries or provide software diagnostics. These configuration options fall into four categories: | + | Omnidex has two main configuration settings for controlling optimization of queries. The first option tells Omnidex which approaches it may use as needed to optimize a query. The second option tells Omnidex which approaches are required when optimizing a query, or else the query should not be processed. |
| - | * **Optimization Settings** - These settings instruct Omnidex about how to optimize queries. Some settings tell Omnidex to enable or disable optimizations as needed. Other settings tell Omnidex to cause an error if certain optimizations cannot be made. | + | === OPTIMIZATION === |
| - | * **Thresholds** - Certain optimizations in Omnidex take affect only after a certain threshold has been reached, or are in affect until a certain threshold is reached. These thresholds can be changed by the administrator based on the needs of the application. | + | The OPTIMIZATION setting controls the approaches available to Omnidex when it optimizes a query. This setting can be set using an option on the SELECT statement, or with a SET OPTIMIZATION command: |
| - | * **Timeouts** - Omnidex can halt processing of a query if too much time has passed. Timeouts are not in effect unless requested, and can be configured for any amount of time. | + | <code> |
| + | select ... with OPTIMIZATION='setting1,setting2,setting3,...' | ||
| + | </code> | ||
| - | * **Diagnostics** - Omnidex has a deep set of diagnostics that can be used to understand what an application is doing or to help diagnose problems with Omnidex. Some of these diagnostics are valuable to the administrator or developer, and others are valuable when working with Technical Support. | + | <code> |
| + | set optimization setting1,setting2,setting3,... | ||
| + | </code> | ||
| + | |||
| + | The OPTIMIZATION settings are shown in the table below. The default setting is shown in **bold**. | ||
| + | |||
| + | |<100% 20% 20% 60% >| | ||
| + | ^ Setting to \\ allow optimization ^ Setting to \\ disallow optimization ^ \\ Description ^ | ||
| + | | **QUAL** | NO_QUAL | Omnidex can use indexes to perform qualifications, usually to process criteria in the WHERE clause. | | ||
| + | | **AGGREGATION** | NO_AGGREGATION | Omnidex can use indexes to perform aggregations, including grouped counts, distinct counts, sums, averages, mins and maxes. | | ||
| + | | **ORDERBY** | NO_ORDERBY | Omnidex can use indexes to perform ordering as required by the ORDER BY clause. | | ||
| + | | **IDXRETRIEVAL** | NO_IDXRETRIEVAL | Omnidex can use indexes to retrieve select items for the result set, avoiding access to the database. | | ||
| + | | **CACHEQUAL** | NO_CACHEQUAL | Omnidex can reuse qualifications from the previous query on this connection. | | ||
| + | | DISTINCT_KEY | **NO_DISTINCT_KEY** | Omnidex can perform aggregations against a QuickText index, and the result set represents an entry per keyword rather than an entry per row. | | ||
| + | | **ROLLUP** | NO_ROLLUP | Omnidex can redirect redirect the query to a Rollup Table for improved performance. | | ||
| + | | **SORTMERGE** | NO_SORTMERGE | Omnidex can perform a sort/merge join operation between two tables in order to improve performance. | | ||
| + | | **HDC** | NO_HDC | Omnidex can use a temporary Hashed Data Cache for improved performance. | | ||
| + | | **LINK** | NO_LINK | Omnidex can use indexes to perform table joins.\\ \\ | | ||
| + | | **PRIKEY** | NO_PRIKEY | Omnidex can favor using the underlying databases primary key indexes rather than using Omnidex indexes. | | ||
| + | | **STARSCHEMA** | NO_STARSCHEMA | Omnidex can perform intermediate aggregations in the fact table of a star schema prior to accessing the dimension tables. | | ||
| + | | UNRESTRICTEDQUAL | **NO_UNRESTRICTEDQUAL** | Omnidex can ignore the normal restrictions on special characters, embedded operators and index options when performing qualifications. | | ||
| + | | NONE | | Omnidex should disable all available optimizations when processing this query. | | ||
| + | | NO_ODX | | Omnidex should disable all optimizations that would involve Omnidex indexes when processing this query. | | ||
| + | |||
| + | === REQUIRED_OPTIMIZATION === | ||
| + | |||
| + | The REQUIRED_OPTIMIZATION setting instructs Omnidex to insure that the named optimizations will be performed for a query. If a REQUIRED_OPTIMIZATION setting cannot be performed, then the query will return an error condition. This setting can be set using an option on the SELECT statement: | ||
| + | |||
| + | <code> | ||
| + | select ... with REQUIRED_OPTIMIZATION='setting1,setting2,setting3,...' | ||
| + | </code> | ||
| + | |||
| + | The REQUIRED_OPTIMIZATIONS settings are shown in the table below: | ||
| + | |||
| + | ^ 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. | | ||
| Line 32: | Line 76: | ||
| ---- | ---- | ||
| \\ | \\ | ||
| - | **[[admin:optimization:config:optimizations|Next]]** | + | **[[admin:optimization:config:home|Prev]]** | |
| + | **[[admin:optimization:config:thresholds|Next]]** | ||