Hi,

Actually I meant that the COUNT on any table takes a huge amount of
time. It is further compounded by the GROUP BY clause.

For the following query it takes 8 seconds to execute
select USER_KEY, COUNT(1) from FUND_SELECTION
GROUP BY USER_KEY

The total count in the table is 49,175 records only.

When the table size grows to millions, performance deteriorates further.
Please help.

Regards,
Hemant.
Project Leader - Investment Banking Group
PrimeSourcing(tm) "Add Value Reduce Risk"
i-flex solutions ltd., Mumbai
Tel Office: +91-22-5642-7144
e-mail: hemant.sapre@...

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Monday, October 10, 2005 11:39 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] GROUP BY clause is very slow

At 09:54 AM 10/10/2005 +0530, you wrote:

>Hi,
>
>I am facing problems with the GROUP BY clause. It takes a lot of time

to

>return with the values.
>
>I am trying the following:
>
>SELECT COUNT(1) FROM VW_INSERT_RIP WHERE USER_KEY = '8888'
>
>This query takes 214 seconds to return the value - 785,000 records from
>60,000,000 records.

This query doesn't have a group by clause. All it does is to limp
through
the entire record set by your view, one row at a time, from beginning to

end, deciding whether to add the row to its count, or not, depending on
the
value it finds for USER_KEY. In a grouped query, it will count the
records
*after* grouping.

Try this instead:

select user_key, count(*) from vw_insert
where user_key = '8888'
group by 1

>The view internally joins 3 tables. It has index on the FUND_PRICE

table

>(with 60,000,000 odd records) but not on the other lighter tables.

Indexes can be crucial to joins and searches. If there is no index on
user_key (or if the join specifications are poor) then nothing is fast.
So
214 seconds to do a bad join and then walk through 60 million records
sounds pretty quick to me (even without knowing what you are concealing
in
the view. :-))

Would you please remove your company's Disclaimer blurb from list
mails. It is totally pointless in list mail, besides wasting bandwidth
and
filling up everyone's inbox with extra garbage.

^ heLen





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links











DISCLAIMER:
This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.