Reporting Server > Introduction to Adapters > Optimization Settings

Optimization Settings

Topics:

Optimizing Requests
Optimizing Requests to Pass Virtual Fields Defined as Constants

Adapter optimization allows the RDBMS to perform the work for which it is best suited, reducing the volume of RDBMS-to-server communication and improving response time. It also enables the RDBMS to exploit its own internal optimization techniques.

Optimizing Requests

How to:

Optimize Requests

Reference:

SQL Generation in Optimization Examples

The adapter can optimize DML requests by creating SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.

Syntax: How to Optimize Requests

SQL ADAPTER_ID SET {OPTIMIZATION|SQLJOIN} setting

where:

ADAPTER_ID

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

The adapter values are:

Adapter	Adapter ID	Adapter	Adapter ID
1010data	sql1010	Cache	dbsqlism
Db2	dbdb2	Excel	dbsqlexc
HP Vertica	dbsqlvrt	Hyperstage	dbsqlhyp
i Access	dbsqliia	CA-IDMS/SQL	dbsqlidm
Informix	dbsqlinf	Ingres	dbsqling
Interplex	dbsqlipx	JBoss Application Server	dbsqlmmx
JDBC	dbsqljdb	MariaDB	dbmariadb
Microsoft Access	dbsqlmac	Microsoft SQL Server	dbsqlmss
Microsoft SQL Server ODBC	dbmsodbc	MySQL	dbsqlmys
Netezza	dbsqlnez	Nucleus	dbsqlnuc
ODBC	dbsqlodb	Oracle	dbsqlora
Oracle TimesTen	dbsqlott	PostgreSQL	dbsqlpst
Progress	dbsqlpro	PSQL	dbsqlpsq
SAP Hana	dbsqlhan	SQLBase	dbsqlbas
Sybase	dbsqlsyb	Teradata	dbsqldbc
Transoft	dbsqltrn	UniData	dbsqlund
UniVerse	dbsqluv

SQLJOIN

Is a synonym for OPTIMIZATION.

setting

Is the optimization setting. Valid values are as follows:

ON instructs the adapter to create SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities. Note that the multiplicative effect may disable optimization in some cases. However, misjoined unique segments and multiplied lines in PRINT-based and LIST-based report requests do not disable optimization. This is the default.

OFF instructs the adapter to create SQL statements for simple data retrieval from each table. The server handles all aggregation, sorting, and joining in your address space or virtual machine to produce the report.

Both OPTIMIZATION settings produce the same report.

Example: SQL Requests Passed to the RDBMS With Optimization OFF

This example demonstrates SQL statements generated without optimization. The report request joins tables EMPINFO and FUNDTRAN with trace components SQLAGGR and STMTRACE allocated.

When optimization is disabled, the data adapter generates two SELECT statements. The first SELECT retrieves any rows from the EMPINFO table that have the value MIS in the DEPARTMENT column. For each EMPINFO row, the second SELECT retrieves rows from the cross-referenced FUNDTRAN table, resolving the parameter marker (?, :000n, or :H, depending on the RDBMS) with the value of the host field (EMP_ID). Both SELECT statements retrieve answer sets, but the server performs the join, sort, and aggregation operations:

SQL ADAPTER_ID SET OPTIMIZATION OFF
 JOIN EMP_ID IN EMPINFO TO ALL WHO IN FUNDTRAN AS J1
 TABLE FILE EMPINFO
  SUM AVE.CURRENT_SALARY ED_HRS BY WHO BY LAST_NAME
  IF DEPARTMENT EQ 'MIS'
 END

In a trace operation, you will see the following output:

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2511) DISABLED BY USER
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
  SELECT T1.EID,T1.LN,T1.DPT,T1.CSAL,T1.OJT
    FROM 'USER1'.'EMPINFO' T1 WHERE (T1.DPT = 'MIS') FOR FETCH ONLY;
  SELECT T2.EID FROM 'USER1'.'FUNDTRAN' T2 WHERE (T2.EID = ?)
    FOR FETCH ONLY;

