Back to Tylogix Home Page

TYLOGIX SQL Performance Page

Link to Tylogix SQL Page
Link to Tylogix SQL Pivot Page

By Thibault Dambrine

Since I have started posting SQL code, I have noticed a number of requests for information on pivoting tables.
This is a first effort, consisting of both examples that I have created and outside links.
The two techniques below are a starting point but once you get the concept, you can apply it to a number of situations.

For variety, I have also included a number of outside links on the same topic.

SQL Performance Tips -  Links within this page	SQL Performance Tips - Outside Links
Query Join Condition Performance: Base Principles Sub-Queries - Correlated vs. Non-Correlated SQL Performance Rules Correlated Sub-Select vs. Join Comparison Correlated Sub-Select vs. Exception Join Comparison Non-Correlated Sub-Select vs. Join Comparison Performance Pitfall: The Deadly Cascaded Joins

Tylogix - SQL Performance Tips and Techniques

Query Join Condition Performance: Base Principles

- Be aware of the instances where DB2 will not use an index:
Avoid Data Type conversions
Avoid Formulas
Avoid Back-ended Wild-Cards – i.e. where COLUMN_A like ‘%ABC’

- Avoid the use of like patterns
i.e. where COLUMN_A like ‘ABC%’

- Avoid the use casting in a join
i.e. converting a INT column to a CHAR on the fly just to be able to get the join to work with compatible data types.

Order your WHERE Clauses by putting the comparisons in order of efficiency:
=
>, >=, <, <=
LIKE
<>
If possible, use WHERE in place of HAVING when using GROUP BY clause. This is because the “HAVING” clause forces a read of the entire set of data.

Go Back to the Top

Sub-Queries: Correlated vs. Non-Correlated Performance Rules

Subquery Definition:
Placing an inner query (SELECT statement)inside an outer query

Subqueries can be:
Correlated – executed once for each row returned by the outer query
Non-correlated – executed once for the entire outer

Joins vs. Sub-Queries - Rule of thumb:
Joins are more efficient than sub-queries

Exception:
When the sub-query contains one or more aggregates

Go Back to the Top

Correlated Sub-Select vs. Join Example

This query will do one sub-select scan per row in the main table:
THIS IS THE SLOW WAY TO JOIN
=================================================
SELECT SP.* FROM SUPPLY_PRICE SP
WHERE SP.PART_NUMBER
IN
(
SELECT PM.PART_NUMBER FROM PARTS_MASTER PM
WHERE PM.PART_NAME = 'COMPRESSOR'
)

This query can be re-written to get the same results much faster
by using an “INNER JOIN” instead of a “IN” with a sub-select:
THIS IS THE FAST WAY TO JOIN – PROVIDING YOU HAVE
INDEXES!
============================================================
SELECT SP.* FROM SUPPLY_PRICE SP INNER JOIN PARTS_MASTER PM
ON SP.PART_NUMBER = PM.PART_NUMBER
AND PM.PART_NAME = 'COMPRESSOR'

Go Back to the Top

Correlated Sub-Select vs. Exception Join Comparison

This query will do one sub-select scan per row in the main table:
THIS IS THE SLOW WAY TO JOIN
===========================================================
SELECT FINTRANS.*
FROM FINANCIAL_TRANSACTIONS FINTRANS
INNER JOIN MFATRNP TRNP ON
FINTRANS.CONTR_REDEM_CODE = TRNP.CONTR_REDEM_CODE
WHERE NOT EXISTS
(
SELECT '*' FROM AACORP AAC
ON
FINTRANS.FORM_SEQ_NO = AAC.FORM_SEQ_NO
AND
AAC.FORM_STATUS = "I"
)

This query can be re-written to get the same results much faster
by using an “EXCEPTION JOIN” to replace the “WHERE NOT EXISTS”:
THIS IS THE FAST WAY TO JOIN – PROVIDING YOU HAVE
INDEXES!
===========================================================
SELECT FINTRANS.*
FROM FINANCIAL_TRANSACTIONS FINTRANS
INNER JOIN MFATRNP TRNP ON
FINTRANS.CONTR_REDEM_CODE = TRNP.CONTR_REDEM_CODE
EXCEPTION JOIN AACORP AAC
ON
FINTRANS.FORM_SEQ_NO = AAC.FORM_SEQ_NO
AND
AAC.FORM_STATUS = "I"

Go Back to the Top

Non-Correlated Sub-Select vs. Join Comparison

This query does all the join work before doing the aggregation it is
THE SLOWER WAY TO JOIN
===========================================================
SELECT SP1.VENDOR_NUMBER, SP1.PART_NUMBER, SP1.DISCOUNT
FROM SUPPLY_PRICE SP1, SUPPLY_PRICE SP2
WHERE SP2.VENDOR_NUMBER = 12345
GROUP BY SP1.VENDOR_NUMBER, SP1.PART_NUMBER, SP1.DISCOUNT
HAVING SP1.DISCOUNT < AVG(SP2.DISCOUNT)

This query can be re-written to get the same results much faster
This is because the sub-select yields a single aggregated value that is calculated
only once (at the beginning) and compared to the entire set in the main query.
===========================================================
SELECT SP1.VENDOR_NUMBER, SP1.PART_NUMBER, SP1.DISCOUNT
FROM SUPPLY_PRICE SP1
WHERE SP1.DISCOUNT <
(
SELECT AVG(SP2.DISCOUNT)FROM SUPPLY_PRICE SP2
WHERE SP2.VENDOR_NUMBER = 12345
)

Go Back to the Top

Performance Pitfall: The Deadly Cascaded Joins

Take a look at the following example of the Proverbial "Forever Processing" Join:
Files BB, CC, DD are all intertwined. Beyond that, the use of “NOT LIKE as well
as the “LIKE ‘%DW%’” is very inefficient, the latter not being able to use indexes.
Using large volumes of data may cause this type of join to never come back with
a result at all.
==============================================================
INSERT INTO FILEA
SELECT BB.* FROM FILEB BB
INNER JOIN FILEC CC
ON BB.KEYB = CC.KEYC
EXCEPTION JOIN FILED DD
ON CC.KEY2 = DD.UU59UPPEID
WHERE DD.UU59UPACTY NOT LIKE '%DW%'
AND DD.DW_ROW_TYPE NOT IN ('R','P')

This query can be re-written to get the same results much faster
by using work files that divide the work in smaller chunks. Beyond this,
adding indexes to the work files can further improve the performance.

Step 1: Minimize the effect of the “LIKE ‘%DW%’” and “NOT IN clauses.
===========================================================
INSERT INTO DDWORKFILE
SELECT DD.* FROM FILED DD
WHERE DD.UU59UPACTY NOT LIKE '%DW%'
AND DD.DW_ROW_TYPE NOT IN ('R','P')

Step 2: Create a new intermediate work file with a 2-way join ONLY with tables
that are indexed.
===========================================================
INSERT INTO CCWORKFILE
SELECT CC.* FROM FILEC CC
EXCEPTION JOIN DDWORKFILE DD
ON CC.KEY2 = DD.UU59UPPEID

Step 3: Complete the select, again using only a 2-way join, ONLY with tables
that are indexed.
===========================================================
INSERT INTO FILEA
SELECT BB.* FROM FILEB BB
INNER JOIN CCWORKFILE CC
ON BB.KEYB = CC.KEYC

Go Back to the Top