Overview | Data Caching | Qualification Caching | Filesystem Caching

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:

• select … where <criteria1>
• select … where <criteria1> and <criteria2>
• select … where <criteria1> and <criteria2> and <criteria3>

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:

• select count(*) from … where <criteria1> and <criteria2> and <criteria3>
• select <columns> from … where <criteria1> and <criteria2> and <criteria3>

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:

• select TOP 20 <columns> from … where <criteria1> and <criteria2> and <criteria3>
• select TOP 20 SKIP 20 <columns> from … where <criteria1> and <criteria2> and <criteria3>
• select TOP 20 SKIP 40 <columns> from … where <criteria1> and <criteria2> and <criteria3>

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. Note that this type of caching only applies to qualifications, joins and index segments, and does not apply to aggregation steps.

If Cached Qualifications are used in a query, it can be seen using Omnidex query plans. The following query plan shows “(Cached)” after the qualifications, joins and index segment steps, indicating that the previous qualifications are being reused.

----------------------------------- SUMMARY -----------------------------------
Select        I.GENDER,
              count(*)
  from        HOUSEHOLDS H
  join        INDIVIDUALS I on H.HOUSEHOLD = I.HOUSEHOLD
  where       ((H.STATE = 'CO' and
                H.CITY = 'Denver') or
               (H.STATE = 'AZ' and
                H.CITY = 'Phoenix')) and
              I.BIRTHDATE > 'January 1, 1980'
  group by    I.GENDER;

Version:      5.2.01  (Compiled Feb  2 2012 21:29:57)
----------------------------------- DETAILS -----------------------------------
Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver' (Cached);
Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO' (Cached);
Create index segment O1 on 1 (Cached);
Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix' (Cached);
Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ' (Cached);
Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)' (Cached);
Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD (Cached);
Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'(Cached);
Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*) on 1;
Return I.GENDER, COUNT('*');
-------------------------------------------------------------------------------

</code>

If cached qualifications are used, Omnidex will favor the previously qualified index pointers, rather than fresh pointers from the indexes. This means that the query will not be aware of any inserts, deletes or updates that have happened since the previous query. This is generally not an issue since most Omnidex applications are read-only, and even applications that are updated may be able to tolerate this type of short-term caching. If needed, though, this type of caching can be disabled by either using the “OPTIMIZATION=NO_CACHEQUAL” option on the select statement, or issuing the “SET OPTIMIZATION NO_CACHEQUAL” command on the connection.

Prev | Next

See also: