z/OS V7 Table Expressions vs. Joins

Philip Sevetson

z/OS V7 Table Expressions vs. Joins
We're starting to see some indicators that Table Expression performance is
significantly worse than Join performance for comparable results. We've
been on V7 for most of a year and have gotten through the familiarity
problems, and this is happening to some of our best SQL-writers.

Is anyone else seeing performance problems with Table Expressions,
particularly against tables with extremely high cardinality?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sushanta K Dash

Re: z/OS V7 Table Expressions vs. Joins
(in response to Philip Sevetson)
Hi Phil, here are my observations and experiences. It is not always true
that join will outperform the Table expressions. Even though the tables
are with extremely high cardinality the cost also depends upon certain
factors like number of rows joined and sort if any involved. Here I m citing
one example below. please check the syntax if I write wrongly.

SELECT A1, B1, C1, COUNT(*)
FROM T1, T2
WHERE T1.A1 = T2.A2
GROUP BY A1, B1, C1;

here the number of rows grouped for sorting and joined would be definitely
more than if I rewrite the query with a table expression like below.

SELECT A1, B1, C1, CNT
FROM T1, (SELECT A2 AS A2,
COUNT(*) AS CNT
FROM T2
GROUP BY A2) AS TEMPORARY
WHERE T1.A1 = TEMPORARY.A2;

In the rewritten query the number of sorting is reduced and hence the join
cost also reduced.

In your case if the tables are with high cardinality then a direct join
would give you a better filter factor and the joins would outperform the
table expression.

I m sending another criteria in another note due to rejection by list serv
for exceeding the lines.


Dash

DB2 / IMS Database Administration Tel: 91
( 44 ) 28113801. X-2366.
India Solution Center Fax: 91
(44 ) 28113790.
Chennai.
E-mail : [login to unmask email] Dash
____________________________________________________________________________
_____________
"The views expressed are my own and do not necessarily represent the views
or policy of my employer"



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sushanta K Dash

Re: z/OS V7 Table Expressions vs. Joins
(in response to Sushanta K Dash)
Phil,
Continuance to the earlier reply.
Similarly there are cases for joins which involve certain functionality to
be achieved like arithmetic calculations then the table expression would
outperform the Joins.

Here is another example. How about if the joins involve with different data
types and length where
you manipulate with STRING, DIGITs , STRIP, CONCAT etc to match the values
between the tables in join. In this case your access path gives you stage 2
and even with high cardinality one can not get more benefits. To avoid this
scenario to have better filtering we go for table expressions.

There are scenarios where the I/O bound queries are also reduced by table
expressions instead of Joins as a result of MERGE or materialization for
table expressions.

From these I can say that you should never think of completely removing the
table expressions.

I know there are experts in this group and they would add their experiences
also.

With thanks
Dash

DB2 / IMS Database Administration Tel: 91
( 44 ) 28113801. X-2366.
India Solution Center Fax: 91
(44 ) 28113790.
Chennai.
E-mail : [login to unmask email] Dash
____________________________________________________________________________
_____________
"The views expressed are my own and do not necessarily represent the views
or policy of my employer"



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: z/OS V7 Table Expressions vs. Joins
(in response to Sushanta K Dash)
Dash

I concur with you. Recently we had a very good example, where a table
expression outperforms a join, because the join condition contained the
SUBSTR-function:

SELECT * FROM DB2.INTB0002 A, DB2.INTB0002 B
WHERE A.AE_ART_SL = 'VV'
AND A.AE_NR LIKE 'SV4%' AND B.AE_NR LIKE 'UR4%
...
AND SUBSTR (A.AE_NR, 3 , 9) = SUBSTR (B.AE_NR , 3 , 9 )

This query was cancelled after 36 hours.

SELECT B.AE_NR FROM
SELECT AE_ART_SL, AE_NR, 'UR'!!SUBSTR(AE_NR, 3, 9) AS AE_NR_JOIN, ...
FROM DB2.INTB0002 A
WHERE A.AE_ART_SL = 'VV‘
AND A.AE_NR LIKE 'SV4%‘) AS A
INNER JOIN DB2.INTB0002 B
ON A.AE_ART_SL = B.AE_ART_SL
AND A.AE_NR_JOIN = B.AE_NR
....

Query ended after 1 minute.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm