SQL queriesunder workspaces can suffer performance issues. Performance degradationoften occurs if the database is not being properly maintained or ifqueries can be rewritten more efficiently. In addition to making surethat the database is properly maintained, there are several techniquesthat can help improve the performance of SQL queries under workspaces.While no single technique yields significant results, a combinationof several techniques can help achieve considerable performance improvementsin many applications.
See Maintaining a WebSphere Commerce DB2 Database for tips on how to maintain a WebSphere CommerceDB2 database. For Oracle databases, see the Oracle documentation onhow to update the table and index statistics.
- General READ SQL optimizationfor DB2 and Oracle
- Optimize queries basedon the query optimization guidelines
- Push predicates intothe OUTER JOIN clause whenever possible
- Duplicate constant conditionfor different tables whenever possible
- Using nested table definitionsto replace workspaces views
- Splitting queries
- Using common expressionsyntax in Oracle
General READ SQL optimization for DB2 andOracle
These techniques apply to both DB2 and Oracle, however,the rules appear to yield a better response time on DB2.
Optimize queries based on the query optimizationguidelines
Follow the SQL best practices to ensure queryoptimization:
- Index all the predicates in JOIN, WHERE, ORDER BY and GROUP BYclauses.
WebSphere Commerce typically depends heavily on indexesto improve SQL performance and scalability. Without proper indexes,SQL queries can cause table scans, which causes either performanceor locking problems. It is recommended that all predicate columnsbe indexed. The exception being where column data has very low cardinality.
- Avoid using functions in predicates.
The index is not used bythe database if there is a function on the column. For example:
SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'As a result of the function UPPER(), the index on COL1is not used by database optimizers. If the function cannot be avoidedin the SQL, you need to create a function-based index in Oracle orgenerated columns in DB2 to improve performance.
- Avoid using wildcard (%) at the beginning of a predicate.
Thepredicate
LIKE '%abc'causes full table scan. Forexample:SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'This is a known performance limitation in all databases.
- Avoid unnecessary columns in SELECT clause.
Specify the columnsin the
SELECTclause instead of usingSELECT*. The unnecessary columns places extra loads on the database,which slows down not just the single SQL, but the whole system. - Use inner join, instead of outer join if possible.
The outerjoin should only be used if it is necessary. Using outer join limitsthe database optimization options which typically results in slowerSQL execution.
- DISTINCT and UNION should be used only if it is necessary.
DISTINCTand UNION operators cause sorting, which slows down the SQL execution.Use UNION ALL instead of UNION, if possible, as it is much more efficient.
- Oracle 10g and 11g requires that the CLOB/BLOB columns must beput at the end of the statements.
Otherwise, it causes failure whenthe input value size is larger than 1000 characters.
- The ORDER BY clause is mandatory in SQL if the sorted result setis expected.
The ORDER BY keyword is used to sort the result-setby specified columns. Without the ORDER BY clause, the result setis returned directly without any sorting. The order is not guaranteed.Be aware of the performance impact of adding the ORDER BY clause,as the database needs to sort the result set, resulting in one ofthe most expensive operations in SQL execution.
Push predicates into the OUTER JOIN clausewhenever possible
For SQL queries with the LEFT OUTER JOIN,pushing predicates of the right table from the WHERE clause into theON condition helps the database optimizer generate a more efficientquery. Predicates of the left table can stay in the WHERE clause.
Similarly,for the SQL queries with the RIGHT OUTER JOIN, predicates for theright table should be moved from the WHERE clause into the ON condition.
Forexample, the suboptimal query is rewritten by pushing predicates applicableto the table TAB_B into the ON clause. The TAB_A specific predicatesin the WHERE clause can either stay, or be pushed into the ON clause:
SuboptimalSQL statement:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1=123 AND TAB_B.COL2=456;Optimized SQL statement:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2=456 WHERE TAB_A.COL1=123;Predicates for any INNER joins can stay in theWHERE clause. If tables TAB_A and TAB_B are defined as views, theoptimizer can push these predicates into the views.
Duplicate constant condition for differenttables whenever possible
When two tables, A and B, are joinedand there is a constant predicate on one of the joined columns, forexample, A.id=B.id and A.id in (10, 12), the constant predicate shouldbe duplicated for the joined column of the second table. That is,A.id=B.id and A.id in (10, 12) and B.id in (10, 12).
For example,TAB_A has a LEFT OUTER JOIN relationship with TAB_B. If there is aTAB_A specific conditions and a cross table condition with TAB_B,create an extra TAB_B specific condition based on TAB_A requirementand keep cross table conditions in the ON clause:
SuboptimalSQL statement:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1 IN (123, 456) AND TAB_B.COL2=TAB_A.COL1;Optimized SQL statement:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2 IN (123, 456) AND TAB_B.COL2=TAB_A.COL1 WHERE TAB_A.COL1 IN (123, 456);In particular, if the constant predicate has only1 value (e.g COL1=123), the second predicate should be converted toa constant predicate too.
For example:
Suboptimal SQLstatement:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1=123 AND TAB_B.COL2=TAB_A.COL1;Optimized SQL statement:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2=123 WHERE TAB_A.COL1=123;Using nested table definitions to replaceworkspaces views
To improve performance in the workspacesenvironment you can use nested table definitions to replace the standardviews defined for the content-managed tables. The following examplequery uses the default views for tables CATENTRY, ATTRIBUTE, and ATTRVALUE:
SELECT CATENTRY.CATENTRY_ID, ATTRIBUTE.ATTRIBUTE_ID, ATTRIBUTE.NAME, ATTRIBUTE.LANGUAGE_ID, ATTRIBUTE.CATENTRY_ID, ATTRVALUE.ATTRVALUE_ID, ATTRVALUE.ATTRIBUTE_ID, ATTRVALUE.CATENTRY_ID, ATTRVALUE.STRINGVALUEFROM CATENTRY, ATTRVALUE, ATTRIBUTEWHERE ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTRY.CATENTRY_ID IN (10683) AND ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 ) AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_IDORDER BY ATTRIBUTE.SEQUENCEThis query can be rewritten to use nested tablesdefinitions for tables CATENTRY, ATTRVALUE, and ATTRIBUTE:
SELECT CATENTRY.CATENTRY_ID, ATTRIBUTE.ATTRIBUTE_ID, ATTRIBUTE.NAME, ATTRIBUTE.LANGUAGE_ID, ATTRIBUTE.CATENTRY_ID, ATTRVALUE.ATTRVALUE_ID, ATTRVALUE.ATTRIBUTE_ID, ATTRVALUE.CATENTRY_ID, ATTRVALUE.STRINGVALUEFROM ( SELECT CATENTRY_ID FROM DB2INST1.CATENTRY WHERE NOT EXISTS ( SELECT '1' FROM WCW101.CATENTRY WHERE DB2INST1.CATENTRY.CATENTRY_ID = WCW101.CATENTRY.CATENTRY_ID) UNION ALL SELECT CATENTRY_ID FROM WCW101.CATENTRY WHERE WCW101.CATENTRY.CONTENT_STATUS <> 'D' ) CATENTRY, ( SELECT ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE FROM DB2INST1.ATTRVALUE WHERE NOT EXISTS ( SELECT '1' FROM WCW101.ATTRVALUE WHERE DB2INST1.ATTRVALUE.ATTRVALUE_ID = WCW101.ATTRVALUE.ATTRVALUE_ID) UNION ALL SELECT ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE FROM WCW101.ATTRVALUE WHERE WCW101.ATTRVALUE.CONTENT_STATUS <> 'D' ) ATTRVALUE, ( SELECT ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME FROM DB2INST1.ATTRIBUTE WHERE NOT EXISTS ( SELECT '1' FROM WCW101.ATTRIBUTE WHERE DB2INST1.ATTRIBUTE.ATTRIBUTE_ID = WCW101.ATTRIBUTE.ATTRIBUTE_ID) UNION ALL SELECT ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME FROM WCW101.ATTRIBUTE WHERE WCW101.ATTRIBUTE.CONTENT_STATUS <> 'D' ) ATTRIBUTEWHERE ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTRY.CATENTRY_ID IN (10683) AND ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 ) AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_IDORDER BY ATTRIBUTE.SEQUENCEOccasionally, further performance gains can beachieved by pushing in predicates into nested table definitions. Thismight reduce the amount of data that needs to be fetched for nestedtables. Continuing with the preceding example, the predicate LANGUAGE_IDIN ( -1,-2,-3,-4,-5 ) is injected into the definition ofthe ATTRVALUE nested table in the following query:
SELECT CATENTRY.CATENTRY_ID, ATTRIBUTE.ATTRIBUTE_ID, ATTRIBUTE.NAME, ATTRIBUTE.LANGUAGE_ID, ATTRIBUTE.CATENTRY_ID, ATTRVALUE.ATTRVALUE_ID, ATTRVALUE.ATTRIBUTE_ID, ATTRVALUE.CATENTRY_ID, ATTRVALUE.STRINGVALUEFROM ( SELECT CATENTRY_ID FROM DB2INST1.CATENTRY WHERE NOT EXISTS ( SELECT '1' FROM WCW101.CATENTRY WHERE DB2INST1.CATENTRY.CATENTRY_ID = WCW101.CATENTRY.CATENTRY_ID) UNION ALL SELECT CATENTRY_ID FROM WCW101.CATENTRY WHERE WCW101.CATENTRY.CONTENT_STATUS <> 'D' ) CATENTRY, ( SELECT ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE FROM DB2INST1.ATTRVALUE WHERE NOT EXISTS ( SELECT '1' FROM WCW101.ATTRVALUE WHERE DB2INST1.ATTRVALUE.ATTRVALUE_ID = WCW101.ATTRVALUE.ATTRVALUE_ID) AND DB2INST1.ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 ) UNION ALL SELECT ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE FROM WCW101.ATTRVALUE WHERE WCW101.ATTRVALUE.CONTENT_STATUS <> 'D' AND WCW101.ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 ) ) ATTRVALUE, ( SELECT ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME FROM DB2INST1.ATTRIBUTE WHERE NOT EXISTS ( SELECT '1' FROM WCW101.ATTRIBUTE WHERE DB2INST1.ATTRIBUTE.ATTRIBUTE_ID = WCW101.ATTRIBUTE.ATTRIBUTE_ID) UNION ALL SELECT ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME FROM WCW101.ATTRIBUTE WHERE WCW101.ATTRIBUTE.CONTENT_STATUS <> 'D' ) ATTRIBUTEWHERE ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTRY.CATENTRY_ID IN (10683) AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_IDORDER BY ATTRIBUTE.SEQUENCEQueries using nested table definitions insteadof views need to explicitly reference the base and the write schemas.These queries are only applicable to the workspaces environment, and,therefore, need to be defined in a query template under a specialcontent management section ('cm'). The following is an example ofthe preceding query, defined as a query template for both the runtimeand the workspaces environments:
Note: The following snippet uses $CM:READ$, $CM:WRITE$,and $CM:BASE$ tags. They will be replaced at runtimeby the READ, WRITE and BASE schema names when the SQL is executedby the Data Service Layer.
See Workspaces data model formore information.
BEGIN_XPATH_TO_SQL_STATEMENT name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]+IBM_Attributes base_table=CATENTRY sql= SELECT CATENTRY.$COLS:CATENTRY_ID$, ATTRIBUTE.$COLS:ATTRIBUTE$,ATTRVALUE.$COLS:ATTRVALUE$ FROM CATENTRY, ATTRVALUE, ATTRIBUTE WHERE ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTRY.CATENTRY_ID IN (?UniqueID?) AND ATTRVALUE.LANGUAGE_ID IN ($CONTROL:LANGUAGES$) AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID ORDER BY ATTRIBUTE.SEQUENCE <!-- the following query is optimized for workspaces --> cm sql= SELECT CATENTRY.$COLS:CATENTRY_ID$, ATTRIBUTE.$COLS:ATTRIBUTE$,ATTRVALUE.$COLS:ATTRVALUE$ FROM ( SELECT CATENTRY_ID FROM $CM:BASE$.CATENTRY WHERE NOT EXISTS ( SELECT '1' FROM $CM:WRITE$.CATENTRY WHERE $CM:BASE$.CATENTRY.CATENTRY_ID = $CM:WRITE$.CATENTRY.CATENTRY_ID) UNION ALL SELECT CATENTRY_ID FROM $CM:WRITE$.CATENTRY WHERE $CM:WRITE$.CATENTRY.CONTENT_STATUS <> 'D' ) CATENTRY, ( SELECT ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE FROM $CM:BASE$.ATTRVALUE WHERE NOT EXISTS ( SELECT '1' FROM $CM:WRITE$.ATTRVALUE WHERE $CM:BASE$.ATTRVALUE.ATTRVALUE_ID = $CM:WRITE$.ATTRVALUE.ATTRVALUE_ID) AND $CM:BASE$.ATTRVALUE.LANGUAGE_ID IN ($CONTROL:LANGUAGES$) UNION ALL SELECT ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE FROM $CM:WRITE$.ATTRVALUE WHERE $CM:WRITE$.ATTRVALUE.CONTENT_STATUS <> 'D' AND $CM:WRITE$.ATTRVALUE.LANGUAGE_ID IN ($CONTROL:LANGUAGES$) ) ATTRVALUE, ( SELECT ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME FROM $CM:BASE$.ATTRIBUTE WHERE NOT EXISTS ( SELECT '1' FROM $CM:WRITE$.ATTRIBUTE WHERE $CM:BASE$.ATTRIBUTE.ATTRIBUTE_ID = $CM:WRITE$.ATTRIBUTE.ATTRIBUTE_ID) UNION ALL SELECT ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME FROM $CM:WRITE$.ATTRIBUTE WHERE $CM:WRITE$.ATTRIBUTE.CONTENT_STATUS <> 'D' ) ATTRIBUTE WHERE ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTRY.CATENTRY_ID IN (?UniqueID?) AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID ORDER BY ATTRIBUTE.SEQUENCEEND_XPATH_TO_SQL_STATEMENTSplitting queries
If applying thesetechniques did not provide the necessary performance improvements,you might consider splitting queries joining many tables into multiplequeries. The objective of splitting the query is to minimize the numberof views used in the join. If you are splitting a single-step query,you will need to convert it to a two-step query with one or more associationqueries. When splitting one of the association SQL statements fora two-step query, you need to add additional association SQL statementsto the access profile definition. When queries are split, you shoulduse inner joins instead of outer joins whenever possible.
Becautious when splitting queries. If you take this approach too extensively,it might result in many queries, each selecting from one table only.In this case, you will need to perform table joins in memory by writingJava code, for example, using a graph composer. In some cases, youwill need to feed the result of one query into another. These typesof queries should be considered as a last resort, as maintainability,customization and migration challenges can arise.
Using common expression syntax in Oracle
The Oracle optimizer typically generates very efficientqueries, where a temporary table is created based on the result setof the original query joining views. A temporary table can be createdusing the common table expression syntax.
For example, the followingquery fetching product attribute values can run significantly fasterafter rewriting it using the common table expression syntax:
Standardquery fetching product attributes:
BEGIN_ASSOCIATION_SQL_STATEMENTname=IBM_CatalogEntryAttributeValuebase_table=CATENTRYsql =SELECT CATENTRY.$COLS:CATENTRY$, ATTRVALUE.$COLS:ATTRVALUE$, ATTRVALUE2.$COLS:ATTRVALUE$FROM CATENTRY, ATTRVALUE JOIN ATTRIBUTE ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID LEFT OUTER JOIN ATTRVALUE ATTRVALUE2 ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID AND ATTRVALUE2.CATENTRY_ID = 0 AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID WHERE CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$) AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND ATTRVALUE.LANGUAGE_ID IN ($CTX:LANG_ID$) END_ASSOCIATION_SQL_STATEMENTUsing common expression syntax:
BEGIN_ASSOCIATION_SQL_STATEMENTname=IBM_CatalogEntryAttributeValuebase_table=CATENTRYsql =WITH TEMP_TABLE AS (SELECT CATENTRY.CE_$COLS:CATENTRY$, ATTRVALUE.ATTR_$COLS:ATTRVALUE$, ATTRVALUE2.ATTR2_$COLS:ATTRVALUE$FROM CATENTRY, ATTRVALUE JOIN ATTRIBUTE ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID LEFT OUTER JOIN ATTRVALUE ATTRVALUE2 ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID AND ATTRVALUE2.CATENTRY_ID = 0 AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID WHERE CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$) AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID AND ATTRVALUE.LANGUAGE_ID IN ($CTX:LANG_ID$) ) SELECT * FROM TEMP_TABLEEND_ASSOCIATION_SQL_STATEMENTThis technique enables the Oracle optimizer topush the necessary predicates into the views. As more data is filteredout in the early stages of query processing, subsequent joins willneed to be applied to a much smaller data set. This often resultsin significant performance improvements.
