Home » SQL & PL/SQL » SQL & PL/SQL » SQL performance tuning
SQL performance tuning [message #243773] Fri, 08 June 2007 22:50 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have the following query, which is taking an awefully long time, could you help me fine tune it.

"select to_char(record_date,'YYYY-MM-DD') as \"nameMon\", "
+ " decode(?,'U','Upgrade','D','Downgrade') as \"directionMon\", "
+ " 'ALL' as \"portfolio_idMon\", avg(total_mv) as \"marketValueMon\", avg(total_sv) as \"statValueMon\", "
+ " avg(total_gv) as \"gaapValueMon\", avg(total_par) as \"parMon\", count(*) as \"naic_shiftsMon\" from naic_up_down_view outer"
+ " where ((decode(?, 'U', 1, 0) = 1 and org_naic > current_naic) or (decode(?, 'D', 1, 0) = 1 "
+ " and org_naic < current_naic)) group by record_date ";
Re: SQL performance tuning [message #243774 is a reply to message #243773] Fri, 08 June 2007 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Prior to posting you should have read the #1 STICKY post at the top of this forum
http://www.orafaq.com/forum/t/42427/74940/
& then you should have FOLLOWED the enumerated items.

Which items did you follow & which did you ignore?
NOBODY can tune any SQL statement based upon only that statement!
How well or how poorly any given SQL statement performs depends upon numerous factors external to just the statement itself.
Without contributing details which you neglected to post
You're On Your Own (YOYO)!
Re: SQL performance tuning [message #243782 is a reply to message #243774] Sat, 09 June 2007 00:16 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
Thanks for the prompt response. I am looking into the indexing and the table plan myself. The reason I didnot post those details is because in this post I am just looking for the order of different clauses within the select statement. I fully understand that it is a combination of a whole lot of things and am looking into those details on my own since it would not be possible for me to post the schema details over here, also I have tried not to use IN clauses, used decode clauses for aggregations, avoided NOT IN and HAVING clauses, tried not to do any calculations on the column names themselves.


This is however a complex query and I was assuming that somebody here might be able to advise me a little more on the order and if it needs to be reshuffled a little bit or if I was missing anything.
Re: SQL performance tuning [message #243786 is a reply to message #243782] Sat, 09 June 2007 00:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, so you're trying to simplify it to make it faster. Do you have a goal? Have you established that simplification will work?

For instance, you could try:

select record_date, avg(total_mv), avg(total_sv), avg(total_gv), avg(total_par) 
from naic_up_down_view outer
where org_naic > current_naic
group by record_date 
and
select record_date, avg(total_mv), avg(total_sv), avg(total_gv), avg(total_par) 
from naic_up_down_view outer
where org_naic < current_naic
group by record_date 

These are simplifications of the two possible queries that could result depending on the value of your bind variable. Are either of them fast enough for your satisfaction?

If not, there wasn't much point asking us without providing more information. If there is a big enough difference to satisfy you, it'd probably be worth posting performance details of the original query and the simplified queries and ask us why you cannot get the original to run as fast as the simplified ones.

Another (far more likely) possibility is that naic_up_down_view is a view with tons of complex code of its own. You are not doing yourself any favours by hiding this detail from us.

Ross Leishman

Re: SQL performance tuning [message #243789 is a reply to message #243782] Sat, 09 June 2007 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is unreadable.
Remove all the irrelavant and post a REAL query.
Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.
Quote:
I am just looking for the order of different clauses within the select statement

Good news for you, this has no importance.
Quote:
I have tried not to use IN clauses, used decode clauses for aggregations, avoided NOT IN and HAVING clauses, tried not to do any calculations on the column names themselves

If they exist, they can be useful depending on your needs.
Quote:
This is however a complex query

Does not seem to me but all the " and + prevent me from understanding it.

Regards
Michel
Re: SQL performance tuning [message #243828 is a reply to message #243773] Sat, 09 June 2007 08:31 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Your query perfoms SELECt from view we don't know anything about:
- number of tables joined
- join conditions and order
- number of rows in each table
- existing indexes and their selectivity, etc.

So, how can anybody advice you on anything?

Michael
Previous Topic: Inserting unique records
Next Topic: Group by error on ytd
Goto Forum:
  


Current Time: Fri Dec 02 20:42:36 CST 2016

Total time taken to generate the page: 0.19772 seconds