This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:optimization:plans:optimization [2012/02/03 20:08] doc |
admin:optimization:plans:optimization [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 103: | Line 103: | ||
| The Notes section gives more specific recommendations for optimizing this particular query: | The Notes section gives more specific recommendations for optimizing this particular query: | ||
| - | * **Optimized aggregations are not possible because Omnidex indexes don't exist on all aggregated columns and GROUP BY columns and all links to dimension tables**. This note describes how this query is performing aggregations, but they are not supported by Omnidex indexes. The technique for optimizing most aggregations is to add Omnidex indexes to all columns that are being aggregated, or that are GROUP BY columns, or that are foreign keys to dimension tables containing GROUP BY columns. In this query, the GENDER column must be indexes since it is a GROUP BY column. No other indexes would be necessary since columns are not being aggregated, and the GROUP BY columns are not in dimension tables within a Star Schema. | + | * **Optimized aggregations are not possible because Omnidex indexes don't exist on all aggregated columns and GROUP BY columns and all links to dimension tables**. This note describes how this query is performing aggregations, but they are not supported by Omnidex indexes. The technique for optimizing most aggregations is to add Omnidex indexes to all columns that are being aggregated, or that are GROUP BY columns, or that are foreign keys to dimension tables containing GROUP BY columns. In this query, the GENDER column must be indexed since it is a GROUP BY column. No other indexes would be necessary since columns are not being aggregated, and the GROUP BY columns are not in dimension tables within a Star Schema. |
| * **HDC optimization not used because table INDIVIDUALS has zero cardinality**. This note describes how Omnidex is trying to find a way to optimize the table join between HOUSEHOLDS and INDIVIDUALS. Omnidex is evaluating whether it can use a Hashed Data Cache (HDC) to optimize the join, but cannot because the table cardinalities are not known. This is an indication that an UPDATE STATISTICS has not been performed. | * **HDC optimization not used because table INDIVIDUALS has zero cardinality**. This note describes how Omnidex is trying to find a way to optimize the table join between HOUSEHOLDS and INDIVIDUALS. Omnidex is evaluating whether it can use a Hashed Data Cache (HDC) to optimize the join, but cannot because the table cardinalities are not known. This is an indication that an UPDATE STATISTICS has not been performed. | ||
| * **SortMerge optimization not used because table HOUSEHOLDS has zero cardinality**. This note also describes how Omnidex is trying to find a way to optimize the table join between HOUSEHOLDS and INDIVIDUALS. Omnidex is evaluating whether it can use a Sort/Merge to optimize the join, but cannot because the table cardinalities are not known. This is an indication that an UPDATE STATISTICS has not been performed. | * **SortMerge optimization not used because table HOUSEHOLDS has zero cardinality**. This note also describes how Omnidex is trying to find a way to optimize the table join between HOUSEHOLDS and INDIVIDUALS. Omnidex is evaluating whether it can use a Sort/Merge to optimize the join, but cannot because the table cardinalities are not known. This is an indication that an UPDATE STATISTICS has not been performed. | ||
| * **Sequential Table Join on INDIVIDUALS with H.HOUSEHOLD = I.HOUSEHOLD**. This note describes that Omnidex has found no other approach to processing this table join other than a Sequential Table Join, where the second table is sequentially scanned for each and every row of the first table. This is the worst performing method of processing a table join. In this case, Omnidex indexes should be added to the join columns, HOUSEHOLDS.HOUSEHOLD and INDIVIDUALS.HOUSEHOLD. | * **Sequential Table Join on INDIVIDUALS with H.HOUSEHOLD = I.HOUSEHOLD**. This note describes that Omnidex has found no other approach to processing this table join other than a Sequential Table Join, where the second table is sequentially scanned for each and every row of the first table. This is the worst performing method of processing a table join. In this case, Omnidex indexes should be added to the join columns, HOUSEHOLDS.HOUSEHOLD and INDIVIDUALS.HOUSEHOLD. | ||
| - | * **Filter on column STATE will not be optimized because there is not an Omnidex index installed on the column**. This note describes how there is not an Omnidex index on the STATE column, and so Omnidex must process this filter by retrieving each row and comparing it at run time. It is important to process all criteria with Omnidex indexes, both for the speed of processing the criteria and also for the speed of later steps, such as table joins, aggregations and ordering. In this case, an Omnidex index should be installed on the STATE column. | + | * **Filter on column STATE will not be optimized because there is not an Omnidex index installed on the column**. This note describes how there is not an Omnidex index on the STATE column, and so Omnidex must process this filter by retrieving each row and comparing it at run time. It is important to process all criteria with Omnidex indexes, both for the speed of processing the criteria and also for the speed of later steps, such as table joins, aggregations and ordering. In this case, an Omnidex index should be added on the STATE column. |
| - | * **Filter on column CITY will not be optimized because there is not an Omnidex index installed on the column**. Similar to the note above, an Omnidex index should be installed on the CITY column. Depending on whether the criteria is textual or not, the administrator should choose between an Omnidex index and a QuickText index. | + | * **Filter on column CITY will not be optimized because there is not an Omnidex index installed on the column**. Similar to the note above, an Omnidex index should be added on the CITY column. Depending on whether the criteria is textual or not, the administrator should choose between an Omnidex index and a QuickText index. |
| - | * **Filter on column BIRTHDATE will not be optimized because there is not an Omnidex index installed on the column**. Similar to the note above, an Omnidex index should be installed on the BIRTHDATE column. | + | * **Filter on column BIRTHDATE will not be optimized because there is not an Omnidex index installed on the column**. Similar to the note above, an Omnidex index should be added on the BIRTHDATE column. |
| Line 120: | Line 120: | ||
| * HOUSEHOLDS.CITY | * HOUSEHOLDS.CITY | ||
| * INDIVIDUALS.HOUSEHOLD | * INDIVIDUALS.HOUSEHOLD | ||
| - | * INDIVIDUALS.GENDER. | + | * INDIVIDUALS.GENDER |
| We have also determined that we should run the UPDATE STATISTICS command after indexing the database. | We have also determined that we should run the UPDATE STATISTICS command after indexing the database. | ||
| Line 126: | Line 126: | ||
| === Step 6. Produce a new query plan and re-analyze the query === | === Step 6. Produce a new query plan and re-analyze the query === | ||
| - | After applying the indexing changes and other recommendations, a new query plan should be analyzed. The changes made in each pass will affect the processing of the query, and may result in other warnings or suggestions for query optimization. These steps should be repeated until the best optimization is obtain. | + | After applying the indexing changes and other recommendations, a new query plan should be analyzed. The changes made in each pass will affect the processing of the query, and may result in other warnings or suggestions for query optimization. These steps should be repeated until the best optimization is obtained. |
| After applying the changes described above, the new query plan shows that the query is well optimized. There are no warnings or notes, and the details show that all processing is done through the Omnidex indexes, rather than the database. | After applying the changes described above, the new query plan shows that the query is well optimized. There are no warnings or notes, and the details show that all processing is done through the Omnidex indexes, rather than the database. | ||
| Line 160: | Line 160: | ||
| ==== Further Optimization === | ==== Further Optimization === | ||
| - | A query is considered "fully optimized" when there are no wanted warnings or notes in the query plan. Moreover, the goal is always to process as much of the query in the Omnidex indexes as possible, and to access the underlying database as little as possible. It is not uncommon for extremely complex queries to be processed entirely in the Omnidex indexes, with no access to the underlying database, even when there are many table joins, subqueries, complex criteria, aggregations and ordering. | + | A query is considered "fully optimized" when there are no unwanted warnings or notes in the query plan. The goal with Omnidex optimization is to process as much of the query in the Omnidex indexes as possible, and to access the underlying database as little as possible. It is even possible for extremely complex queries to be processed entirely in the Omnidex indexes, with no access to the underlying database, even when there are many table joins, subqueries, complex criteria, aggregations and ordering. |
| Sometimes the techniques described above are not enough to achieve the desired query performance. Some queries will perform poorly because of the fundamental structure of the query. This can happen because the query is written in an unusual way, often using techniques that are recommended for an underlying relational database. It is sometimes best to study the query, understand what the query is fundamentally trying to do, and then simplify the query. | Sometimes the techniques described above are not enough to achieve the desired query performance. Some queries will perform poorly because of the fundamental structure of the query. This can happen because the query is written in an unusual way, often using techniques that are recommended for an underlying relational database. It is sometimes best to study the query, understand what the query is fundamentally trying to do, and then simplify the query. | ||
| - | Other times, it is beneficial to restructure the query to take advantage of Omnidex indexing techniques, similar to what may have been done the underlying relational database. It is usually preferable to maintain queries in their simplest form, but like all databases, certain approaches work better than others. | + | While simplicity is usually the best approach for Omnidex optimization, there are times when it is beneficial to restructure the query to take advantage of Omnidex indexing techniques. Ironically, this is similar to what may have been done for an underlying relational database, but this time we are favoring the optimization techniques of Omnidex. |
| - | If you are not able to achieve the level of performance you desired with a query, contact Technical Support. We can sometimes offer suggestions as part of your support contract, and if needed, we can discuss having an engineer analysis your environment and your query plans to achieve improved performance. | + | If you are not able to achieve the level of performance you desired with a query, contact [[appendix:contactus|Technical Support]]. We can sometimes offer suggestions as part of your support contract, and if needed, we can discuss having an engineer analyze your environment and your query plans to achieve improved performance. |
| Line 178: | Line 178: | ||
| ====== 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}} | ||