Thanks for your kind details and quick response. I have referred the links given by you and I am not able to see any issues with the SQL. Is this SQL is already an optimized one?
Please find below my SQL. Can you please suggest some hints on how to proceed further in tuning the SQL if I am missing something.
WHERE (U.COMPANY = 'AB' AND
U.CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW'))
AND U.CL_NBR > ''
AND C.COMPANY = U.COMPANY
AND M.CODE = U.CODE
AND M.CODE = C.CODE
AND M.UH_NBR = U.UH_NBR
AND C.UH_NBR = U.UH_NBR
AND C.DDATE >= :S_DATE
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Even if you remove DISTINCT also, an order by is there for DB2 to do a sort.
Table ASUK data can be minimized first using a inline view and then use that data to join to other two tables.
FROM
(SELECT
CL_NBR ,
CL_ACC_NBR,
UH_NBR,
COMPANY,
CODE
FROM
DB06.ASUK
WHERE
COMPANY = 'AB' AND
AND CL_NBR > ''
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS U
DB06.TBLM M,
DB06.ACH C
WHERE
C.COMPANY = U.COMPANY
AND M.CODE = U.CODE
AND M.CODE = C.CODE
AND M.UH_NBR = U.UH_NBR
AND C.UH_NBR = U.UH_NBR
AND C.DDATE >= :S_DATE
ORDER BY CL_NBR, CL_ACC_NBR ;
This will reduce the number of rows required for joining.
Please try.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Try it out ; it's not a subquery - instead of joining the entire table ASUK, i am just using only those rows which satisfies the conditions.
It is safe to filter rows and then join.
Try to EXPLAIN this query too and see what difference does it make.
I ran the query and unfortunaly there is no improvement in the run time.
When I see the ACCESSTYPE type in EXPLAIN_TABLE ( as below) it shows as 'N' for the table ASUK for other tables it shows as 'Y'. Also METHOD shows as 0 for ASUK table. Are these the culprits?
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Sorry to hear that. Please provide all info.
Of tables ASUK and ACH which table is smaller ? Making the smaller table as outer one in a join will help to reduce the number of times the inner table must be re-accessed. The conditions COMPANY and CODE can be applied to ACH too making it the outer table as selective predicates are available. These are just assumptions without knowing the indexes ; check when was RUNSTATS / REORG done on these tables last.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Many times writing the smallest amount of code results in poor performance - regardless of how well the query is written. The fastest possible 1,000,000 database accesses will still take considerable resources.
Depending on the actual processing, it is often possible to reduce processing resource requirements by 90-95%.
Many processes test perfectly with tiny amounts of data. When full volume is used for the first time in Production (which, imho, should never happen), there is an instant panic. . .
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
ashimer
Quote:
an order by is there for DB2 to do a sort.
Not necessary....
Appropriate indexing may permit DB2 to avoid sorting the data to match the ORDER BY clause. If the data is already sequenced via the index, DB2 may choose to use the index and avoid sorting the data.
Quote:
Making the smaller table as outer one in a join will help to reduce the number of times the inner table must be re-accessed.
DB2's choice of inner and outer table is a delicate trade off. More the no. of inner table rows ,longer the scan...if outer rows are more then no. of opening and closing of internal cursor on inner table are more... DB2 favours the larger table as the outer table in a nested loop join...
chinnielr
Check whether the columns indexed and used by are also clustered....this info you will not get in plan table...You will need to query DB2 catalog.
If INDEXONLY is N then tablespace data pages are also being used... so check of you have appropriate indexes defined....
You might also want to refer to columns PREFETCH,JOIN_DEGREE for more information...
Also you might need to rewrite the query,tweaking some parts...
Query optimization can be daunting...but you need to keep trying...
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Quote:
DB2's choice of inner and outer table is a delicate trade off. More the no. of inner table rows ,longer the scan...if outer rows are more then no. of opening and closing of internal cursor on inner table are more... DB2 favours the larger table as the outer table in a nested loop join...
Not necessary....
Appropriate indexing may permit DB2 to avoid sorting the data to match the ORDER BY clause. If the data is already sequenced via the index, DB2 may choose to use the index and avoid sorting the data.
Agree on a general case; but here please see columns SORTC_UNIQ and SORTC_ORDERBY of PLAN table.
Quote:
If INDEXONLY is N then tablespace data pages are also being used... so check of you have appropriate indexes defined....
Obviously DB2 has to go for data pages else all the fetched columns must be in the index - what say ?
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
Quote:
The outer table is small.
Don't let this statement(in the link) blur your conclusions of the NLJOIN....As you can see my post , I have not made a sweeping statement that NLJOIN will always use a larger table.None of these are hard and fast rules...DB2 will even use the larger table as outer if selective predicates applies to it....similarly if there is no index ...then even if the table is small it is not used as inner table.
Quote:
Obviously DB2 has to go for data pages else all the fetched columns must be in the index - what say ?
having an index defined and being used in the query is a necessary condition..... not sufficient....that is why I used the term "appropriate".
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
I was contradicting your statement "DB2 favours the larger table as the outer table in a nested loop join" - dont worry my visions are not blurred by the link; that was just provided as a documentation.
I was emphasizing on the current SQL where we have indexes , probably a healthy MATCHCOLS , ACCESSTYPE.
ASUK has a method 0 , but not performing well; why dont we change that and make ACH in place of ASUK as the outer table.
We better focus on the issue here rather than generalizing.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
None of these are "hard and fast rules" ; i am just referring to the o/p of the EXPLAIN posted above and suggesting what can be done here ; not having a discussion on DB2.
Let's leave it to the optimizer to take the final decision
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
focussuing on just some particular rules can be very dangerous unless you are aware of the general rules ... have a clear macro view only then you can go for a micro focus....which is what I was trying to share with chinnielr...so that he/she could be better informed.
An again a "healthy MATCHCOLS" does not gurantee a great performance.
Such data mask much more information than they reveal....
Obviously we can't help chinnielr further... because we neither know the data nor the # of rows nor the various DB2 subsystem parameters(Sort pool,RID pool,buffer pool....)
BTW when I ran the changed query as mentioned by ashimer it is taking more time than the original one.
I am providing some more info on the indexes for these tables.
Please pardon me if there is an issue with formatting.
I understand that all the columns under where clause have indexes defined. But still I am not sure why there is a performance issue with this query.
Can somebody help me out to resolve this issue.
Code:
Index details for TBLM
TABLE INDEX
NAME NO NAME DATATYPE NULL PK FK REFERENTAB INDEXED NAME
------------------ ------ ------------------ ----------------------- -------- --- --- ------------------ ------- ------------------
TBLM 1 CODE CHAR(3) NOT NULL YES YES TTYNUHA1
TBLM 1 CODE CHAR(3) NOT NULL YES YES TTYNUHA2
TBLM 2 UH_NBR CHAR(8) NOT NULL YES YES TTYNUHA1
TBLM 2 UH_NBR CHAR(8) NOT NULL YES YES TTYNUHA2
TBLM 3 REG_DATE DATE NOT NULL YES YES TTYNUHA1
TBLM 3 REG_DATE DATE NOT NULL YES YES TTYNUHA2
TBLM 4 TRN_SL_NBR CHAR(7) NOT NULL YES YES TTYNUHA1
TBLM 5 TRN_TYPE CHAR(2) NOT NULL YES YES TTYNUHA1
TBLM 5 TRN_TYPE CHAR(2) NOT NULL YES YES TTYNUHA2
TBLM 6 TRN_SL_NO_PRE CHAR(3) NOT NULL
TBLM 7 EFF_DATE DATE NOT NULL
TBLM 8 UNITS DECIMAL(15,4) NOT NULL
TBLM 9 DIST_NBR DECIMAL(3,0) NULL
TBLM 10 TRSFR_UH_NBR CHAR(8) NULL
TBLM 11 REG_DEREG_IND CHAR(1) NOT NULL YES TTYNUHA2
TBLM 12 PRC DECIMAL(9,4) NOT NULL
TBLM 13 REN_COM_AG CHAR(7) NULL
TBLM 14 AQU_DATE DATE NULL
TBLM 15 CERT_LOST_IND CHAR(1) NOT NULL
TBLM 16 XD_IND CHAR(1) NOT NULL
TBLM 17 UNITS_REN DECIMAL(15,4) NOT NULL
TBLM 18 REPLACE_REQ_FLAG CHAR(2) NOT NULL
TBLM 19 REPURC_CON_NO CHAR(10) NOT NULL
TBLM 20 DISP_PRC DECIMAL(9,4) NOT NULL
TBLM 21 REPLACEMENT_DATE DATE NULL
TBLM 22 SHR_CL_SW CHAR(1) NULL
TBLM 23 SCS CHAR(3) NOT NULL
Code:
index table for ACH
TABLE INDEX
NAME NO NAME DATATYPE NULL PK FK REFERENTAB INDEXED NAME
------------------ ------ ------------------ ----------------------- -------- --- --- ------------------ ------- ------------------
ACH 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNCO1
ACH 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNCO2
ACH 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNCO3
ACH 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNCO4
ACH 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNCO5
ACH 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNCO6
ACH 2 CON_NBR CHAR(7) NOT NULL YES YES TTYNCO1
ACH 2 CON_NBR CHAR(7) NOT NULL YES YES TTYNCO3
ACH 2 CON_NBR CHAR(7) NOT NULL YES YES TTYNCO4
ACH 2 CON_NBR CHAR(7) NOT NULL YES YES TTYNCO6
ACH 3 SL_RPC CHAR(1) NOT NULL YES TTYNCO2
ACH 3 SL_RPC CHAR(1) NOT NULL YES TTYNCO3
ACH 3 SL_RPC CHAR(1) NOT NULL YES TTYNCO5
ACH 3 SL_RPC CHAR(1) NOT NULL YES TTYNCO8
ACH 4 SUB_TYPE CHAR(1) NOT NULL
ACH 5 C_DATE DATE NOT NULL YES TTYNCO2
ACH 5 C_DATE DATE NOT NULL YES TTYNCO3
ACH 5 C_DATE DATE NOT NULL YES TTYNCO5
ACH 6 STATUS CHAR(1) NOT NULL YES TTYNCO2
ACH 6 STATUS CHAR(1) NOT NULL YES TTYNCO3
ACH 6 STATUS CHAR(1) NOT NULL YES TTYNCO5
ACH 25 COMPANY CHAR(2) NOT NULL YES TTYNCO2
ACH 25 COMPANY CHAR(2) NOT NULL YES TTYNCO3
ACH 25 COMPANY CHAR(2) NOT NULL YES TTYNCO4
ACH 25 COMPANY CHAR(2) NOT NULL YES TTYNCO5
ACH 25 COMPANY CHAR(2) NOT NULL YES TTYNCO6
ACH 26 AT_CODE CHAR(7) NULL YES TTYNCO2
ACH 26 AT_CODE CHAR(7) NULL YES TTYNCO3
ACH 27 CAMP_CODE CHAR(2) NULL YES TTYNCO5
ACH 28 UH_NBR CHAR(8) NULL YES TTYNCO4
ACH 28 UH_NBR CHAR(8) NULL YES TTYNCO6
ACH 29 INFO_CODE_1 CHAR(10) NULL
ACH 30 INFO_CODE_2 CHAR(10) NULL
ACH 31 INV_TYPE CHAR(2) NULL YES TTYNCO8
ACH 32 EXT_CON CHAR(10) NULL
ACH 44 EFF_DATE_VERSION_1 DATE NOT NULL YES TTYNCO8
ACH 51 DSTATION CHAR(2) NULL YES TTYNCO7
ACH 52 DNUMBER CHAR(6) NULL YES TTYNCO7
Code:
index tabole for ASUK
TABLE INDEX
NAME NO NAME DATATYPE NULL PK FK REFERENTAB INDEXED NAME
------------------ ------ ------------------ ----------------------- -------- --- --- ------------------ ------- ------------------
ASUK 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNFBC1
ASUK 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNFBC2
ASUK 1 CODE CHAR(3) NOT NULL YES YES TRUST YES TTYNFBC3
ASUK 2 UH_NBR CHAR(8) NOT NULL YES YES TTYNFBC1
ASUK 2 UH_NBR CHAR(8) NOT NULL YES YES TTYNFBC2
ASUK 2 UH_NBR CHAR(8) NOT NULL YES YES TTYNFBC3
ASUK 3 COMPANY CHAR(2) NOT NULL YES CLIENT_ACC YES TTYNFBC2
ASUK 3 COMPANY CHAR(2) NOT NULL YES CLIENT_ACC YES TTYNFBC3
ASUK 4 UNITS DECIMAL(15,4) NOT NULL YES TTYNFBC2
ASUK 4 UNITS DECIMAL(15,4) NOT NULL YES TTYNFBC3
ASUK 5 MTYPE CHAR(1) NOT NULL
ASUK 6 PMANDATE CHAR(1) NOT NULL
ASUK 7 CL_ACC_NBR DECIMAL(9,0) NOT NULL YES CLIENT_ACC YES TTYNFBC2
ASUK 7 CL_ACC_NBR DECIMAL(9,0) NOT NULL YES CLIENT_ACC YES TTYNFBC3
ASUK 8 CL_NBR CHAR(8) NOT NULL YES CLIENT_ACC YES TTYNFBC2
ASUK 8 CL_NBR CHAR(8) NOT NULL YES CLIENT_ACC YES TTYNFBC3
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Which one did you try ? post the details (definition) of the index which are being used as shown in the EXPLAIN ; also a rough estimate of data in each table - how many ?
No of records in ASUK – 6236353
No of records in TBLM – 61351411
No of records in ACH - 28445032
Please find below the index details.
Code:
INDEXNAME UNIQUERULE COLCOUNT CLUSTERING CLUSTERED PGSIZE SPACE CLUSTERRATIO
UKI1UMV1 P 5 Y Y 4096 1844640 97
UKI1UMV2 D 5 N N 4096 990000 92
UKI1CON1 P 2 N N 4096 0 0
UKI1CON2 D 6 Y Y 4096 0 0
UKI1CON3 D 7 N N 4096 0 0
UKI1CON4 U 4 N N 4096 0 0
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
You are dealing with tables with data in millions ... there will be performance issues ... asked for definitions of index which are used as per your explain ... try the below query too .. use the conditions on each table based on the index defined on them .. if not remove the conditions where you can improve performance.
Code:
SELECT COLS FROM
(SELECT COLS FROM ASUK
WHERE COMPANY = 'AB'
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS U
,
(SELECT COLS FROM ACH
WHERE COMPANY = 'AB'
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')
AND DDATE >= :S_DATE ) AS C
,(SELECT COLS FROM TBLM WHERE CODE
IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS M
WHERE
M.CODE = U.CODE
AND M.UH_NBR = U.UH_NBR
AND C.UH_NBR = U.UH_NBR
Select only those columns which are required, the idea is to reduce the number of rows required for the final join.
Please find below the additional details of PLAN_TABLE report and index details as you requested.
Code:
QUERYNO QBLOCKNO PLANNO METHOD TNAME
1 1 1 0 ASUK
1 1 2 1 TBLM
1 1 3 2 ACH
1 1 4 3 ----
ACCESSTYPE MATCHCOLS INDEXONLY SORTC_UNIQ
N 2 Y N
I 3 N N
I 2 Y N
- 0 N Y
SORTC_ORDERBY TSLOCKMODE PREFETCH MIXOPSEQ
N IS -- 0
N IS -- 0
N IS -- 0
Y -- -- 0
CORRELATION_NAME PAGE_RANGE QBLOCK_TYPE ACCESSNAME
U Y SELECT TTYNFBC3
C - SELECT TTYNCO4
M - SELECT TTYNUHA1
- - SELECT -------
Code:
INDEXNAME UNIQUERULE COLCOUNT CLUSTERING CLUSTERED PGSIZE SPACE CLUSTERRATIO FIRSTKEYCARD FULLKEYCARD
TTYNUHA1 P 5 Y Y 4096 1844640 97 2217 36525187
TTYNCO4 U 4 N N 4096 0 0 -1 -1
TTYNFBC3 U 6 N N 4096 216000 94 43
FROM ACH
WHERE COMPANY = 'AB'
AND CODE IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')
AND DDATE >= :S_DATE ) AS C
,(SELECT 1 FROM TBLM WHERE CODE
IN ('RRR','TRE','PRP','CON',
'RRT','RRU','RRV','RRW')) AS M
WHERE
M.CODE = U.CODE
AND M.UH_NBR = U.UH_NBR
AND C.UH_NBR = U.UH_NBR
Can you please let me know whether the above query is qhat you are referring to? Also we do not select any columns from TBLM table so I have selected 1 is it fine?
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Did you atleast try the query you have posted above ? If you are selecting 1 how do you plan to join M with U ?
No more spoon feeding; try try and try until you find a good improved SQL.
If you face any difficulties ppl are here to help ; you should be doing your part too.
