query running 15hrs for 1lakh records

Question

Post reply

query running 15hrs for 1lakh records

cnu1252

Old Hand

Points: 377
More actions
September 28, 2014 at 3:08 pm

#291109

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Have you tried posting to a DB2 forum?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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