Example: SQL Requests Passed to the RDBMS With Optimization ON

With optimization enabled, the data adapter generates one SELECT statement that incorporates the join, sort, and aggregation operations. The RDBMS manages and processes the request. The server only formats the report.

SQL ADAPTER_ID SET OPTIMIZATION ON
 JOIN EMP_ID IN EMPINFO TO ALL WHO IN FUNDTRAN AS J1
 TABLE FILE EMPINFO
  SUM AVE.CURRENT_SALARY ED_HRS BY WHO BY LAST_NAME
  IF DEPARTMENT EQ 'MIS'
 END

In a trace operation, you will see the following output:

AGGREGATION DONE ...
    SELECT T2.EID,T1.LN, AVG(T1.CSAL), SUM(T1.OJT)
      FROM 'USER1'.'EMPINFO' T1,'USER1'.'FUNDTRAN' T2
      WHERE (T2.EID = T1.EID) AND (T1.DPT = 'MIS')
      GROUP BY T2.EID,T1.LN
      ORDER BY T2.EID,T1.LN;

Reference: SQL Generation in Optimization Examples

There are minor differences in the specific SQL syntax generated for each RDBMS. However, the adapter messages are the same and the generated SQL statements are similar enough that most examples will illustrate the SQL syntax generated by any relational adapter.

Optimizing Requests to Pass Virtual Fields Defined as Constants

A virtual field defined as a constant is passed directly to an SQL database engine (RDBMS) for optimized processing that takes advantage of RDBMS join, sort, and aggregation capabilities, thus reducing the volume of RDBMS-to-server communication and improving response time. (In prior releases, constants were not passed to the database engine, causing optimization to be turned off.)

Constants in the following formats are passed to the RDBMS: NUMERIC, CHAR, VARCHAR, CHAR/INTEGER combination, and DATE. (Note that a few formats are not passed to the RDBMS; these are CHAR and VARCHAR combination, TIME, and DATETIME.)

When valid constants are passed to an RDBMS engine, a report is calculated based on the defined value(s) and only the calculated subset of records is returned. The extent of data manipulation at the adapter level is limited, thereby improving performance.

Example: Passing Numeric Constants

DEFINE FILE SMIX87
INTEGERCONST/I4 = 5
REALCONST/D20.2 = -97995.38
NUMERICCONST/P13.4 = -92999.3647

TABLE FILE SMIX87
 SUM INTEGERCONST REALCONST NUMERICCONST MAX.QUOT.FA01INTEGER
 BY QUOT.FA02INTEGER
 END

The following SQL is generated:

SELECT T2."FA02INTEGER", SUM(5), SUM(-97995.38),
   SUM(-92999.3647), MAX(T2."FA01INTEGER") FROM TMIX83A T2 GROUP
   BY T2."FA02INTEGER" ORDER BY T2."FA02INTEGER";

Example: Passing a CHAR Constant

DEFINE FILE SMIX87
 CHARCONST/A10 = '2N'

TABLE FILE SMIX87
 SUM CHARCONST MIN.FA02CHAR_15
 BY QUOT.FA02INTEGER
 END

The following SQL is generated:

SELECT T2."FA02INTEGER", MAX('2N'), MIN(T3."FA02CHAR_15") FROM 
   TMIX83A T2,TMIX86A T3 WHERE (T3."FA01INTEGER" =
   T2."FA01INTEGER") GROUP BY T2."FA02INTEGER" ORDER BY
   T2."FA02INTEGER";

Example: Passing CHAR and INTEGER Constants

DEFINE FILE SMIX87
 CHARCONST/A10 = '2N'
 INTEGERCONST/I4 = 5

TABLE FILE SMIX87
SUM CHARCONST INTEGERCONST
BY QUOT.FA02INTEGER
END

The following SQL is generated:

SELECT T2."FA02INTEGER", MAX('2N'), SUM(5) FROM TMIX83A T2
GROUP BY T2."FA02INTEGER" ORDER BY T2."FA02INTEGER";

	WebFOCUS

Feedback