Hi,

I have 3 tables

1. products (rows 28844)
------------------------
company_id smallint
id integer
product_nm varchar(50)
(pk - company_id, id)
(other columns.....)

2. sale (rows 61608)
--------------------
company_id smallint
id integer
entry_date date
(other columns.....)
(pk - company_id, id)
(index - company_id, entry_date)

3. sale_item (rows 292475)
--------------------------
company_id smallint
parent_id integer
id integer
product_id integer
(other columns.....)
(pk - company_id, parent_id, id)
(fk - company_id, parent_id to sale (company_id, id)
)
(fk - company_id, product_id to products
(company_id, id) )

Now a query like this

SELECT P.product_nm, S.bill_no, S.entry_date,
SD.quantity,
SUM( ( SD.quantity * SD.rate ) -
SD.discount_a ), 'S'
FROM sale_details SD JOIN sale S ON (
SD.company_id = S.company_id )
AND (
SD.parent_id = S.id )
JOIN products P ON (
SD.company_id = P.company_id )
AND (
SD.product_id = P.id )
WHERE ( ( S.company_id = 1 ) AND ( S.entry_date

>= '1.4.04' )

AND ( S.entry_date <= '30.4.04' ) )
GROUP BY S.entry_date, S.bill_no, P.product_nm,
SD.quantity

takes around 42 seconds to execute and using the
plan
PLAN SORT (JOIN (SD INDEX
(FK_SALE_DETAILS_TO_SALE),S INDEX (PK_SALE),P INDEX
(PK_PRODUCTS))).

Is there any other way to speed up this query ? If
an index is required,
on what colunm and which table ?

I am on W2k Server (standalone), celeron 333 with
256 MB Ram and Firebird 1.5.

TIA.

Sudheer Palaprambil





___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com