Home » RDBMS Server » Performance Tuning » Urgent:-help in rewriting an SQL
Urgent:-help in rewriting an SQL [message #65568] Sun, 31 October 2004 17:59
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi All,

I am having this SQL which is taking more than 12 Hours to execute.

Can anybody help me in rewriting the SQL. Pls. Help I will be really thankful to you.

SELECT COUNT(*) FROM TCOMMUNICATION_LOG L WHERE (L.COMP_REP_ID_C IS NULL OR
EXISTS (SELECT 'X' FROM TCOMP_REP R,TACCOUNT ACCT, TORGANIZATION ORG WHERE R.COMP_REP_ID_C = L.COMP_REP_ID_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','000005', '000006') AND  ACCT.ORG_ID_C = R.ORG_ID_C AND ACCT.ORG_NME_SEQ_C = R.ORG_NME_SEQ_C
AND ORG.ORG_ID_C = ACCT.ORG_ID_C AND ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C  AND ACCT.ACCT_TYP_C = 'COMP'
AND ACCT.ACCT_STAT_C NOT IN ('POTENL','ACTWSB') AND ORG.JURIS_ID_C <> '70001' )) AND ((L.ORG_ID_C IS NULL AND
L.ORG_NME_SEQ_C IS NULL) OR EXISTS (SELECT 'X' FROM TACCOUNT ACCT, TORGANIZATION
ORG, TORGANIZATION ACCT_ORG, TCOMP_REP R WHERE ORG.ORG_ID_C = L.ORG_ID_C AND ORG.ORG_NME_SEQ_C = L.ORG_NME_SEQ_C AND
ORG.JURIS_ID_C <> '70001' AND ACCT_ORG.ORG_ID_C = ORG.ORG_ID_C AND ACCT_ORG.JURIS_ID_C <> '70001' 
AND ACCT_ORG.ORG_ID_C = ACCT.ORG_ID_C AND ACCT_ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C AND ACCT.ACCT_TYP_C = 'COMP'
AND ACCT.ACCT_STAT_C NOT IN ('POTENL','ACTWSB') AND R.ORG_ID_C = ACCT_ORG.ORG_ID_C AND R.ORG_NME_SEQ_C =
ACCT_ORG.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','000005', '000006'))) AND L.JURIS_ID_C <> '70001'

Thanks

Milind
Previous Topic: what is the configuration to create a fresh database
Next Topic: Does providing a Numeric value in Single quotes affects performance
Goto Forum:
  


Current Time: Tue Jan 19 22:45:29 CST 2021