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: 12378 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 |
|
 |
|
|