Home » RDBMS Server » Performance Tuning » Performance Degrads
Performance Degrads [message #285079] Mon, 03 December 2007 05:58 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
The below query is taking time to execute and the records in the
tables are like in crores.....
Kindly suggests an alternate to the below query without using joins........

SELECT f.LEVEL_CODE,
f.DESCRIPTION,
f.master_code,
f.FACILITY_LEVEL_ID,
f.CHARGE,
P.ICD9_CODE,
P.CPT_CODE,
P.CPT_ICD9_ID,
FROM CPT_ICD9_PROCEDURES P, facility_level f
WHERE P.CPT_CODE(+) = f.LEVEL_CODE
AND f.FACILITY_NUMBER = :batch.facility_number
AND ACTIVE_FLAG = 'Y'
ORDER BY level_code,icd9_code;
Re: Performance Degrads [message #285083 is a reply to message #285079] Mon, 03 December 2007 06:07 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

if you had formatted your code ,then it would have been much better.By the way what do you mean by

Quote:

Kindly suggests an alternate to the below query without using joins



regards,
Re: Performance Degrads [message #285084 is a reply to message #285083] Mon, 03 December 2007 06:13 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
I have used left outer join and because of this it taking time.
So,how to avoid left outer join?
Re: Performance Degrads [message #285085 is a reply to message #285084] Mon, 03 December 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select the line and press on the "del" key.

Regards
Michel
Re: Performance Degrads [message #285086 is a reply to message #285084] Mon, 03 December 2007 06:29 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

Quote:

I have used left outer join and because of this it taking time.

how did you arrive at this conclusion?there might be other reasons for your query's slow performence.


regards,
Re: Performance Degrads [message #285087 is a reply to message #285079] Mon, 03 December 2007 06:30 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Check an index for columns CPT_CODE,FACILITY_NUMBER,ACTIVE_FLAG

Kiran.
Re: Performance Degrads [message #285088 is a reply to message #285079] Mon, 03 December 2007 06:32 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Funny Michel Laughing
Re: Performance Degrads [message #285093 is a reply to message #285085] Mon, 03 December 2007 06:47 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Removed bad comments.

[Edited by Taj]

[Updated on: Mon, 03 December 2007 07:41] by Moderator

Report message to a moderator

Re: Performance Degrads [message #285096 is a reply to message #285079] Mon, 03 December 2007 07:08 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

@Subbu,

Before you post read this: http://www.orafaq.com/forum/t/88153/0/

And also how to format the codes section.

Kiran.

[Updated on: Mon, 03 December 2007 07:09]

Report message to a moderator

Re: Performance Degrads [message #285101 is a reply to message #285084] Mon, 03 December 2007 07:43 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>I have used left outer join and because of this it taking time.
>So,how to avoid left outer join?
According your provide details Micheal answer was very clear and right.

Remove left outer join in your sql statement.


If you want our help & suggestion then please provide us more details. and Never Ever use Bad comments which I removed.


Re: Performance Degrads [message #285117 is a reply to message #285101] Mon, 03 December 2007 10:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi Mohammad,
should you really be editing someone's post in this way? I can see that editing a post to improve readability (removing IM speak, adding tags etc) can certainly be useful and valid, even removing abusive language I can see and agree with. But I'm not sure that censoring the above post is a valid use of this power. I saw the post and don't believe that it was particularly abusive, obnoxious yes, bleating yes but not abusive (although there was, what appeared to be, some foreign language text at the start of the post so I could be wrong and apologise if this is the case)
Re: Performance Degrads [message #285202 is a reply to message #285117] Mon, 03 December 2007 22:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Actually OP comments is abusive lanaguage in Hindi but written in english lanaguage. That is why I have to removed that comments
Re: Performance Degrads [message #285222 is a reply to message #285079] Tue, 04 December 2007 00:27 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member


I agree with Mohammad. Well done Mohammad.

Regards,
Kiran.
Re: Performance Degrads [message #285247 is a reply to message #285202] Tue, 04 December 2007 01:31 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Then fair play and as I said before, I apologise if this is the case.
Previous Topic: sql performance
Next Topic: Shows difference in EXPLAIN PLAN result for same query.
Goto Forum:
  


Current Time: Mon Dec 05 13:09:20 CST 2016

Total time taken to generate the page: 0.12411 seconds