Subject RE: [firebird-support] Performance of long GROUP BY clause
Author Rick Debay
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

Select *
(select from Atable a group by
join descriptive_table d on d.Atable_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-----
[] On Behalf Of t.s.
Sent: Tuesday, May 02, 2006 3:24 PM
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,
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,

Basically, this particular query returns the total sales grouped by

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.



Visit and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at


Yahoo! Groups Links