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 SQLMSS SET {OPTIMIZATION|SQLJOIN} setting

where:

SQLMSS

Indicates the adapter. 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 SQLMSS 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 SQLMSS 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 SQLMSS SET OPTIFTHENELSE {ON|OFF}

where:

SQLMSS

Indicates the adapter. 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 SQLMSS 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 SQLMSS 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 SQLMSS 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:


Top of page

x
Optimizing Requests if a Virtual Field Contains Null Values

How to:

Reference:

The SET OPTNOAGGR command provides finely-tuned control of adapter behavior for optimization. Users who for any reason wish to prevent passing aggregation to the RDBMS can use this command. An example of such a reason might be where NULL values occur in aggregated data with calculations. The SET OPTNOAGGR command causes the adapter to generate SQL without passing aggregation to the DBMS. Aggregation is instead performed internally by the server while JOIN and SORT operations are handled by the RDBMS.

If any DEFINE field contains calculations with NULL fields then such operations cannot be translated to SQL and pass to DBMS because always return NULL. It has to be processed by FOCUS.

This can be achieved by SET OPTIMIZATION OFF.

However, in some cases it is preferable to use the off-load JOIN and SORT operation to DBMS for better performance while leaving AGGREGATION to FOCUS.



x
Syntax: How to Set Enhanced Aggregation Control
SQL SQLMSS SET OPT {AGGR|NOAGGR}

where:

AGGR

Directs the adapter to off-load aggregated DEFINE fields to the DBMS. This is the default setting.

NOAGGR

Directs the adapter to generate SQL without passing aggregation to the DBMS. Aggregation is, instead, performed internally by the server, while JOIN and SORT operations are handled by the RDBMS. This setting can also be used to provide backwards compatibility for applications that were written based on the functionality of the previous release, when less SQL was off-loaded to the RDBMS. For example, when a calculation on aggregated fields may have contained NULL data that was not processed by the RDBMS NVL( ) function.



Example: Using IF-THEN_ELSE Optimization With a Condition That Is Always False
SQL SQLMSS 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) FOR FETCH ONLY;


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:


Top of page

x
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";

Top of page

x
Specifying Block Size for Retrieval Processing

How to:

Reference:

The Adapter for Microsoft SQL Server supports array retrieval from result sets produced by executing SELECT queries or stored procedures. This technique substantially reduces network traffic and CPU utilization.

Using high values increases the efficiency of requests involving many rows, at the cost of higher virtual storage requirements. A value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.

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

x



x
Syntax: How to Specify Block Size for Array Retrieval

The block size for a SELECT request applies to TABLE FILE requests, MODIFY requests, MATCH requests, and DIRECT SQL SELECT statements.

ENGINE SQLMSS SET FETCHSIZE n

where:

SQLMSS

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

n

Is the number of rows to be retrieved at once using array retrieval techniques. Accepted values are 1 to 5000. The default varies by adapter. If the result set contains a column that has to be processed as a CLOB or a BLOB, the FETCHSIZE value used for that result set is 1.

x



x
Syntax: How to Specify Block Size for Insert Processing

In combination with LOADONLY, the block size for an INSERT applies to MODIFY INCLUDE requests. INSERTSIZE is also supported for parameterized DIRECT SQL INSERT statements.

ENGINE SQLMSS SET INSERTSIZE n

where:

SQLMSS

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

n

Is the number of rows to be inserted using array insert techniques. Accepted values are 1 to 5000. 1 is the default value. If the result set contains a column that has to be processed as a BLOB, the INSERTSIZE value used for that result set is 1.

x



x
Syntax: How to Suppress the Bulk Insert API
ENGINE SQLMSS SET FASTLOAD [ON|OFF]

where:

SQLMSS

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

ON

Uses the Bulk Insert API. ON is the default.

OFF

Suppresses the use of the Bulk Insert API.



x
Reference: Bulk Insert API Behavior

You can use DataMigrator with the Bulk Insert API for Microsoft SQL Server.

For the Adapter for Microsoft SQL Server, the Bulk API is used automatically in LOADONLY mode. Measurements show that intermediate flushes do not affect performance; therefore, the behavior does not depend on the INSERTSIZE.

Errors that occur during the load (such as duplication) can cause the batch of rows to be rejected as a whole.


Top of page

x
Optimizing Non-Equality WHERE-Based Left Outer Joins

