Posted: Fri Dec 03, 2010 10:11 am Post subject: Optimization question - Multiple acces of the same table
Hi,
Iam trying to optimize this query:
Query:
Code:
SELECT DISTINCT A.SMT_DTN_CNY_CD DTNCNY
,A.DTN_SLC_NR DTNSLIC
,A.SVC_TYP_CD SVCTYPE
,COUNT(B.SMT_NR) SMTLETTER
,COUNT(C.SMT_NR) SMTDOC
,COUNT(D.SMT_NR) SMTNONDOC
,COUNT(A.SMT_NR) SMTTOTAL
FROM TABLEA A
LEFT OUTER JOIN TABLEA B ON A.SMT_NR = B.SMT_NR
AND B.SMT_TYP_CD = '01'
LEFT OUTER JOIN TABLEA C ON A.SMT_NR = C.SMT_NR
AND C.SMT_TYP_CD = '02'
LEFT OUTER JOIN TABLEA D ON A.SMT_NR = D.SMT_NR
AND D.SMT_TYP_CD = '03'
WHERE A.USR_NR = 'APP1BWT'
AND A.ORG_SRT_DT = '2010-11-08'
AND A.ORG_SRT_TYP_CD LIKE 'T%'
AND A.ORG_SLC_NR ='01029'
GROUP BY A.SMT_DTN_CNY_CD, A.DTN_SLC_NR, A.SVC_TYP_CD
What is it trying to do?
SMT_TYP_CD field indicates if the Shipment is of type Letter or Document or Non Document. This query is embedded in a report program that will be executed thousands of time in a day. The TABLEA contains approx 12M rows. I think scanning this table three times is causing the delay. Is there any way we can access the table once to get all the SMT_TYP_CD
FROM TABLEA A
,(SELECT SMT_NR SMT_NR
,CHAR('B') B_IND
,CHAR(' ') C_IND
,CHAR(' ') D_IND
FROM TABLEB
WHERE SMT_TYP_CD = '01'
UNION ALL
SELECT SMT_NR SMT_NR
,CHAR(' ') B_IND
,CHAR('C') C_IND
,CHAR(' ') D_IND
FROM TABLEC
WHERE SMT_TYP_CD = '02'
UNION ALL
SELECT SMT_NR SMT_NR
,CHAR(' ') B_IND
,CHAR(' ') C_IND
,CHAR('D') D_IND
FROM TABLED
WHERE SMT_TYP_CD = '03') Z
WHERE A.SMT_NR = Z.SMT_NR
AND A.ORG_SRT_DT = '2010-11-08'
AND A.ORG_SLC_NR ='01029'
AND A.ORG_SRT_TYP_CD LIKE 'T%'
GROUP BY A.SMT_DTN_CNY_CD, A.DTN_SLC_NR, A.SVC_TYP_CD
;
get all the 3 tables as a single table and then use that result table to join
1) There is only one table involved here. TABLEA
2) If I use the approach you suggested, I will be selecting the same table four times. This table has 12M rows in it.
Also, the counts doesnt match. Here is the O/P of the suggested query:
+-------------------------------------------------------------------------------
| DTNCNY | DTNSLIC | SVCTYPE | SMTLETTER | SMTDOC | SMTNONDOC
+-------------------------------------------------------------------------------
| AU | 08691 | 21 | 1 | 1 | 1
| AU | 08695 | 05 | 1 | 1 | 1
| AU | 08695 | 28 | 1 | 1 | 1
| AU | 09639 | 01 | 1 | 1 | 1
| AU | 09639 | 05 | 1 | 1 | 1
+------------------------------------------------------------------------------
-----------------+
| SMTTOTAL |
-----------------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
-----------------+
Basically, the query is trying to get the Total counts, Letter Counts, Doc Counts, Non Doc counts for a particular Country, Slic and Service type combination.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Fri Dec 03, 2010 2:52 pm Post subject:
THRIVIKRAM wrote:
1) There is only one table involved here. TABLEA
Thanks!!
THRIVIKRAM,
Ok I am now confused. If it is just 1 table why do you have to use an LEFT OUTER joins? Why can't you do it with just a case statement? All you wanted is a count. Try this untested sql.
Code:
SELECT DISTINCT SMT_DTN_CNY_CD DTNCNY
,DTN_SLC_NR DTNSLIC
,SVC_TYP_CD SVCTYPE
,SUM(CASE WHEN SMT_TYP_CD = '01'
THEN INT(1) ELSE INT(0) END) SMTLETTER
,SUM(CASE WHEN SMT_TYP_CD = '02'
THEN INT(1) ELSE INT(0) END) SMTDOC
,SUM(CASE WHEN SMT_TYP_CD = '03'
THEN INT(1) ELSE INT(0) END) SMTNONDOC
,COUNT(SMT_NR) SMTTOTAL
FROM TABLE
WHERE ORG_SRT_DT = '2010-11-08'
AND ORG_SLC_NR ='01029'
AND ORG_SRT_TYP_CD LIKE 'T%'
AND SMT_TYP_CD IN ('01','02','03')
GROUP BY SMT_DTN_CNY_CD
,DTN_SLC_NR
,SVC_TYP_CD
;
In my initial query, TABLEA A in one region and TABLEA B,C,D are in other region. Initial query has a different names for TABLEA B,C,D which are Data Proped from TABLEA A. (Sorry, If Iam confusing here) I dont want my question to be too complex and so gave that all my tables correspond to a single table. In this thought process, this SUM function skipped my mind.
Iam going to use your SQL to suit my needs. Thanks once again.
Not sure if it would have too much impact, but I think you can remove the DISTINCT clause since you are grouping counts by A.SMT_DTN_CNY_CD,A.DTN_SLC_NR,A.SVC_TYP_CD.
You would always be assured of only getting 1 row for that combination of values with the counts. Distinct causes a sort to be performed, but removing it in this case probably doesn't matter as the GROUP BY would more than likely cause a sort to be performed anyway. Still, the DISTINCT is not needed and can be taken out.
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
