Are you planning on using FB 2? If so you could select the core data
using your original query, and then join that select to the descriptive
data:

Select *
From
(select a.id from Atable a group by a.id)
join descriptive_table d on d.Atable_id = a.id

I'd make the main query a view if it were useful elsewhere (although
views are hard to maintin in firebird) and then this kind of join could
be done in FB 1.5 as well.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of t.s.
Sent: Tuesday, May 02, 2006 3:24 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Performance of long GROUP BY clause

Hello everyone,

I'm building several OLAP-type queries like this (for example) :
...
select m.trans_date, c.customer_code, d.product_code,
m.sales_code,
sum(qty), avg(unit_price), sum(d.qty * d.unit_price)
from sales_master m
join sales_detail d on (d.sales_master_id = m.sales_master_id)
join product p on (p.product_id = d.product_id)
join customer c on (c.customer_id = m.customer_id)
where (m.sales_date between :begin_date and :end_date)
group by m.trans_date, c.customer_code, d.product_code,
m.sales_code
...

Basically, this particular query returns the total sales grouped by
TRANS_DATE, CUSTOMER_CODE, PRODUCT_CODE, and SALES_CODE.

But to make the query more 'useful' i'd have to add more columns to the
SELECT clause, for example, i'd need the CUSTOMER_NAME, PRODUCT_NAME,
PRODUCT_DESCRIPTION, etc, etc. But this would mean that i'd also have to
add those fields to the GROUP BY clause.

My question is, given the original choice :
...
group by m.trans_date, c.customer_code, d.product_code, m.sales_code
...
vs. the expanded GROUP BY clause :
...
group by m.trans_date, c.customer_code, c.customer_name, c.address,
d.product_code, d.product_name, d.product_description,
m.sales_code, m.reference, etc, etc
...
Will the long GROUP BY clause will be a problem performance-wise?
Thank you very much in advance.

Regards,
t.s.













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

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