Confirmation about different coding of the same SQL

Walter Janißen

Confirmation about different coding of the same SQL
Hi all

I want to change a pretty complicated SQL-statement, which I found in an
application program and I know there are more than that, where SQL-
statements like this are used:

DECLARE CUR_AUF_LVVW0241 CURSOR FOR
SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0241 A
WHERE A.VNR = :H
AND A.BEZUGS_TYP BETWEEN :H
AND :H
AND A.BEZG_POS_LNR BETWEEN :H
AND :H
AND A.BEZG_KOMP_LNR BETWEEN :H
AND :H
AND A.HH_BEARB_ZP =
(SELECT MAX (B.HH_BEARB_ZP)
FROM LVVW0241 B
WHERE B.VNR = A.VNR
AND B.BEZUGS_TYP = A.BEZUGS_TYP
AND B.BEZG_POS_LNR = A.BEZG_POS_LNR
AND B.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND B.HH_GUELTIG_AB_DAT <= :H
AND B.HH_GUELTIG_BIS_DAT >= :H
AND B.HH_BEARB_ZP <= :H
AND (B.HH_STATUS_MERKMAL >= :H
OR B.HH_STATUS_MERKMAL = 0))
AND NOT EXISTS
(SELECT C.VNR
FROM LVVW0221 C
WHERE C.VNR = A.VNR
AND C.BEZUGS_TYP = A.BEZUGS_TYP
AND C.BEZG_POS_LNR = A.BEZG_POS_LNR
AND C.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND C.HH_GUELTIG_AB_DAT <= :H
AND C.HH_GUELTIG_BIS_DAT >= :H
AND C.HH_BEARB_ZP <= :H
AND (C.HH_STATUS_MERKMAL >= :H
OR C.HH_STATUS_MERKMAL = 0)
AND ((C.BEZUGS_TYP >= :H
AND C.BEZG_POS_LNR >= :H
AND C.BEZG_KOMP_LNR > :H)
OR (C.BEZUGS_TYP >= :H
AND C.BEZG_POS_LNR > :H)
OR (C.BEZUGS_TYP > :H)))
AND ((A.BEZUGS_TYP >= :H
AND A.BEZG_POS_LNR >= :H
AND A.BEZG_KOMP_LNR > :H)
OR (A.BEZUGS_TYP >= :H
AND A.BEZG_POS_LNR > :H)
OR (A.BEZUGS_TYP > :H))
UNION ALL
SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0221
WHERE VNR = :H
AND BEZUGS_TYP BETWEEN :H
AND :H
AND BEZG_POS_LNR BETWEEN :H
AND :H
AND BEZG_KOMP_LNR BETWEEN :H
AND :H
AND HH_GUELTIG_AB_DAT <= :H
AND HH_GUELTIG_BIS_DAT >= :H
AND HH_BEARB_ZP <= :H
AND (HH_STATUS_MERKMAL >= :H
OR HH_STATUS_MERKMAL = 0)
AND ((BEZUGS_TYP >= :H
AND BEZG_POS_LNR >= :H
AND BEZG_KOMP_LNR > :H)
OR (BEZUGS_TYP >= :H
AND BEZG_POS_LNR > :H)
OR (BEZUGS_TYP > :H))
ORDER BY 1, 2, 3, 4 FOR FETCH ONLY
WITH UR

To explain, what the query does: it want's to pick up all rows of table
LVVW0221 (which contains only the current rows) and probably rows from the
history table LVVW0241.

My proposal is as follows:

SELECT COALESCE (A.VNR, B.VNR) COALESCE(...
FROM LVVW0221 A
LEFT OUTER JOIN
(SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0241 A
WHERE A.VNR = :H
AND A.BEZUGS_TYP BETWEEN :H
AND :H
AND A.BEZG_POS_LNR BETWEEN :H
AND :H
AND A.BEZG_KOMP_LNR BETWEEN :H
AND :H
AND A.HH_BEARB_ZP =
(SELECT MAX (B.HH_BEARB_ZP)
FROM LVVW0241 B
WHERE B.VNR = A.VNR
AND B.BEZUGS_TYP = A.BEZUGS_TYP
AND B.BEZG_POS_LNR = A.BEZG_POS_LNR
AND B.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND B.HH_GUELTIG_AB_DAT <= :H
AND B.HH_GUELTIG_BIS_DAT >= :H
AND B.HH_BEARB_ZP <= :H
AND (B.HH_STATUS_MERKMAL >= :H
OR B.HH_STATUS_MERKMAL = 0)) ) AS B
on A.VNR = B.VNR
AND all other WHERE-conditions from the NOT EXISTS clause
WHERE VNR = :H
AND BEZUGS_TYP BETWEEN :H
AND :H
AND BEZG_POS_LNR BETWEEN :H
AND :H
AND BEZG_KOMP_LNR BETWEEN :H
AND :H
AND HH_GUELTIG_AB_DAT <= :H
AND HH_GUELTIG_BIS_DAT >= :H
AND HH_BEARB_ZP <= :H
AND (HH_STATUS_MERKMAL >= :H
OR HH_STATUS_MERKMAL = 0)
AND ((BEZUGS_TYP >= :H
AND BEZG_POS_LNR >= :H
AND BEZG_KOMP_LNR > :H)
OR (BEZUGS_TYP >= :H
AND BEZG_POS_LNR > :H)
OR (BEZUGS_TYP > :H))
ORDER BY 1, 2, 3, 4 FOR FETCH ONLY
WITH UR

Do the two SQL-statement produce the result and does the second query
perform better?



Dominic MORTIMER

Re: Confirmation about different coding of the same SQL
(in response to Walter Janißen)
Walter,

I think that this is going to be a difficult question to answer, without
having some idea of the cardinalities involved. Have you tried running the
two options through DB2 estimator, I've found it to be quite useful in doing
this sort of comparison. However, sometimes the access paths chosen by DB2
estimator are different to those chosen by the mainframe optimiser.

If you don't know, DB2 Estimator is a windows based tool which can be
downloaded for free from the IBM web site, and covers all supported versions
of DB2.

Another way would of finding out would be to run both options against a copy
of your production data.

Cheers

Dom
Senior DBA
SchlumbergerSema

-----Original Message-----
From: Walter Janißen [mailto:[login to unmask email]
Sent: 09 January 2003 09:17
To: [login to unmask email]
Subject: Confirmation about different coding of the same SQL


Hi all

I want to change a pretty complicated SQL-statement, which I found in an
application program and I know there are more than that, where SQL-
statements like this are used:

DECLARE CUR_AUF_LVVW0241 CURSOR FOR
SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0241 A
WHERE A.VNR = :H
AND A.BEZUGS_TYP BETWEEN :H
AND :H
AND A.BEZG_POS_LNR BETWEEN :H
AND :H
AND A.BEZG_KOMP_LNR BETWEEN :H
AND :H
AND A.HH_BEARB_ZP =
(SELECT MAX (B.HH_BEARB_ZP)
FROM LVVW0241 B
WHERE B.VNR = A.VNR
AND B.BEZUGS_TYP = A.BEZUGS_TYP
AND B.BEZG_POS_LNR = A.BEZG_POS_LNR
AND B.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND B.HH_GUELTIG_AB_DAT <= :H
AND B.HH_GUELTIG_BIS_DAT >= :H
AND B.HH_BEARB_ZP <= :H
AND (B.HH_STATUS_MERKMAL >= :H
OR B.HH_STATUS_MERKMAL = 0))
AND NOT EXISTS
(SELECT C.VNR
FROM LVVW0221 C
WHERE C.VNR = A.VNR
AND C.BEZUGS_TYP = A.BEZUGS_TYP
AND C.BEZG_POS_LNR = A.BEZG_POS_LNR
AND C.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND C.HH_GUELTIG_AB_DAT <= :H
AND C.HH_GUELTIG_BIS_DAT >= :H
AND C.HH_BEARB_ZP <= :H
AND (C.HH_STATUS_MERKMAL >= :H
OR C.HH_STATUS_MERKMAL = 0)
AND ((C.BEZUGS_TYP >= :H
AND C.BEZG_POS_LNR >= :H
AND C.BEZG_KOMP_LNR > :H)
OR (C.BEZUGS_TYP >= :H
AND C.BEZG_POS_LNR > :H)
OR (C.BEZUGS_TYP > :H)))
AND ((A.BEZUGS_TYP >= :H
AND A.BEZG_POS_LNR >= :H
AND A.BEZG_KOMP_LNR > :H)
OR (A.BEZUGS_TYP >= :H
AND A.BEZG_POS_LNR > :H)
OR (A.BEZUGS_TYP > :H))
UNION ALL
SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0221
WHERE VNR = :H
AND BEZUGS_TYP BETWEEN :H
AND :H
AND BEZG_POS_LNR BETWEEN :H
AND :H
AND BEZG_KOMP_LNR BETWEEN :H
AND :H
AND HH_GUELTIG_AB_DAT <= :H
AND HH_GUELTIG_BIS_DAT >= :H
AND HH_BEARB_ZP <= :H
AND (HH_STATUS_MERKMAL >= :H
OR HH_STATUS_MERKMAL = 0)
AND ((BEZUGS_TYP >= :H
AND BEZG_POS_LNR >= :H
AND BEZG_KOMP_LNR > :H)
OR (BEZUGS_TYP >= :H
AND BEZG_POS_LNR > :H)
OR (BEZUGS_TYP > :H))
ORDER BY 1, 2, 3, 4 FOR FETCH ONLY
WITH UR

To explain, what the query does: it want's to pick up all rows of table
LVVW0221 (which contains only the current rows) and probably rows from the
history table LVVW0241.

My proposal is as follows:

SELECT COALESCE (A.VNR, B.VNR) COALESCE(...
FROM LVVW0221 A
LEFT OUTER JOIN
(SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0241 A
WHERE A.VNR = :H
AND A.BEZUGS_TYP BETWEEN :H
AND :H
AND A.BEZG_POS_LNR BETWEEN :H
AND :H
AND A.BEZG_KOMP_LNR BETWEEN :H
AND :H
AND A.HH_BEARB_ZP =
(SELECT MAX (B.HH_BEARB_ZP)
FROM LVVW0241 B
WHERE B.VNR = A.VNR
AND B.BEZUGS_TYP = A.BEZUGS_TYP
AND B.BEZG_POS_LNR = A.BEZG_POS_LNR
AND B.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND B.HH_GUELTIG_AB_DAT <= :H
AND B.HH_GUELTIG_BIS_DAT >= :H
AND B.HH_BEARB_ZP <= :H
AND (B.HH_STATUS_MERKMAL >= :H
OR B.HH_STATUS_MERKMAL = 0)) ) AS B
on A.VNR = B.VNR
AND all other WHERE-conditions from the NOT EXISTS clause
WHERE VNR = :H
AND BEZUGS_TYP BETWEEN :H
AND :H
AND BEZG_POS_LNR BETWEEN :H
AND :H
AND BEZG_KOMP_LNR BETWEEN :H
AND :H
AND HH_GUELTIG_AB_DAT <= :H
AND HH_GUELTIG_BIS_DAT >= :H
AND HH_BEARB_ZP <= :H
AND (HH_STATUS_MERKMAL >= :H
OR HH_STATUS_MERKMAL = 0)
AND ((BEZUGS_TYP >= :H
AND BEZG_POS_LNR >= :H
AND BEZG_KOMP_LNR > :H)
OR (BEZUGS_TYP >= :H
AND BEZG_POS_LNR > :H)
OR (BEZUGS_TYP > :H))
ORDER BY 1, 2, 3, 4 FOR FETCH ONLY
WITH UR

Do the two SQL-statement produce the result and does the second query
perform better?







_________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_________________________________________________________



Terry Purcell

Re: Confirmation about different coding of the same SQL
(in response to Dominic MORTIMER)
Walter,

The first query returns all qualifying rows that are in LVVW0241, but not in
LVVW0221. PLUS all qualifying rows from LVVW0221.

The second returns all qualifying rows from LVVW0221 regardless of whether
or not they are in LVVW0241.

If I have read that correctly, they may not return the same result (but they
may if the data dictates it). Depending on the relationship between the two
tables (is it a parent/child thus one way is optional and the other is
mandatory?), then you may want a RIGHT JOIN rather than LEFT.

Like Dominic, I would suggest you test the queries to ensure you get the
same result, then worry about performance. I think you are definitely on the
right track of combining the queries for performance (but we'll leave that
for another day).

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of MORTIMER, Dominic
Sent: Thursday, January 09, 2003 4:22 AM
To: [login to unmask email]
Subject: Re: Confirmation about different coding of the same SQL


Walter,

I think that this is going to be a difficult question to answer, without
having some idea of the cardinalities involved. Have you tried running the
two options through DB2 estimator, I've found it to be quite useful in doing
this sort of comparison. However, sometimes the access paths chosen by DB2
estimator are different to those chosen by the mainframe optimiser.

If you don't know, DB2 Estimator is a windows based tool which can be
downloaded for free from the IBM web site, and covers all supported versions
of DB2.

Another way would of finding out would be to run both options against a copy
of your production data.

Cheers

Dom
Senior DBA
SchlumbergerSema

-----Original Message-----
From: Walter Janißen [mailto:[login to unmask email]
Sent: 09 January 2003 09:17
To: [login to unmask email]
Subject: Confirmation about different coding of the same SQL


Hi all

I want to change a pretty complicated SQL-statement, which I found in an
application program and I know there are more than that, where SQL-
statements like this are used:

DECLARE CUR_AUF_LVVW0241 CURSOR FOR
SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0241 A
WHERE A.VNR = :H
AND A.BEZUGS_TYP BETWEEN :H
AND :H
AND A.BEZG_POS_LNR BETWEEN :H
AND :H
AND A.BEZG_KOMP_LNR BETWEEN :H
AND :H
AND A.HH_BEARB_ZP =
(SELECT MAX (B.HH_BEARB_ZP)
FROM LVVW0241 B
WHERE B.VNR = A.VNR
AND B.BEZUGS_TYP = A.BEZUGS_TYP
AND B.BEZG_POS_LNR = A.BEZG_POS_LNR
AND B.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND B.HH_GUELTIG_AB_DAT <= :H
AND B.HH_GUELTIG_BIS_DAT >= :H
AND B.HH_BEARB_ZP <= :H
AND (B.HH_STATUS_MERKMAL >= :H
OR B.HH_STATUS_MERKMAL = 0))
AND NOT EXISTS
(SELECT C.VNR
FROM LVVW0221 C
WHERE C.VNR = A.VNR
AND C.BEZUGS_TYP = A.BEZUGS_TYP
AND C.BEZG_POS_LNR = A.BEZG_POS_LNR
AND C.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND C.HH_GUELTIG_AB_DAT <= :H
AND C.HH_GUELTIG_BIS_DAT >= :H
AND C.HH_BEARB_ZP <= :H
AND (C.HH_STATUS_MERKMAL >= :H
OR C.HH_STATUS_MERKMAL = 0)
AND ((C.BEZUGS_TYP >= :H
AND C.BEZG_POS_LNR >= :H
AND C.BEZG_KOMP_LNR > :H)
OR (C.BEZUGS_TYP >= :H
AND C.BEZG_POS_LNR > :H)
OR (C.BEZUGS_TYP > :H)))
AND ((A.BEZUGS_TYP >= :H
AND A.BEZG_POS_LNR >= :H
AND A.BEZG_KOMP_LNR > :H)
OR (A.BEZUGS_TYP >= :H
AND A.BEZG_POS_LNR > :H)
OR (A.BEZUGS_TYP > :H))
UNION ALL
SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0221
WHERE VNR = :H
AND BEZUGS_TYP BETWEEN :H
AND :H
AND BEZG_POS_LNR BETWEEN :H
AND :H
AND BEZG_KOMP_LNR BETWEEN :H
AND :H
AND HH_GUELTIG_AB_DAT <= :H
AND HH_GUELTIG_BIS_DAT >= :H
AND HH_BEARB_ZP <= :H
AND (HH_STATUS_MERKMAL >= :H
OR HH_STATUS_MERKMAL = 0)
AND ((BEZUGS_TYP >= :H
AND BEZG_POS_LNR >= :H
AND BEZG_KOMP_LNR > :H)
OR (BEZUGS_TYP >= :H
AND BEZG_POS_LNR > :H)
OR (BEZUGS_TYP > :H))
ORDER BY 1, 2, 3, 4 FOR FETCH ONLY
WITH UR

To explain, what the query does: it want's to pick up all rows of table
LVVW0221 (which contains only the current rows) and probably rows from the
history table LVVW0241.

My proposal is as follows:

SELECT COALESCE (A.VNR, B.VNR) COALESCE(...
FROM LVVW0221 A
LEFT OUTER JOIN
(SELECT VNR, BEZUGS_TYP, BEZG_POS_LNR, BEZG_KOMP_LNR,
BEZG_KA_SL, BEDING_ART_SL_01, BEDING_NR_01,
...
FROM LVVW0241 A
WHERE A.VNR = :H
AND A.BEZUGS_TYP BETWEEN :H
AND :H
AND A.BEZG_POS_LNR BETWEEN :H
AND :H
AND A.BEZG_KOMP_LNR BETWEEN :H
AND :H
AND A.HH_BEARB_ZP =
(SELECT MAX (B.HH_BEARB_ZP)
FROM LVVW0241 B
WHERE B.VNR = A.VNR
AND B.BEZUGS_TYP = A.BEZUGS_TYP
AND B.BEZG_POS_LNR = A.BEZG_POS_LNR
AND B.BEZG_KOMP_LNR = A.BEZG_KOMP_LNR
AND B.HH_GUELTIG_AB_DAT <= :H
AND B.HH_GUELTIG_BIS_DAT >= :H
AND B.HH_BEARB_ZP <= :H
AND (B.HH_STATUS_MERKMAL >= :H
OR B.HH_STATUS_MERKMAL = 0)) ) AS B
on A.VNR = B.VNR
AND all other WHERE-conditions from the NOT EXISTS clause
WHERE VNR = :H
AND BEZUGS_TYP BETWEEN :H
AND :H
AND BEZG_POS_LNR BETWEEN :H
AND :H
AND BEZG_KOMP_LNR BETWEEN :H
AND :H
AND HH_GUELTIG_AB_DAT <= :H
AND HH_GUELTIG_BIS_DAT >= :H
AND HH_BEARB_ZP <= :H
AND (HH_STATUS_MERKMAL >= :H
OR HH_STATUS_MERKMAL = 0)
AND ((BEZUGS_TYP >= :H
AND BEZG_POS_LNR >= :H
AND BEZG_KOMP_LNR > :H)
OR (BEZUGS_TYP >= :H
AND BEZG_POS_LNR > :H)
OR (BEZUGS_TYP > :H))
ORDER BY 1, 2, 3, 4 FOR FETCH ONLY
WITH UR

Do the two SQL-statement produce the result and does the second query
perform better?