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

Report message to a moderator

Re: URGENT:-SQL statement not performing well. [message #65487 is a reply to message #65486] Wed, 06 October 2004 06:04 Go to previous messageGo to next message
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'))
/
----------------------------------------------------------------------

Report message to a moderator

Re: URGENT:-SQL statement not performing well. [message #65490 is a reply to message #65487] Wed, 06 October 2004 07:20 Go to previous message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi Art,

Thanks for your quick response.
The query is working fine now.

Thanks a lot.

Milind.

Report message to a moderator

Previous Topic: Buffer Cache Hit Ratio
Next Topic: Index Rebuld
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 24 14:43:56 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.