| to tune an SQL query [message #438307] |
Fri, 08 January 2010 00:13  |
mohannksr
Messages: 28
Registered: January 2009
|
Junior Member |
|
|
Hi,
this is my query
select count(*) from
(SELECT
*
FROM TABLE_P PIC,
TABLE_bs STG
LEFT OUTER JOIN
(SELECT
T.T_NAME AS T_NAME,
CY.CY_NAME AS CY,
EX.EX_NAME AS EX_NAME,
TH.TH_NAME AS CI_NAME,
TH.M_CODE AS T_CODE,
SC.SC_NAME AS SC_NAME,
SM.SM_NR AS SCM_NO,
CEL.EQ_NR AS EQ_NO,
T.T_NR AS T_NO
FROM
TABLE_EX EX,TABLE_TH TH,TABLE_SC SC,
TABLE_CY CY,TABLE_T T,TABLE_SM SM,
TABLE_EL CEL
WHERE RTRIM(TH.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(TH.T_NR)=RTRIM(T.T_NR)
AND RTRIM(TH.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(SC.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(CY.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(SM.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(SM.SC_NR)=RTRIM(SC.SC_NR)
AND RTRIM(SM.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(CEL.SM_NR) =RTRIM(SM.SM_NR)
AND SM.RD='N'
) TB
ON RTRIM(TB.T_NAME)=RTRIM(STG.T_NAME)
AND RTRIM(TB.EX_NAME) = RTRIM(STG.EX_NAME)
AND RTRIM(TB.CI_NAME) = RTRIM(STG.CI_NAME)
AND RTRIM(TB.T_CODE)=RTRIM(STG.MCC)
AND RTRIM(TB.SC_NAME) = RTRIM(STG.SC_NAME)
AND RTRIM(TB.CY) = RTRIM(STG.CY)
WHERE RTRIM(PIC.M_P)=RTRIM(STG.M_N)
AND UPPER(RTRIM(STG.DS))='NEW'
AND TB.T_NAME IS NOT NULL);
The query is taking 21 sec.
After removing the TABLE_P table the performance has to be increased but actually it decreased.the query is taking 2 minutes.
select count(*) from
(SELECT
*
FROM --TABLE_P PIC,
TABLE_bs STG
LEFT OUTER JOIN
(SELECT
T.T_NAME AS T_NAME,
CY.CY_NAME AS CY,
EX.EX_NAME AS EX_NAME,
TH.TH_NAME AS CI_NAME,
TH.M_CODE AS T_CODE,
SC.SC_NAME AS SC_NAME,
SM.SM_NR AS SCM_NO,
CEL.EQ_NR AS EQ_NO,
T.T_NR AS T_NO
FROM
TABLE_EX EX,TABLE_TH TH,TABLE_SC SC,
TABLE_CY CY,TABLE_T T,TABLE_SM SM,
TABLE_EL CEL
WHERE RTRIM(TH.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(TH.T_NR)=RTRIM(T.T_NR)
AND RTRIM(TH.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(SC.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(CY.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(SM.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(SM.SC_NR)=RTRIM(SC.SC_NR)
AND RTRIM(SM.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(CEL.SM_NR) =RTRIM(SM.SM_NR)
AND SM.RD='N'
) TB
ON RTRIM(TB.T_NAME)=RTRIM(STG.T_NAME)
AND RTRIM(TB.EX_NAME) = RTRIM(STG.EX_NAME)
AND RTRIM(TB.CI_NAME) = RTRIM(STG.CI_NAME)
AND RTRIM(TB.T_CODE)=RTRIM(STG.MCC)
AND RTRIM(TB.SC_NAME) = RTRIM(STG.SC_NAME)
AND RTRIM(TB.CY) = RTRIM(STG.CY)
WHERE /*RTRIM(PIC.M_P)=RTRIM(STG.M_N)
AND */UPPER(RTRIM(STG.DS))='NEW'
AND TB.T_NAME IS NOT NULL);
i have attached the execution plan for both the queries..
could any one help me to know the reason behing this..?
-
Attachment: BOOKING.LOG
(Size: 12.86KB, Downloaded 1190 times)
|
|
|
|
|
|
| Re: to tune an SQL query [message #438313 is a reply to message #438311] |
Fri, 08 January 2010 01:09  |
mohannksr
Messages: 28
Registered: January 2009
|
Junior Member |
|
|
hi rahul,
thanks for your suggestion...
but the question is why the performance decreased after
removing the table TABLE_P from the query..?
could any one help me by reviewing the execution plan..?
|
|
|
|
| Re: to tune an SQL query [message #438319 is a reply to message #438313] |
Fri, 08 January 2010 01:36 |
 |
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
mohannksr wrote on Fri, 08 January 2010 01:09
....
....
could any one help me by reviewing the execution plan..?
Post execution plan of this query.( not attached one )
from Attached execution Plan
Quote:
TABLE ACCESS (FULL) OF 'TABLE_P' (Cost=68 Card=18715 Bytes=56145)
its full table access means index is not being used.
-Rahul
[Updated on: Fri, 08 January 2010 01:39] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
