This is an old revision of the document!
Omnidex optimizes most statements by qualifying rows using the Omnidex indexes. Each of these qualifications searches one or more index and write index pointers to a temporary file. This is much faster than searching the data directly, but this approach can be further optimized.
Many applications issue several SQL statements to process a single request from a user. The ActiveCounts feature of Omnidex provides a classic example. In that example, the application performed three consecutive statements, each time adding an additional piece of criteria:
At each step, Omnidex recognizes these scenarios and reuses the index pointers that were isolated in the prior step. This means that the redundant criteria is never reprocessed. This can substantially improve performance, especially on large databases, and on queries with a lot of criteria.
This optimization can also be useful when the application first obtains a count prior to returning the data. Applications often perform the following sequence of statements:
Omnidex also recognizes these scenarios and reuses the index pointers that were isolated in the first statement. This means that the criteria does not need to be processed at all in the second statement.
This optimization is also invaluable when applications must display screenfuls of data to the user. Applications will typically use the TOP and SKIP functions to do paging:
Omnidex will recognize these scenarios and reuse the index pointers that were isolated in the first statement. This means that the criteria is processed only during the first statement.
All of these optimizations are automatic, and do not require action by the administrator or developer.
If Cached Qualifications are used in a query, it can be seen using Omnidex query plans. The following query plan shows “(Cached)” after each qualifications, which indicates that the previous qualifications are being reused.
----------------------------------- SUMMARY ----------------------------------- Select INDIVIDUALS.NAME, GENDERS.DESCRIPTION from INDIVIDUALS join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Warnings: SEQUENTIAL_SCAN ----------------------------------- DETAILS ----------------------------------- Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; Retrieve INDIVIDUALS sequentially; Retrieve {1} using PK_GENDER = INDIVIDUALS.GENDER; Return INDIVIDUALS.NAME, GENDERS.DESCRIPTION; -------------------------------------------------------------------------------
The Hashed Data Cache is limited to 32MB of memory per table by default. This can be adjusted using the HDC_THRESHOLD option of the SELECT statement, which sets the maximum cache size, measured in MB.
See also: