Optimization Settings

In this section:

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.


Top of page

x
Optimizing Requests

How to:

Reference:

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.



x
Syntax: How to Optimize Requests
x
SQL SQLIPX SET {OPTIMIZATION|SQLJOIN} setting

where:

SQLIPX

Is the target RDBMS. You can omit this value if you previously issued the SET SQLENGINE command.

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 SQLIPX 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 SQLIPX 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;


x
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.


Top of page

x
Optimizing Requests Containing IF-THEN-ELSE Virtual Fields

How to:

Reference:

The adapter can optimize DML requests to the server that include virtual (DEFINE) fields created using IF-THEN-ELSE syntax. In certain cases, such DEFINE fields can be passed to the RDBMS as expressions, enhancing performance and minimizing the size of the answer set returned to the server.

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.



x
Syntax: How to Optimize Requests Containing IF-THEN-ELSE Virtual Fields
x

When you issue the SET OPTIFTHENELSE command, the adapter attempts to deliver the construct of a DML IF-THEN-ELSE DEFINE field to the RDBMS as an expression. The DEFINE field must be an object of a selection test or an aggregation request. The DEFINE definition may be specified in the DML request or in the Master File.

SQL SQLIPX SET OPTIFTHENELSE {ON|OFF}

where:

SQLIPX

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

ON

Enables IF-THEN-ELSE optimization. This is the default.

OFF

Disables IF-THEN-ELSE optimization.

There is no guarantee that the SQL that is generated will improve performance for all requests. If you find that this feature does not improve performance, set OPTIFTHENELSE OFF to disable the feature.

IF-THEN-ELSE optimization applies to SELECT statements created as a result of requests and is subject to SQL limitations on optimization of DEFINE expressions.



Example: Using IF-THEN_ELSE Optimization Without Aggregation

Consider the following request:

SQL SQLIPX SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF1 = IF (LAST_NAME EQ ' ') AND (FIRST_NAME EQ '  ')
       AND (DEPARTMENT EQ 'MIS') THEN 1 ELSE 0;
END
TABLE FILE EMPINFO
PRINT DEPARTMENT LAST_NAME FIRST_NAME
WHERE DEF1 EQ 1
END

The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the WHERE DEF1 EQ 1 test:

SELECT T1.'LN',T1.'FN',T1.'DPT' FROM USER1.'EMPINFO' T1
  WHERE (((((T1.'LN' = ' ') AND (T1.'FN' = ' '))
     AND (T1.'DPT' = 'MIS'))));


Example: Using IF-THEN_ELSE Optimization With Aggregation

Consider the following request:

SQL SQLIPX SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF2 = IF LAST_NAME EQ 'SMITH' THEN 1 ELSE IF LAST_NAME EQ 'JONES' THEN 2
       ELSE IF LAST_NAME EQ 'CARTER' THEN 3 ELSE 0;
END
 
TABLE FILE EMPINFO
SUM MAX.LAST_NAME IF DEF2 EQ 1
END

The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the WHERE DEF2 EQ 1 test:

SELECT MAX(T1.'LN') FROM USER1.'EMPINFO' T1 
  WHERE (((T1.'LN' = 'SMITH')));
 
TABLE FILE EMPINFO
SUM MAX.LAST_NAME IF DEF2 EQ 2
END

The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the WHERE DEF2 EQ 2 test:

SELECT MAX(T1.'LN') FROM USER1.'EMPINFO' T1 
  WHERE (((NOT (T1.'LN' = 'SMITH')) AND (T1.'LN' = 'JONES')));


Example: Using IF-THEN_ELSE Optimization With a Condition That Is Always False
SQL SQLIPX SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF3 = IF FIRST_NAME EQ 'RITA' THEN 1 ELSE 0;
END
 
TABLE FILE EMPINFO
PRINT FIRST_NAME
IF DEF3 EQ 2
END

Because DEF3 EQ 2 will never be true, the adapter passes the WHERE test 1=0 (which is always false) to the RDBMS, returning zero records from the RDBMS:

SELECT T1.'FN' FROM USER1.'EMPINFO' T1 WHERE (1 = 0);


x
Reference: SQL Limitations on Optimization of DEFINE Expressions

Since the FOCUS reporting language is more extensive than native SQL, the data adapter cannot pass certain DEFINE expressions to the RDBMS for processing. The data adapter does not offload DEFINE-based aggregation and record selection if the DEFINE includes:

In addition, IF-THEN-ELSE optimization does not support the following features:


iWay Software