Home » SQL & PL/SQL » SQL & PL/SQL » Poor Performance of aggregate Function
icon11.gif  Poor Performance of aggregate Function [message #202423] Thu, 09 November 2006 13:09 Go to next message
toadster
Messages: 7
Registered: November 2006
Junior Member
Gurus,

I am using the following query to get some data (Query is valid).
My problem is that if I retreive simply data, the query is fast (15 sec). However, when I try to use an aggregate function SUM on one of the fields, it takes over 3 minutes before I get results (not acceptable).

Any idea of what should I do to improve execution time when using aggregate functions?? The amount field is indexed.

select SUM (e.amount)
from unitfact uf, entry e, account a
where uf.phaseid = 1763
and uf.isresidential = 1
AND to_date('08/31/2002','mm/dd/yyyy') + 1 BETWEEN uf.startdate AND futurecap(uf.enddate)
and uf.unitid = e.unitid
and e.isposted = 1
and e.ispayment = 0
and e.enddate is null
AND e.entrydate BETWEEN to_date('08/30/2002','mm/dd/yyyy') and to_date('08/31/2003','mm/dd/yyyy')
AND e.accountid = a.id
and a.accounttype = 'Rent'
and a.enddate IS NULL
order by e.amount

[Updated on: Thu, 09 November 2006 13:09]

Report message to a moderator

Re: Poor Performance of aggregate Function [message #202429 is a reply to message #202423] Thu, 09 November 2006 14:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
how many rows does the non-summed query retrieve? Does it take 15 seconds to get ALL of them, or just the first couple?
Re: Poor Performance of aggregate Function [message #202431 is a reply to message #202429] Thu, 09 November 2006 14:33 Go to previous messageGo to next message
toadster
Messages: 7
Registered: November 2006
Junior Member

It takes about 15 seconds to retrieve entire data set (13072 records) for non-summed query. But when I apply the aggregate function (SUM), it takes about 3 minutes before the sum gets computed. It could be the case that sql engine performs full table scan on the 13072 records to perform the add. I tried using index, but it didn't work. Any other ideas would be welcome.
Re: Poor Performance of aggregate Function [message #202432 is a reply to message #202429] Thu, 09 November 2006 14:34 Go to previous messageGo to next message
toadster
Messages: 7
Registered: November 2006
Junior Member

It takes about 15 seconds to retrieve entire data set (13072 records) for non-summed query. But when I apply the aggregate function (SUM), it takes about 3 minutes before the sum gets computed. It could be the case that sql engine performs full table scan on the 13072 records to perform the add. I tried using index, but it didn't work. Any other ideas would be welcome.
Re: Poor Performance of aggregate Function [message #202484 is a reply to message #202432] Fri, 10 November 2006 00:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post BOTH SQLs and their respective Explain Plans.

Ross Leishman
Re: Poor Performance of aggregate Function [message #202659 is a reply to message #202484] Fri, 10 November 2006 14:22 Go to previous messageGo to next message
toadster
Messages: 7
Registered: November 2006
Junior Member
Okay..here is the sql.

select NVL(SUM(e.amount),0)
from unitfact uf,
entry e,
account a
where uf.phaseid = 1763
and uf.isresidential = 1
AND to_date('08/31/2002','mm/dd/yyyy') + 1 BETWEEN uf.startdate AND futurecap(uf.enddate)
and uf.unitid = e.unitid
and e.isposted = 1
and e.ispayment = 0
and e.enddate is null
AND e.entrydate BETWEEN to_date('08/30/2002','mm/dd/yyyy') and to_date('08/31/2003','mm/dd/yyyy')
AND e.accountid = a.id
and a.accounttype = 'Rent'
and a.enddate IS NULL;

Here is explain plan...
----------------------

SELECT STATEMENT CHOOSECost: 2,790 Bytes: 68 Cardinality: 1
8 SORT AGGREGATE Bytes: 68 Cardinality: 1
7 HASH JOIN Cost: 2,790 Bytes: 18,020 Cardinality: 265 1 INDEX FAST FULL SCAN INDEX
AVBCODS.ACCOUNT_I_ID_ACCT_TYPE_ENDDT Cost: 2
Bytes: 572 Cardinality: 26

6 HASH JOIN Cost: 2,787 Bytes: 66,792
Cardinality: 1,452

3 TABLE ACCESS BY INDEX ROWID TABLE
AVBCODS.UNITFACT Cost: 729 Bytes: 23,966
Cardinality: 1,042

2 INDEX RANGE SCAN INDEX
AVBCODS.UNITFACT_I_PHASE_RES_DATE Cost: 6
Cardinality: 1,083
5 TABLE ACCESS BY INDEX ROWID TABLE AVBCODS.ENTRY
Cost: 2,057 Bytes: 1,234,778 Cardinality: 53,686

4 INDEX SKIP SCAN INDEX AVBCODS.ENT_COMM_I
Cost: 194 Cardinality: 10,159

Re: Poor Performance of aggregate Function [message #202666 is a reply to message #202431] Fri, 10 November 2006 15:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Out of curiosity, how do you know that it takes 15 seconds to retrieve the whole set? Did you run the statement in Pl/Sql, or did you just use a count(*)?

If it was the count(*), then your estimate for the total time could be inaccurate, as count(*) may well use a different plan to the actual query.

Also - you can lose the order by from the query - if you're getting a single aggregate row, then you don't need to worry about the order.
Re: Poor Performance of aggregate Function [message #202670 is a reply to message #202484] Fri, 10 November 2006 18:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Fri, 10 November 2006 17:30
Post BOTH SQLs and their respective Explain Plans.

Ross Leishman

Re: Poor Performance of aggregate Function [message #202690 is a reply to message #202666] Sat, 11 November 2006 00:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I agree with Littlefoot; I found 15 seconds to retrieve and display 15000 rows a bit low, considering the query had to be parsed and executed as well...
Re: Poor Performance of aggregate Function [message #202736 is a reply to message #202690] Sat, 11 November 2006 08:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It wasn't us. It was them. It was the Warriors.

*Cough* ... I believe you meant JRowbottom, not me ./fa/1704/0/
Re: Poor Performance of aggregate Function [message #202750 is a reply to message #202736] Sat, 11 November 2006 12:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, you guys all look alike.

Sorry JRowbottom!

[Edit: bloody h..., misspelled his name too]

[Updated on: Sat, 11 November 2006 12:59]

Report message to a moderator

Re: Poor Performance of aggregate Function [message #203282 is a reply to message #202666] Tue, 14 November 2006 10:23 Go to previous messageGo to next message
toadster
Messages: 7
Registered: November 2006
Junior Member
How do I know that query returns results in 15 sec? Well, If I was to take the aggregate function out i.e. just retrieve the amount column by itself based on the same where clauses, then the entire result set comes back in 15 seconds. I didn't do count as that was not required.
Re: Poor Performance of aggregate Function [message #203287 is a reply to message #203282] Tue, 14 November 2006 11:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Wild guess.
Since your name is toadster, I guess you executed the query to retrieve the complete set in TOAD.
Right?
Re: Poor Performance of aggregate Function [message #203309 is a reply to message #203287] Tue, 14 November 2006 13:16 Go to previous messageGo to next message
toadster
Messages: 7
Registered: November 2006
Junior Member
That is correct. Please let me know of any other way that I can extract the explain plan.
Re: Poor Performance of aggregate Function [message #203318 is a reply to message #203309] Tue, 14 November 2006 15:17 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In TOAD, by default, only the first n rows (approx. 100) gets fetched. This means that it is not the complete set that is retrieved in 15 seconds, but only some rows of it.
Execute the query (without sum) in sqlplus and see how long it takes to actually retrieve all rows..
Previous Topic: See the sql behind the view
Next Topic: Oracle hint?
Goto Forum:
  


Current Time: Fri Dec 09 02:02:39 CST 2016

Total time taken to generate the page: 0.10693 seconds