SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-24
Query Caching
Query Caching
In NonStop SQL/MP, you control statement and query caching with MXCS and
SQL/MP configuration attributes, system defaults, and control statements. These
settings persist until a user changes a Guardian user ID, a server is reinitialized, or a
rollback condition occurs. Caching takes place at a high level.
In NonStop SQL/MX, caching is controlled by control query default statements.
Caching takes place within each SQL/MX MXCMP compiler session and is applicable
to query compilation requests in that session. MXCS, JDBC, and MXCI clients have
their own MXCMP sessions. Each MXCMP session has caching turned on by default.
MXCS and JDBC/MX clients use control statements that you can store in a named
collection linked to a user and stored in configuration tables for that user, so they are
always set for that user.
See the SQL/MX Reference Manual for details about these settings and the SQL/MX
Query Guide for more information about queries.
This table compares caching features in NonStop SQL/MP and NonStop SQL/MX:
Feature NonStop SQL/MP NonStop SQL/MX
Cache location In ODBC/MP server In MXCMP compiler
Cache setting Disabled by default Enabled by default
Cache size limit 32,767 statements 4,194,303 KB of memory
Efficiency increase
from cache hit
High Medium
Effect of schema
change on cached
query
Flushes cache. Flush and re-cache
transparently.
Caching
considerations
Caching is discouraged for
SELECT queries with
ORDER BY clauses.
An optional feature of
statement cache supports
caching parallel plans and
allocated ESPs.
To avoid resource
exhaustion, use caching only
where ESP sharing is
enabled and the number of
statements cached are small.
Complex queries and
DSS-type queries are
currently not cached.
Location of caching
control
ODBC/MP user's profile. SQL/MX’s MXCS session.