| SQL performance tuning [message #243773] |
Fri, 08 June 2007 22:50  |
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 #243782 is a reply to message #243774] |
Sat, 09 June 2007 00:16  |
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 |
rleishman
Messages: 3728
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 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 #243828 is a reply to message #243773] |
Sat, 09 June 2007 08:31 |
michael_bialik
Messages: 621
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
