, TXX_USER_ID
, TXX_TIMESTAMP
FROM T_TRANSPORT_PROT_S
WHERE (TXX_WGN_RIV_CODE BETWEEN
:WV-LO-TXX-WGN-RIV-CODE AND :WV-HI-TXX-WGN-RIV-CODE
) AND NOT (((
TXX_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR < :WV-LO-TXX-WGN-BVW-NR
) OR (
TXX_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ < :WV-LO-TXX-WGN-GATT-KENNZ
) OR (
TXX_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ = :WV-LO-TXX-WGN-GATT-KENNZ
AND
TXX_WGN_SEQ_NR < :WV-LO-TXX-WGN-SEQ-NR
) OR (
TXX_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ = :WV-LO-TXX-WGN-GATT-KENNZ
AND
TXX_WGN_SEQ_NR = :WV-LO-TXX-WGN-SEQ-NR
AND
TXX_WGN_PRUEF_ZF < :WV-LO-TXX-WGN-PRUEF-ZF
) OR (
TXX_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ = :WV-LO-TXX-WGN-GATT-KENNZ
AND
TXX_WGN_SEQ_NR = :WV-LO-TXX-WGN-SEQ-NR
AND
TXX_WGN_PRUEF_ZF = :WV-LO-TXX-WGN-PRUEF-ZF
AND
TXX_ID <WV> :WV-HI-TXX-WGN-BVW-NR
) OR (
TXX_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ > :WV-HI-TXX-WGN-GATT-KENNZ
) OR (
TXX_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ = :WV-HI-TXX-WGN-GATT-KENNZ
AND
TXX_WGN_SEQ_NR > :WV-HI-TXX-WGN-SEQ-NR
) OR (
TXX_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ = :WV-HI-TXX-WGN-GATT-KENNZ
AND
TXX_WGN_SEQ_NR = :WV-HI-TXX-WGN-SEQ-NR
AND
TXX_WGN_PRUEF_ZF > :WV-HI-TXX-WGN-PRUEF-ZF
) OR (
TXX_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TXX_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TXX_WGN_GATT_KENNZ = :WV-HI-TXX-WGN-GATT-KENNZ
AND
TXX_WGN_SEQ_NR = :WV-HI-TXX-WGN-SEQ-NR
AND
TXX_WGN_PRUEF_ZF = :WV-HI-TXX-WGN-PRUEF-ZF
AND
TRANSLATE(TXX_ID, 'X', 'X') > :WV-HI-TXX-ID
)))
UNION
SELECT
TYY_WGN_RIV_CODE AS TXX_WGN_RIV_CODE
, TYY_WGN_BVW_NR AS TXX_WGN_BVW_NR
, TYY_WGN_GATT_KENNZ AS TXX_WGN_GATT_KENNZ
, TYY_WGN_SEQ_NR AS TXX_WGN_SEQ_NR
, TYY_WGN_PRUEF_ZF AS TXX_WGN_PRUEF_ZF
, TYY_ID AS TXX_ID
, TYY_BLOCK_ID AS TXX_BLOCK_ID
, TYY_AFT_SEQ_NR AS TXX_AFT_SEQ_NR
, TYY_AFT_ERFASS_NR AS TXX_AFT_ERFASS_NR
, TYY_AFT_DATUM AS TXX_AFT_DATUM
, TYY_AFT_BVW_NR AS TXX_AFT_BVW_NR
, TYY_AFT_BEFP_VERS AS TXX_AFT_BEFP_VERS
, TYY_PVG_AFT_ID AS TXX_PVG_AFT_ID
, TYY_USER_ID AS TXX_USER_ID
, TYY_TIMESTAMP AS TXX_TIMESTAMP
FROM T_TRANSPORT_PROT
WHERE (TYY_WGN_RIV_CODE BETWEEN
:WV-LO-TXX-WGN-RIV-CODE AND :WV-HI-TXX-WGN-RIV-CODE
) AND NOT (((
TYY_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR < :WV-LO-TXX-WGN-BVW-NR
) OR (
TYY_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ < :WV-LO-TXX-WGN-GATT-KENNZ
) OR (
TYY_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ = :WV-LO-TXX-WGN-GATT-KENNZ
AND
TYY_WGN_SEQ_NR < :WV-LO-TXX-WGN-SEQ-NR
) OR (
TYY_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ = :WV-LO-TXX-WGN-GATT-KENNZ
AND
TYY_WGN_SEQ_NR = :WV-LO-TXX-WGN-SEQ-NR
AND
TYY_WGN_PRUEF_ZF < :WV-LO-TXX-WGN-PRUEF-ZF
) OR (
TYY_WGN_RIV_CODE = :WV-LO-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-LO-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ = :WV-LO-TXX-WGN-GATT-KENNZ
AND
TYY_WGN_SEQ_NR = :WV-LO-TXX-WGN-SEQ-NR
AND
TYY_WGN_PRUEF_ZF = :WV-LO-TXX-WGN-PRUEF-ZF
AND
TYY_ID <WV> :WV-HI-TXX-WGN-BVW-NR
) OR (
TYY_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ > :WV-HI-TXX-WGN-GATT-KENNZ
) OR (
TYY_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ = :WV-HI-TXX-WGN-GATT-KENNZ
AND
TYY_WGN_SEQ_NR > :WV-HI-TXX-WGN-SEQ-NR
) OR (
TYY_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ = :WV-HI-TXX-WGN-GATT-KENNZ
AND
TYY_WGN_SEQ_NR = :WV-HI-TXX-WGN-SEQ-NR
AND
TYY_WGN_PRUEF_ZF > :WV-HI-TXX-WGN-PRUEF-ZF
) OR (
TYY_WGN_RIV_CODE = :WV-HI-TXX-WGN-RIV-CODE
AND
TYY_WGN_BVW_NR = :WV-HI-TXX-WGN-BVW-NR
AND
TYY_WGN_GATT_KENNZ = :WV-HI-TXX-WGN-GATT-KENNZ
AND
TYY_WGN_SEQ_NR = :WV-HI-TXX-WGN-SEQ-NR
AND
TYY_WGN_PRUEF_ZF = :WV-HI-TXX-WGN-PRUEF-ZF
AND
TRANSLATE(TYY_ID, 'X', 'X') > :WV-HI-TXX-ID
)))
ORDER BY TXX_ID
*MVS PORT-- BEGIN MVS
* WITH UR
*MVS PORT-- END MVS
END-EXEC.
EXEC SQL
OPEN FTCHCURA
END-EXEC.
The fetch works fine but is quite slow in performance.
Joined: 26 Nov 2002 Posts: 12401 Topics: 75 Location: San Jose
Posted: Wed Jul 26, 2006 7:29 am Post subject:
davinski_code,
First Look at the sql is that you have a lot of conditions and without the knowledge of the indexes involved we really cannot suggest improvements. May be there is a way to convert the conditions to simpler conditions depending on what you want. Also why do you need a translate verb when you are simply converting the same character? You are also checking for a negative condition which is not good.
A UNION just combines the result tables of two selects and performs an additional sort to eliminate duplicate rows. If you know, that the result sets of the selects are mutually exclusive, code a UNION ALL instead; this one will do no extra sort and is therefore giving you a better performance.
Therefore you can optimize your two selects separately; just ignore the UNION.
Looking at your statement, I can say that using ORs is never good for the performance, since this mostly hinders efficient index utilization. Perhaps you split your selects and use even more UNIONs (or UNION ALLs) to give each OR-block a separate select.
If this does not lead to propper index usage, you are better of with your current selects, since they will do just a single tablespace scan each, and not multiple ones.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
