This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:optimization:config:thresholds [2012/02/13 21:47] doc |
admin:optimization:config:thresholds [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 20: | Line 20: | ||
| Omnidex has several threshold settings that control when optimizations take place. Thresholds are needed because not all queries should be optimized using Omnidex indexes. Sometimes, indexing can even slow down a query. | Omnidex has several threshold settings that control when optimizations take place. Thresholds are needed because not all queries should be optimized using Omnidex indexes. Sometimes, indexing can even slow down a query. | ||
| - | === QUALIFICATION_THRESHOLD === | + | === Qualification Threshold === |
| The Qualification Threshold determines when indexes are used to process criteria and retrieve data. When the number of rows qualified by the Omnidex exceeds a percentage of the rows in the table, it is no longer cost-effective to use indexes to retrieve the rows. This is because the underlying database will fetch a block of adjacent rows off the disk drive each time any of the rows is requested. Depending on the size of the row, each fetch may return 100 or more rows. This means that once you want more than 1% of the rows in the database, it becomes more efficient to perform a sequential table scan rather than using indexes. | The Qualification Threshold determines when indexes are used to process criteria and retrieve data. When the number of rows qualified by the Omnidex exceeds a percentage of the rows in the table, it is no longer cost-effective to use indexes to retrieve the rows. This is because the underlying database will fetch a block of adjacent rows off the disk drive each time any of the rows is requested. Depending on the size of the row, each fetch may return 100 or more rows. This means that once you want more than 1% of the rows in the database, it becomes more efficient to perform a sequential table scan rather than using indexes. | ||
| Line 40: | Line 40: | ||
| </code> | </code> | ||
| - | === AGGREGATION_THRESHOLD === | + | === Aggregation Threshold === |
| The Aggregation Threshold determines when indexes are used to aggregate data. When the number of rows qualified by the Omnidex falls below a percentage of the rows in the table, it is no longer cost-effective to use indexes to aggregate the rows. There is a base cost to scanning Omnidex indexes for aggregating data, and if few enough rows are to be aggregated, it is simply faster to fetch the rows and aggregate them without the use of indexes. | The Aggregation Threshold determines when indexes are used to aggregate data. When the number of rows qualified by the Omnidex falls below a percentage of the rows in the table, it is no longer cost-effective to use indexes to aggregate the rows. There is a base cost to scanning Omnidex indexes for aggregating data, and if few enough rows are to be aggregated, it is simply faster to fetch the rows and aggregate them without the use of indexes. | ||
| Line 59: | Line 59: | ||
| - | === HDC_THRESHOLD === | + | === Hashed Data Cache Threshold === |
| - | === SORTMERGE_THRESHOLD === | + | The Hashed Data Cache Threshold determines how much data should be available to a process for temporary caching of data to improve table join performance. The Hashed Data Cache will cache small tables or blocks of data that will be accessed repeatedly, but will not exceed the Hashed Data Cache Threshold. |
| + | The default Hashed Data Cache Threshold is 32MB, meaning that each query may use up to 32MB of memory to temporarily cache data. | ||
| + | |||
| + | The Hashed Data Cache Threshold is changed using the HDC_THRESHOLD option of the SELECT statement. The value used in this option is the number of megabytes of memory that should be allowed. | ||
| + | |||
| + | <code> | ||
| + | select ... with HDC_THRESHOLD=64 | ||
| + | </code> | ||
| + | |||
| + | The Hashed Data Cache Threshold can also be disabled using the HDC_THRESHOLD option of the SELECT statement. | ||
| + | |||
| + | <code> | ||
| + | select ... with HDC_THRESHOLD=OFF | ||
| + | </code> | ||
| ===== ===== | ===== ===== | ||
| ---- | ---- | ||
| \\ | \\ | ||
| + | **[[admin:optimization:config:optimizations|Prev]]** | | ||
| **[[admin:optimization:config:timeouts|Next]]** | **[[admin:optimization:config:timeouts|Next]]** | ||