How to:

Reference:

A left outer join selects all records from the host table and matches them with records from the cross-referenced table. When no matching records exist, the host record is still retained, and default values (blank or zero) are assigned to the cross-referenced fields. The adapter can optimize any WHERE-based left outer join command in which the conditional expression is supported by the RDBMS.



x
Syntax: How to Specify a Conditional Left Outer JOIN
JOIN LEFT_OUTER FILE hostfile AT hfld1 [TAG tag1]
     [WITH hfld2]
     TO {UNIQUE|MULTIPLE} 
     FILE crfile AT crfld [TAG tag2] [AS joinname]
     [WHERE expression1;
     [WHERE expression2;
     ...]
 
END

where:

LEFT_OUTER

Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.

hostfile

Is the host Master File.

AT

Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used as segment references.

hfld1

Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in its data source that is referenced.

tag1

Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.

WITH hfld2

Is a data source field with which to associate a DEFINE-based conditional JOIN. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.

MULTIPLE

Specifies a one-to-many relationship between from_file and to_file. Note that ALL is a synonym for MULTIPLE.

UNIQUE

Specifies a one-to-one relationship between hostfile and crfile. Note that ONE is a synonym for UNIQUE.

Note: Unique returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).

The unique join is a WebFOCUS concept. The RDBMS makes no distinction between unique and non-unique situations. It always retrieves all matching rows from the cross-referenced file.

If the RDBMS processes a join that the request specifies as unique, and if there are, in fact, multiple corresponding rows in the cross-referenced file, the RDBMS returns all matching rows. If, instead, optimization is disabled so that WebFOCUS processes the join, a different report results because WebFOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.

crfile

Is the cross-referenced Master File.

crfld

Is the join field name in the cross-referenced Master File. It can be any field in the segment.

tag2

Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.

joinname

Is the name associated with the joined structure.

expression1, expression2

Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.



x
Reference: Conditions for WHERE-Based Outer Join Optimization


Example: Optimizing a Non-Equality Left Outer Join

The following request creates a left outer conditional join between two MSSQL data sources and reports against the joined data sources. The STMTRACE is turned on in order to view the SQL generated for this request:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
JOIN LEFT_OUTER FILE employee AT EMPLOYEEID 
TO ALL FILE employeepayhistory AT EMPLOYEEID 
     WHERE RATECHANGEDATE GT HIREDATE;
END     
TABLE FILE employee
PRINT RATE
BY EMPLOYEEID
END

The WebFOCUS request is translated to a single MSSQL SELECT statement that incorporates the left outer join, and the non-equality condition is passed to the RDBMS in the ON clause:

SELECT T1."EmployeeID", T1."HireDate", T2."EmployeeID", 
T2."RateChangeDate", T2."Rate" FROM 
AdventureWorks.HumanResources.Employee T1 LEFT OUTER JOIN 
AdventureWorks.HumanResources.EmployeePayHistory T2 ON 
(T2."RateChangeDate" > T1."HireDate") ) ORDER BY T1."EmployeeID"; 

Top of page

x
Improving Optimizer Efficiency with Hints

How to:

DBMS Optimizer hints can be used to alter an execution plan. The adapter provides a setting which enable the TABLE command to place the hints at the end of the generated query for Microsoft SQL Server.

This occurs when the adapter constructs a single SELECT statement. It does not occur in the case of a FOCUS-managed Join when multiple SELECTs are generated.

To reverse the setting, use SET HINT without a hint_text parameter.



x
Syntax: How to Set Specific Hints

Use the following syntax to set specific hints:

SQL SQLMSS SET HINT OPTION (hint_text) 

where

SQLMSS

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

OPTION (hint_text)

Is the text of the hint or hints combination. The end user is responsible for the syntax. Omitting OPTION (hint_text) resets the hint to none.



Example: Setting an Microsoft SQL Hint
SQL SQLMSS SET HINT OPTION (FAST 2)
TABLE FILE STXT31M
 PRINT *
 BY F01INT
 END

The WebFOCUS request is translated into a SELECT statement that incorporates the specified hint.

SELECT
   T1."F01INT",
   T1."F02CHAR_10",
   T1."F03VARCHAR_10"
FROM
   D999AIXPPC71XX_TTXT31M T1
ORDER BY
   T1."F01INT"
OPTION (FAST 2);

WebFOCUS