MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Tuning the query

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
sinduja
Beginner


Joined: 20 Jun 2005
Posts: 29
Topics: 14
PostPosted: Thu Oct 16, 2008 8:00 am    Post subject: Tuning the query Reply with quote
Hi all,

I need help tuning this query. Any input is appreciated.

Details as follows:
The query:

Code:
SELECT B.TRAN_TRAN_KEY,                     
                        B.FSTN_FIRST_NAME,           
                        B.MIDN_MDDLE_NAME,       
                        B.LASN_LAST_NAME,           
                        A.PAYEE_NAME,                     
                        A.ADDR_LINE1,                       
                        A.ADDR_LINE2,                       
                        A.STREET,                                 
                        A.CITY,                                     
                        A.STATE_C,                             
                        A.ZIP_CODE,                           
                        A.COUNTRY_C,                       
                        C.CTRN_GROUP_NUMBER, 
                        C.CQHE_CIN,                                   
                        D.CMS_ACCOUNT_NUM,                   
                        B.PPTP_PMT_TYPE_C,                         
                        A.GROSS_AMT                                   
FROM   BK_PMT_TRAN A,           
           BK_PRTCPNT_MSTR B,         
           BK_GRP_MASTER C,             
           BK_GRP_PS_DD D                   
WHERE  B.SSNO_SOC_SEC_NUM = '123456798' AND           
            A.CHK_NUM = '12345' AND                              
            A.PAY_D = '2008-08-08' AND                            
            A.TRAN_TRAN_KEY = B.TRAN_TRAN_KEY AND       
            B.CTRN_GROUP_NUMBER = C.CTRN_GROUP_NUMBER AND 
            C.CTRN_GROUP_NUMBER = D.CTRN_GROUP_NUMBER AND
            D.PS_DED_DATA_TYPE = 'PS' AND                       
            D.PS_DED_NUM = '53'     


This a query has four tables. The details of those tables

BK_PRTCPNT_MSTR
Cardinality - 1190962
Indexes
1. TRAN_TRAN_KEY (primary key and on single column)
2. SSNO_SOC_SEC_NUM (single column)

BK_PMT_TRAN
Cardinality - 24780480
Indexes
1. index 1 on the following columns (TRAN_TRAN_KEY, TRAN_CODE, SEQ_NUM, PROCESS_D, ROW_ID)
2. PAY_D
3. PROCESS_D

BK_GRP_MASTER
Cardinality - 6610
Indexes
1. CTRN_GROUP_NUMBER

BK_GRP_PS_DD
Cardinality - 159979
Indexes
1. index on the following columns (CTRN_GROUP_NUMBER, PS_DED_DATA_TYPE, PS_DED_NUM)

I have Mentioned only the indexes that I felt are relevant.

The program with this query runs for more than an hour. CPU Time was 4.94. As the tables are not very huge and this being a simple query I just wanted to know if I can improve the performance of the query.

After explain the Plan table showed that index are being used for all the tables. The match cols is 3 for BK_GRP_PS_DD and it is 1 for the other 3 tables.

I was wondering if increasing the match cols for the BK_PMT_TRAN will help. And the only column I can include in the query is PROCESS_D (the 4th column in the index of this table)

Does any one have any other suggestion??????

Thanks for your help!!

Regards,
Sinduja
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
PostPosted: Fri Oct 17, 2008 10:14 am    Post subject: Reply with quote
Give this a try. It is untested but should be close.

Code:
SELECT B.TRAN_TRAN_KEY,
       B.FSTN_FIRST_NAME,
       B.MIDN_MDDLE_NAME,
       B.LASN_LAST_NAME,
       A.PAYEE_NAME,
       A.ADDR_LINE1,
       A.ADDR_LINE2,
       A.STREET,
       A.CITY,
       A.STATE_C,
       A.ZIP_CODE,
       A.COUNTRY_C,
       C.CTRN_GROUP_NUMBER,
       C.CQHE_CIN,
       D.CMS_ACCOUNT_NUM,
       B.PPTP_PMT_TYPE_C,
       A.GROSS_AMT

  FROM BK_PRTCPNT_MSTR B
 inner join BK_PMT_TRAN A  on 
       B.TRAN_TRAN_KEY      =   A.TRAN_TRAN_KEY
   AND A.CHK_NUM            = '12345'
   AND A.PAY_D              = '2008-08-08' 
 inner join BK_GRP_MASTER C on
       B.CTRN_GROUP_NUMBER  = C.CTRN_GROUP_NUMBER
 inner join BK_GRP_PS_DD D  on
       C.CTRN_GROUP_NUMBER  = D.CTRN_GROUP_NUMBER
   AND D.PS_DED_DATA_TYPE   = 'PS'
   AND D.PS_DED_NUM         = '53'
 WHERE B.SSNO_SOC_SEC_NUM   = '123456798'
 

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Mon Oct 20, 2008 2:29 am    Post subject: Reply with quote
The two querys are the same ? Loooking to the performance, there is no difference, right?

sinduja, can you pls. provide the explain results?

Is BK_PRTCPNT_MSTR the first accessed table? How many rows match with B.SSNO_SOC_SEC_NUM = '123456798' ?

regards,
bauer
Back to top
View user's profile Send private message
sinduja
Beginner


Joined: 20 Jun 2005
Posts: 29
Topics: 14
PostPosted: Mon Oct 20, 2008 4:11 am    Post subject: Reply with quote
Bauer,

Quote:

Is BK_PRTCPNT_MSTR the first accessed table? How many rows match with B.SSNO_SOC_SEC_NUM = '123456798' ?


Yes BK_PRTCPNT_MSTR is selected first. Only one row is selected for that where condition.

The plan table after explain...

Code:

QUERYNO   QBLOCKNO  PLANNO  METHOD  TNAME                     TABNO 
10               1     1      0       BK_PRTCPNT_MSTR         2
10               1     2      1       BK_PMT_TRAN             1
10               1     3      1       BK_GRP_MASTER           3
10               1     4      1       BK_GRP_PS_DD            4

ACCESSTYPE  MATCHCOLS ACCESSNAME
I                 1             BKI1PMS2
I                 1             BKI1PTR1
I                 1             BKI1GMS1
I                 3             BKI1GPD1


DSN_statement table looks like this...

Code:

QUERYNO   STMT_TYPE  COST_CATEGORY       PROCMS       PROCSU
10              SELECT       A                 1             3


Let me know if you need any other details...

NASCAR9,

I have not yet tested your piece of code. Will let you know of the result after testing it.
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Mon Oct 20, 2008 6:46 am    Post subject: Reply with quote
Are you sure, your statistics data is valid (runstats done)?

The results in DSN_statement_table looks pretty good. I cann't believe the runtime of more than one hour and the posted explain match together ?!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group