SAS Scalable Performance Data Server 4.5, Administrator'S Manual

Page: 95 / 271

Chapter 8
SPD Server SQL Query Rewrite
Facility
Overview of the SQL Query Rewrite Facility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Configuring Storage Space for the SQL Query Rewrite Facility . . . . . . . . . . . . . . . 89
SQL Query Rewrite Facility Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
_QRWENABLE Option: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
_QRW Option: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Overview of the SQL Query Rewrite Facility
The SQL Query Rewrite facility in SPD Server examines SQL queries in order to optimize
processing performance. Some SQL queries contain SQL statements and sub-queries that
can be more rapidly evaluated in a separate space, as opposed to sequentially evaluating
large blocks of SQL statements. When SPD Server detects and processes SQL statements
or sub-queries in a separate space, intermediate result tables are produced. The original
SQL query is dynamically rewritten, using intermediate results tables to replace the SQL
code that was separately evaluated. The result is a dynamic process that evaluates and
processes SQL queries more efficiently.
Inserting the derived intermediate data into the original SQL query does not change the
quantitative results; it only expedites the processing that is required to calculate them. The
SQL Query Rewrite Facility does not change the content of the query's answer row set.
However, the order of the rows in the query answer set might vary if you compare the
optimized query answer set with the query answer set SPD Server generates with the SQL
Query Rewrite facility disabled.
Configuring Storage Space for the SQL Query
Rewrite Facility
The SQL Query Rewrite Facility uses intermediate results tables to expedite original SQL
queries. SPD Server administrators must provide adequate space for the generation and
storage of the intermediate results tables. The intermediate results tables are formatted as
SPD Server tables. Optional indexes are permitted.
Intermediate results tables are stored in a common SPD Server LIBNAME domain that the
SPD Server administrator specifies. One dedicated SQL Rewrite Facility LIBNAME
domain is sufficient to provide adequate intermediate results table storage for many
89