Administration: Optimizing Queries

Dynamic Caches

Qualification Caching

Omnidex optimizes most statements by qualifying rows using the Omnidex indexes. Each qualification searches one or more indexes and then writes index pointers to a temporary file. This approach is much faster than searching the data directly, but it can also 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>

During the second and third statements, Omnidex recognizes that is can reuse the index pointers that were isolated in the prior statement. In each of these statements, only one additional search of the indexes is performed, and no criteria is processed twice. 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 recognizes that the criteria is the same 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 valuable when applications display screenfuls of data to the user. Applications will typically use the TOP and SKIP functions when paging through data:

  • 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 recognizes that the criteria is the same and reuses 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.

Cached Qualifications in Query Plans

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('*');
-------------------------------------------------------------------------------

Disabling Cached Qualifications

When cached qualifications are used, the current 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, this type of caching can be disabled using the “OPTIMIZATION=NO_CACHEQUAL” option on the select statement, or using the “SET OPTIMIZATION NO_CACHEQUAL” command on the connection.

Additional Resources

See also:

 
Back to top
admin/optimization/caches/cachequal.txt · Last modified: 2016/06/28 22:38 (external edit)