Home » RDBMS Server » Performance Tuning » URGET:-SQL statement not performing well.
URGET:-SQL statement not performing well. [message #65486] |
Wed, 06 October 2004 05:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Milind Deshpande
Messages: 93 Registered: May 2004
|
Member |
|
|
Hi all,
I am Tuning this Sql statement which is taking more than 5 mins. to execute. The server is situated locally and there are not much sessions on it then also the sql is taking lot of time to execute.can anybody help me on this regarding rewriting the sql or if any hints are required.SQL is using Index and there are no FTS.
The sql is :-
Select
count(ACCT_ID_C)
from
TACCOUNT ACCT,
TORGANIZATION ORG,
(select COUNT(R.COMP_REP_ID_C) COMP_REP_ID_C
from
TCOMP_REP R,
TACCOUNT ACCT
where
R.ORG_ID_C = ACCT.ORG_ID_C and
R.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and
r.svc_c IN ('000020', '000021', '000056', '000356', '000057',
'000048', '000046', '008203', '000148', '008201',
'000228', '000223', '000047', '000054', '000049',
'008204', '000154', '008202', '000229', '000227',
'000053', '000560', '000160', '000161', '000006')) tmp
where
ACCT.ACCT_STAT_C not in ('POTENL', 'ACTWSB') and
ACCT.ORG_ID_C = ORG.ORG_ID_C AND
ACCT.ORG_NME_SEQ_C = ORG.ORG_NME_SEQ_C and
ACCT.ACCT_TYP_C = 'COMP' and
ORG.JURIS_ID_C NOT IN '70001' and
tmp.COMP_REP_ID_C > 0
Kindly help me on this.
Thanks in Advance
Milind
|
|
|
Re: URGENT:-SQL statement not performing well. [message #65487 is a reply to message #65486] |
Wed, 06 October 2004 06:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Instead of counting the rows in tmp and seeing if it is greater than zero, you should be able to get away with an EXISTS clause. I think the poor performance here really stems from a kind of Cartesian join, where you have the table taccount two times--both times with the alias acct--in your FROM clause and within your in-line view, when I think you only needed it once.
Try the following SQL to see if it gets you your desired result set.
----------------------------------------------------------------------
SELECT COUNT(acct.acct_id_c)
FROM taccount acct
, torganization org
WHERE acct.acct_stat_c NOT IN ('POTENL', 'ACTWSB')
AND acct.org_id_c = org.org_id_c
AND acct.org_nme_seq_c = org.org_nme_seq_c
AND acct.acct_typ_c = 'COMP'
AND org.juris_id_c NOT IN '70001'
AND EXISTS (SELECT NULL
FROM tcomp_rep r
WHERE r.comp_rep_id_c IS NOT NULL
AND r.org_id_c = acct.org_id_c
AND r.org_nme_seq_c = acct.org_nme_seq_c
AND r.svc_c IN ('000020', '000021', '000056', '000356'
, '000057', '000048', '000046', '008203'
, '000148', '008201', '000228', '000223'
, '000047', '000054', '000049', '008204'
, '000154', '008202', '000229', '000227'
, '000053', '000560', '000160', '000161'
, '000006'))
/
----------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Sat Aug 31 20:09:59 CDT 2024
|