Show TOC

Performance Optimization (New and Changed)Locate this document in the navigation structure

Changes that improve database performance.

Improving Equality Condition Performance

SAP IQ leverages information available in a hash-partitioned or a hash-range-partitioned table to improve the performance of some equality conditions on the hash-partitioning basis column.

The optimization is applied when all of the following are true:
  1. The SQL statement contains a condition of the form:
    <column> = constant-expression
    The expression cannot apply to <>, <, <=, >, >=, IN, NOT IN, or BETWEEN conditions.
  2. That <column> is a base column in a real table, not from a view or a derived table.

  3. The base column is the sole basis for the hash partitioning of the table containing that <column>.

  4. That constant-expression is either a literal constant, a host variable, or a simple scalar expression containing only constants and host variables, not a scalar subquery or an aggregate expression.

  5. The equality condition is in the SQL statement at a location which allows it to be applied at the leaf node in the query plan, not in some other query block, and not in a ON clause of FULL OUTER JOIN in the same query block.

  6. The datatype of the constant-expression is highly compatible with the datatype of the base column.

  7. There is no HG or LF index on that base column. When all these conditions are true, then the optimizer will infer an additional condition of the form:
    HashPartitionNumber( <column> ) = integer-constant
    where that integer-constant is the hash partition number for the constant-expression in the original condition. This condition will appear in the query plan like any other inferred condition.
By executing this inferred condition before the original equality condition, that original equality condition only needs to scan the FP cell values for rows contained within that one hash partition.
Additional Information

Performance and Tuning > Schema Design > Hash Partitioning

LOAD TABLE IGNORE CONSTRAINT Behavior Change

In SAP IQ 16.0, a redesign in memory usage reduced memory footprint and provided better parallel processing for loads. As a result, the row log file for LOAD TABLE IGNORE CONSTRAINT now returns NULL strings instead of the original file contents for certain values when data conversion errors occur.

For example, assume the following SQL statement:
LOAD TABLE dbo.IQ_TEST
   (
      amount ASCII(10) null(ZEROS),
       FILLER ('\n')
)
      FROM 'c:\data\test.data'
quotes off
escapes off
format 'ascii'
strip off
with checkpoint off
ignore constraint DATA VALUE 50
message Log 'c:\log\test.log' row log 
'c:\log\test.row' only log ALL
The test.data file contains these values:
100.4     
ABCED     
265.3456  
23.4          
 .              
234           
345 

SAP IQ 15.4 returns these rows:

2 ,ABCED     ,
5 , .        ,
SAP IQ 16.0 returns these rows:
1 ,NULL, 
2 ,NULL, 

Cases such as column check constraints continue to generate the correct rows.

UNION ALL Parallelism

SAP IQ now fully parallelizes and distributes UNION ALL operators on a cost basis, along with other data flow operators, regardless of the number of unions, union arms, or operator types. You will see significant performance increases over previous releases, on the same hardware, without tuning or schema changes. This improvement particularly benefits DAS configurations. Unions are even partially parallelized if some arms do not allow parallelism or are not worth parallelizing on a cost basis.

Examples of common UNION ALL operations:
  • Queries combine similar data from multiple locations, often a small work table and a larger historical table. Queries typically select data from all tables.

  • Identical tables store a similar size period of data. Queries typically select data from specific periods.

Direct I/O Support

SAP IQ now opens all UFS (Unix File System) database files using direct I/O.