September 28, 2014 at 3:08 pm
Hi
The below query is running in my production server.As u can understand the query
fetching the records & comparing columns from 2 different tables.Now the problem with this query
is taking more time for execution.It was taken 15-20 hrs for fetching & comparing the columns.
Please do some modifications in the query to execute fast.
SELECT A.TKT_DOC_PRTTN_ID,
A.TKT_DOC_NB,
A.TKT_DOC_ISS_LDT,
A.TKT_DOC_SQ_NB,
A.SM_ACCT_NB,
B.FF_NB,
B.CPN_NB,
B.CPN_ST_TYP_CD,
B.CPN_ST_SQ_NB,
B.CPN_USG_TYP_CD,
B.CPN_ST_VOID_IND,
B.FF_CRR_CD IN
FROM MLP.T_MT_TKT_DOC A,
MLP.T_MT_CPN_ST B WHERE
A.TKT_DOC_NB = B.TKT_DOC_NB AND
A.TKT_DOC_ISS_LDT = B.TKT_DOC_ISS_LDT AND
A.TKT_DOC_PRTTN_ID = B.TKT_DOC_PRTTN_ID AND
A.TKT_DOC_SQ_NB = B.TKT_DOC_SQ_NB AND
A.SM_ACCT_NB <> '' AND
B.FF_NB <> '' AND
A.SM_ACCT_NB <> B.FF_NB AND
SUBSTR(A.SM_ACCT_NB,3,10) <> B.FF_NB AND
A.SM_ACCT_NB <> SUBSTR(B.FF_NB,3,23) AND
SUBSTR(A.SM_ACCT_NB,3,10) <> SUBSTR(B.FF_NB,3,23) AND
A.SM_ACCT_NB NOT IN (SELECT C.AKA FROM
MLP.T_MBR_AKA C WHERE
(((SUBSTR(C.FK_MBR_ACCT_NUM,3,10) = SUBSTR(B.FF_NB,1,10)) OR
(SUBSTR(C.FK_MBR_ACCT_NUM,3,10) = SUBSTR(B.FF_NB,3,10)))
AND (
SUBSTR(C.AKA,3,10) = SUBSTR(A.SM_ACCT_NB,3,10) OR
SUBSTR(C.AKA,4,9) = SUBSTR(A.SM_ACCT_NB,1,9) OR
SUBSTR(C.AKA,4,9) = SUBSTR(A.SM_ACCT_NB,3,9) OR
SUBSTR(C.AKA,1,12) = SUBSTR(A.SM_ACCT_NB,1,12) OR
SUBSTR(C.AKA,1,12) = SUBSTR(A.SM_ACCT_NB,3,12) OR
SUBSTR(C.AKA,3,10) = SUBSTR(A.SM_ACCT_NB,1,10)))) AND
B.FF_NB NOT IN (SELECT D.AKA FROM
MLP.T_MBR_AKA D WHERE
(((SUBSTR(D.FK_MBR_ACCT_NUM,3,10) = SUBSTR(A.SM_ACCT_NB,1,10)) OR
(SUBSTR(D.FK_MBR_ACCT_NUM,3,10) = SUBSTR(A.SM_ACCT_NB,3,10)))
AND (
SUBSTR(D.AKA,3,10) = SUBSTR(B.FF_NB,3,10) OR
SUBSTR(D.AKA,4,9) = SUBSTR(B.FF_NB,1,9) OR
SUBSTR(D.AKA,4,9) = SUBSTR(B.FF_NB,3,9) OR
SUBSTR(D.AKA,1,12) = SUBSTR(B.FF_NB,1,12) OR
SUBSTR(D.AKA,1,12) = SUBSTR(B.FF_NB,3,12) OR
SUBSTR(D.AKA,3,10) = SUBSTR(B.FF_NB,1,10)))) AND
A.TKT_DOC_ISS_LDT BETWEEN '2014-03-15' AND '2014-09-26'
AND CPN_ST_TYP_CD = 'U'
AND CPN_ST_VOID_IND = 'N'
AND CPN_USG_TYP_CD = 'L'
AND FF_CRR_CD IN ('NW','DL')
WITH UR;
-----------------------------------------------------------------------------------------------------------
SAMPLE DATA:
MLP.T_MT_CPN_ST (1/300)
NAME TYPE LENGTH NULL VALUE
TKT_DOC_PRTTN_ID CHAR 3 747
TKT_DOC_NB CHAR 15 0068262073747
TKT_DOC_ISS_LDT DATE 10 2012-02-06
TKT_DOC_SQ_NB SMALLINT 2 1
CPN_NB SMALLINT 2 1
CPN_ST_TYP_CD CHAR 1 U
CPN_ST_SQ_NB SMALLINT 2 1
AC_SEAT_ID CHAR 3
ASSD_DOC_IND CHAR 1 N
BKG_ID CHAR 16
CPN_BKG_STT_CD CHAR 2
CPN_PAX_CT DECIMAL 5,2 1.00
CPN_ITIN_TYP_CD CHAR 1
CPN_ST_VOID_IND CHAR 1 N
CPN_USG_TYP_CD CHAR 1 L
DL_AC_TYP_CD CHAR 3
FF_CRR_CD CHAR 3
FF_NB CHAR 25
MDOC_ISSC_RSN_CD CHAR 1 A
EMD_CPN_AMT DECIMAL 18,2 0.00
EMD_CPN_CURR_CD CHAR 5
EMD_CPN_STD_AMT DECIMAL 18,2 0.00
EMD_EXCG_RT_ID INTEGER 4 0
******************************* BOTTOM OF DATA ********************************
MLP.T_MT_TKT_DOC (1/13)
NAME TYPE LENGTH NULL VALUE
TKT_DOC_PRTTN_ID CHAR 3 747
TKT_DOC_NB CHAR 15 0060195147747
TKT_DOC_ISS_LDT DATE 10 2014-08-15
TKT_DOC_SQ_NB SMALLINT 2 1
ASP_RCVD_IND CHAR 1 U
BSP_RCVD_IND CHAR 1 U
DL_PASS_TKT_TYP_CD CHAR 4
DSCD_FARE_TYP_CD CHAR 2
DL_PAX_TYP_TXT CHAR 2
EQVT_FARE_PD_AMT DECIMAL 18,2 29.25
EQVT_FPD_CURR_CD CHAR 5 USD
EQVT_FPD_STD_AMT DECIMAL 18,2 29.25
ET_RCVD_IND CHAR 1 Y
ET_TKT_DOC_TYP_CD CHAR 3
MT_TKT_DOC_TYP_CD CHAR 6 EMD
MISC_DOC_IND CHAR 1 Y
PAX_FRST_NM VARCHAR 50 'GARY LEE'
PAX_LST_NM VARCHAR 50 'SEDLACEK'
PAX_FULL_NM VARCHAR 100 'SEDLACEK/GARY LEE'
PVI_CHG_TYP_CD CHAR 1
PRMY_TKT_IND CHAR 1 Y
RND_TRP_IND CHAR 1 N
SKLTL_IND CHAR 1 N
SLD_CPN_CT SMALLINT 2 N 1
SM_ACCT_NB CHAR 25 N
TCN_RCVD_IND CHAR 1 U
TKT_DOC_EXCG_IND CHAR 1 N
TKT_DOC_STT_CD CHAR 3 N
TKT_FORM_TYP_CD CHAR 3 MCO
TKIDM_FARE_BAS_TXT CHAR 15
TKIDM_TDSGR_TXT CHAR 15
TOUR_CD_TXT CHAR 20
LST_UPDT_GTS TIMESTMP 26 2014-09-01-00.00.00.000000
RSS_OF_RSSD_IND CHAR 1 N
TKDC_REV_RDTN_IND CHAR 1 Y
TKT_CPN_RSS_TYP_CD CHAR 4
TKDC_REV_RDTN_AMT DECIMAL 18,2 0.00
TDRRDN_CURR_CD CHAR 5
TDRRDN_STD_AMT DECIMAL 18,2 0.00
DRVD_INVLT_RSS_IND CHAR 1
ATPSF_TABL_STD_AMT DECIMAL 18,2 0.00
EMD_ASSN_TYP_CD CHAR 1 S
TKDC_TOT_CURR_CD CHAR 5 USD
TKDC_TOT_STD_AMT DECIMAL 18,2 29.25
TKT_DOC_TOT_AMT DECIMAL 18,2 29.25
EXCG_VALTN_TYP_CD CHAR 4
September 28, 2014 at 3:22 pm
Have you tried posting to a DB2 forum?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
September 28, 2014 at 4:02 pm
Table definitions, index definitions and execution plan please, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2014 at 3:59 am
Here is your DB2 query reformatted somewhat for clarity. The two subqueries in the WHERE clause are most likely to hit performance due to the non-SARGable predicates. If DB2 has a LEFT() function which it can optimise, then you could replace SUBSTR() where the first argument = 1 for some gain, otherwise you best hope of optimising this is to normalise the following columns:
MLP.T_MT_CPN_ST.FF_NB
MLP.T_MT_TKT_DOC.SM_ACCT_NB
MLP.T_MBR_AKA.FK_MBR_ACCT_NUM
MLP.T_MBR_AKA.AKA
If you can attach the explain plan to your next post, there may be someone around who can interpret it and offer further suggestions.
SELECT
A.TKT_DOC_PRTTN_ID, A.TKT_DOC_NB, A.TKT_DOC_ISS_LDT, A.TKT_DOC_SQ_NB, A.SM_ACCT_NB,
B.FF_NB, B.CPN_NB, B.CPN_ST_TYP_CD, B.CPN_ST_SQ_NB, B.CPN_USG_TYP_CD, B.CPN_ST_VOID_IND,
B.FF_CRR_CD AS [IN] -- Note: IN is a keyword
FROM MLP.T_MT_TKT_DOC A
INNER JOIN MLP.T_MT_CPN_ST B
ON A.TKT_DOC_NB = B.TKT_DOC_NB
AND A.TKT_DOC_ISS_LDT = B.TKT_DOC_ISS_LDT
AND A.TKT_DOC_PRTTN_ID = B.TKT_DOC_PRTTN_ID
AND A.TKT_DOC_SQ_NB = B.TKT_DOC_SQ_NB
WHERE A.TKT_DOC_ISS_LDT BETWEEN '2014-03-15' AND '2014-09-26' -- CAST the literals to the correct type?
AND b.CPN_ST_TYP_CD = 'U'
AND b.CPN_ST_VOID_IND = 'N'
AND b.CPN_USG_TYP_CD = 'L'
AND b.FF_CRR_CD IN ('NW','DL')
AND A.SM_ACCT_NB <> ''
AND B.FF_NB <> ''
AND A.SM_ACCT_NB NOT IN (B.FF_NB, SUBSTR(B.FF_NB,3,23))
AND SUBSTR(A.SM_ACCT_NB,3,10) NOT IN (B.FF_NB, SUBSTR(B.FF_NB,3,23))
AND NOT EXISTS (
SELECT 1
FROM MLP.T_MBR_AKA C
WHERE C.AKA = A.SM_ACCT_NB
AND SUBSTR(C.FK_MBR_ACCT_NUM,3,10) IN (SUBSTR(B.FF_NB,1,10), SUBSTR(B.FF_NB,3,10)) -- correlate with B in outer query
AND ( -- correlate with A in outer query
SUBSTR(C.AKA,3,10) IN (SUBSTR(A.SM_ACCT_NB,1,10), SUBSTR(A.SM_ACCT_NB,3,10))
OR SUBSTR(C.AKA,4,9) IN (SUBSTR(A.SM_ACCT_NB,1,9), SUBSTR(A.SM_ACCT_NB,3,9))
OR SUBSTR(C.AKA,1,12) IN (SUBSTR(A.SM_ACCT_NB,1,12), SUBSTR(A.SM_ACCT_NB,3,12))
)
)
AND NOT EXISTS (
SELECT 1
FROM MLP.T_MBR_AKA D
WHERE D.AKA = B.FF_NB
AND SUBSTR(D.FK_MBR_ACCT_NUM,3,10) IN (SUBSTR(A.SM_ACCT_NB,1,10), SUBSTR(A.SM_ACCT_NB,3,10)) -- correlate with A in outer query
AND ( -- correlate with B in outer query
SUBSTR(D.AKA,3,10) IN (SUBSTR(B.FF_NB,1,10), SUBSTR(B.FF_NB,3,10))
OR SUBSTR(D.AKA,4,9) IN (SUBSTR(B.FF_NB,1,9), SUBSTR(B.FF_NB,3,9))
OR SUBSTR(D.AKA,1,12) IN (SUBSTR(B.FF_NB,1,12), SUBSTR(B.FF_NB,3,12))
)
)
WITH UR; -- DB2 dialect "Uncommitted Read"
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply