Home » RDBMS Server » Performance Tuning » Performance Problem
icon5.gif  Performance Problem [message #249014] Tue, 03 July 2007 01:36 Go to next message
barakula
Messages: 5
Registered: June 2007
Junior Member
Hello all .

I have a serious performance problem ....
I have a table with 4 million rows which name is fvx478_bp_compare_53 .

a simple query of 5 rows takes over 2 minutes ....

I am trying to do the following on this table :

select distinct account_id,owner_id,item_code,rate_scheme_code,
       nvl((select sum(a.kesef) from fvx478_bp_compare_53 a
        where a.account_id=b.account_id
        and a.item_code=b.item_code
        and a.rate_scheme_code=b.rate_scheme_code
        and a.bp_number=143
        group by a.item_code),'0') kesef_old,
       nvl((select sum(c.kesef) from fvx478_bp_compare_53 c
        where c.account_id=b.account_id
        and c.item_code=b.item_code
        and c.rate_scheme_code=b.rate_scheme_code
        and c.bp_number=144
        group by c.account_id),'0') kesef_new,
        null,null,description
from fvx478_bp_compare_53 b;


because i need to see the difference on the field KESEF between the two bp_number fields in one row .

for example :

i want to see this :

account_id owner_id item_code rate_scheme_code kesef_old kesef_new null null description

1234567 6666666 9 TORO 314.3 450.6 null null firifiri


PLEASE HELP ...

Thanks,
Barak .
Re: Performance Problem [message #249020 is a reply to message #249014] Tue, 03 July 2007 01:49 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What else but a full table scan would you expect if you do not provide a where clause?
Re: Performance Problem [message #249024 is a reply to message #249020] Tue, 03 July 2007 01:54 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Obviously a Full Table scan will hit the Performance.

Sorry Frank Didn't See your reply.

[Updated on: Tue, 03 July 2007 01:54]

Report message to a moderator

Re: Performance Problem [message #249029 is a reply to message #249014] Tue, 03 July 2007 02:07 Go to previous messageGo to next message
barakula
Messages: 5
Registered: June 2007
Junior Member
Thanks for answering .

The only where clause that i can think of is :

"where b.kesef <> 0"

This does not help me at all ...
Re: Performance Problem [message #249032 is a reply to message #249029] Tue, 03 July 2007 02:09 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Try creating a unique index for the table and check.
Re: Performance Problem [message #249043 is a reply to message #249032] Tue, 03 July 2007 02:37 Go to previous messageGo to next message
barakula
Messages: 5
Registered: June 2007
Junior Member
caliguardo wrote on Tue, 03 July 2007 02:09
Try creating a unique index for the table and check.


Hi

There is an index on account_id and bp_number ....
Re: Performance Problem [message #249228 is a reply to message #249014] Tue, 03 July 2007 12:39 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hello,
your query does not seem simple to me as you join your table with itself twice on (account_id, item_code, rate_scheme_code). Composite index on these columns could help. How many rows are (in average) with the same (account_id, item_code, rate_scheme_code) values? Also it would be good to know how many rows are with bp_number = 143 (144).

Also I would reduce the number of processed rows by putting DISTINCT into inner query, like
SELECT account_id,owner_id,item_code,rate_scheme_code,
  <other columns>
FROM (SELECT DISTINCT account_id,owner_id,item_code,rate_scheme_code, description
 FROM fvx478_bp_compare_53) b;

If the inner query returns 5 rows (cardinality of your resultset), this should help.

If it will not, post explain plan and requested row counts.

[Edit: Slightly rephrased the end]

[Updated on: Tue, 03 July 2007 23:02]

Report message to a moderator

Previous Topic: How can i tune such a simple sql
Next Topic: SQL query needs tuning
Goto Forum:
  


Current Time: Thu Dec 05 12:49:21 CST 2024