Differences

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

--- admin:optimization:caches:cachequal [2012/02/07 00:13]
doc
+++ admin:optimization:caches:cachequal [2016/06/28 22:38] (current)
@@ Line 9: / Line 9: @@
 [[admin:optimization:caches:home|Overview]] |
-[[admin:optimization:caches:hdc|Data Caching]] |
+[[admin:optimization:caches:hdc|Hashed Data Caching]] |
 **[[admin:optimization:caches:cachequal|Qualification Caching]]** |
-[[admin:optimization:caches:files|Filesystem Caching]]
+[[admin:optimization:caches:filesystem|Filesystem Caching]]
 ----
@@ Line 17: / Line 17: @@
 ==== 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.
-=== Qualification Caches in Query Plans ===
+Many applications issue several SQL statements to process a single request from a user.  The [[admin:indexing:activecounts:home|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:
-If a Hashed Data Cache is used in a query, it can be seen using [[admin:optimization:plans:home|Omnidex query plans]].  The following query plan shows a "Build cache" step that builds the Hashed Data Cache, and then a subsequent "Retrieve" step that retrieves from the Hashed Data Cache.
+  * **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 [[admin:optimization:plans:home|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.
 <code>
 ----------------------------------- SUMMARY -----------------------------------
-Select        INDIVIDUALS.NAME,
+Select        I.GENDER,
-              GENDERS.DESCRIPTION
+              count(*)
-  from        INDIVIDUALS
+  from        HOUSEHOLDS H
-  join        GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER;
+  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)
-Warnings:     SEQUENTIAL_SCAN
 ----------------------------------- DETAILS -----------------------------------
-Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2;
+Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver' (Cached);
-Retrieve INDIVIDUALS sequentially;
+Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO' (Cached);
- Retrieve {1} using PK_GENDER = INDIVIDUALS.GENDER;
+Create index segment O1 on 1 (Cached);
- Return INDIVIDUALS.NAME, GENDERS.DESCRIPTION;
+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>
-=== Adjusting the Cache Size ===
+=== Disabling Cached Qualifications ===
-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.
+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.
 =====  =====
 ----
 \\
-**[[admin:optimization:caches:home|Prev]]** |
+**[[admin:optimization:caches:hdc|Prev]]** |
-**[[admin:optimization:caches:cachequal|Next]]**
+**[[admin:optimization:caches:filesystem|Next]]**
 ====== Additional Resources ======
 See also:
-{{page>:admin:indexing:see_also&nofooter&noeditbtn}}
+{{page>:admin:optimization:see_also&nofooter&noeditbtn}}
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

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

Sitemap Recent Changes