Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
admin:optimization:plans:optimization [2012/02/03 20:05]
doc
admin:optimization:plans:optimization [2016/06/28 22:38] (current)
Line 96: Line 96:
   * **UNOPTIMIZED_AGGREGATION**. ​ The aggregations in this query cannot be satisfied using Omnidex indexes. ​ This means that the data must be retrieved from the database and sorted. ​ It is preferable to have the aggregation processed through Omnidex indexes.   * **UNOPTIMIZED_AGGREGATION**. ​ The aggregations in this query cannot be satisfied using Omnidex indexes. ​ This means that the data must be retrieved from the database and sorted. ​ It is preferable to have the aggregation processed through Omnidex indexes.
   * **UNOPTIMIZED_SORT**. ​ Some aspect of this query required sorting at run time.  This is not always an optimization issue, as sometimes it is required to sort intermediate results that were derived from Omnidex indexes. ​ If sorting can be eliminated through the use of Omnidex indexes, it is an easy way to improve the query performance.   * **UNOPTIMIZED_SORT**. ​ Some aspect of this query required sorting at run time.  This is not always an optimization issue, as sometimes it is required to sort intermediate results that were derived from Omnidex indexes. ​ If sorting can be eliminated through the use of Omnidex indexes, it is an easy way to improve the query performance.
-  * **SEQUENTIAL_SCAN**. ​ At least one table is being sequentially scanned. ​ This is not always an optimization issue, especially if more than 10% of the table is to be ultimately ​retrieved. ​ Often it is an indication that Omnidex indexes were not present, either for processing criteria or table joins.+  * **SEQUENTIAL_SCAN**. ​ At least one table is being sequentially scanned. ​ This is not always an optimization issue, especially if more than 10% of the table is to be retrieved. ​ Often it is an indication that Omnidex indexes were not present, either for processing criteria or table joins.
   * **SEQUENTIAL_TABLE_JOIN**. ​ At least one table join required a sequential table join, where the second table is sequentially scanned for each and every row in the first table. ​ This is the most inefficient approach to table joins and should always be avoided when possible. ​ It is usually an indication that an UPDATE STATISTICS command has not be run to register the table cardinalities,​ and that indexes are not present on the join columns. ​   * **SEQUENTIAL_TABLE_JOIN**. ​ At least one table join required a sequential table join, where the second table is sequentially scanned for each and every row in the first table. ​ This is the most inefficient approach to table joins and should always be avoided when possible. ​ It is usually an indication that an UPDATE STATISTICS command has not be run to register the table cardinalities,​ and that indexes are not present on the join columns. ​
  
 == Notes == == Notes ==
  
-  ​* **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.+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 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 118: 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 124: 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 158: 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 optimizationthere are times when it is beneficial to restructure the query to take advantage of Omnidex indexing techniques.  Ironicallythis 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 176: 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}}
 
Back to top
admin/optimization/plans/optimization.1328299507.txt.gz · Last modified: 2016/06/28 22:38 (external edit)