| View previous topic :: View next topic |
| Author |
Message |
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
 Posted: Mon Nov 22, 2010 1:43 pm Post subject: Optimize of the Query |
 |
|
Helo,
Below is the query that Iam trying to Optimize:
| Code: |
SELECT
COUNT(DISTINCT E.SMT_NR)
, COUNT(DISTINCT F.SMT_NR)
, ((COUNT(DISTINCT E.SMT_NR) +
COUNT(DISTINCT F.SMT_NR))*100)/COUNT(DISTINCT A.SMT_NR)
FROM TXPSHPT A
INNER JOIN TXPDCKG B ON A.SMT_NR = B.SMT_NR
INNER JOIN TOPLOCM C ON B.CNY_CD = C.CNY_CD AND
B.SLC_NR = C.SLC_NR
LEFT OUTER JOIN D472ORG.TXPSHCR D ON A.SMT_NR = D.SMT_NR
LEFT OUTER JOIN D472ORG.TXPSHPT E ON A.SMT_NR = E.SMT_NR
AND D.ASY_SVC_TYP_CD = 'PL0'
LEFT OUTER JOIN TXPSHPT F ON A.SMT_NR = F.SMT_NR
AND D.ASY_SVC_TYP_CD = 'PL1'
WHERE B.SRT_DT = ?
AND B.SRT_TYP_CD = ?
AND C.REG_NR = ?
AND C.DIS_NR = ?
AND B.SLC_NR = ?
GROUP BY A.SHR_AC_NR;
|
Basically the Left Outer Joins on the same table is causing lot of time delay. Can someone please help on how to optimize this query.
Thanks!! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 22, 2010 1:58 pm Post subject: |
|
|
THRIVIKRAM,
1. Learn to use Code tags
2. Post all the relevant info regarding the primary keys involved in all the tables.
3. Post a sample data from all the tables and desired output
4. Run an explain on the query and see where the time is spent
http://mvsforums.com/helpboards/viewtopic.php?t=587&highlight=explain
Last but not least you need to learn writing your queries in a such a way that it is easier to read something like this
| Code: |
SELECT COUNT(DISTINCT E.SMT_NR)
,COUNT(DISTINCT F.SMT_NR)
,((COUNT(DISTINCT E.SMT_NR) + COUNT(DISTINCT F.SMT_NR))*100) /
COUNT(DISTINCT A.SMT_NR)
FROM TXPSHPT A
INNER JOIN TXPDCKG B
ON A.SMT_NR = B.SMT_NR
INNER JOIN TOPLOCM C
ON B.CNY_CD = C.CNY_CD
AND B.SLC_NR = C.SLC_NR
LEFT OUTER JOIN D472ORG.TXPSHCR D
ON A.SMT_NR = D.SMT_NR
LEFT OUTER JOIN D472ORG.TXPSHPT E
ON A.SMT_NR = E.SMT_NR
AND D.ASY_SVC_TYP_CD = 'PL0'
LEFT OUTER JOIN TXPSHPT F
ON A.SMT_NR = F.SMT_NR
AND D.ASY_SVC_TYP_CD = 'PL1'
WHERE B.SRT_DT = ?
AND B.SRT_TYP_CD = ?
AND C.REG_NR = ?
AND C.DIS_NR = ?
AND B.SLC_NR = ?
GROUP BY A.SHR_AC_NR;
|
Kolusu |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany
|
| Posted: Wed Nov 24, 2010 3:01 am Post subject: |
|
|
And:
1) Available Index's ?
2) Cardinality of the tables ?
3) Runstats done ?
4) Bind Parameters ? |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Thu Nov 25, 2010 3:55 am Post subject: |
|
|
Suggest you ask your DBA to run an EXPLAIN for your query....
_________________
Regards,
Anuj |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
