heroes3lover wrote:

> So many nice guys here,
> The SQL is very simple:
>
> SELECT s.PLU, s.DESCRIPTION,
> CAST( 'N' AS VARCHAR(1) ) AS ISLINKED,
> CAST( '-1' AS VARCHAR(10) ) AS STOREID,
> SUM(A.QUANTITYSOLD*s.PACKQTY) AS QUANTITY,
> SUM(B.QUANTITYSOLD*s.PACKQTY) AS PROMQUANTITY,
> SUM(D.QUANTITYSOLD*s.PACKQTY) AS PURCHQUANTITY,
> SUM(A.SALESEXGST) AS SALES,
> SUM(A.SALESEXGST-A.COSTEXGST) AS SALESPROFIT,
> SUM(B.SALESEXGST) AS PROMSALES,
> SUM(B.SALESEXGST-B.COSTEXGST) AS PROMPROFIT,
> SUM(D.COSTEXGST) AS PURCHSALES
> FROM PLUWEEK A
> JOIN STDPROD s ON (s.PLU = A.PLU )
> and( A.SALESDATE BETWEEN :STARTDATE AND :ENDDATE)
> and(A.STOREID=:THESTOREID)
> JOIN FILTERSTD F
> ON( S.PLU= F.CODE1 )
> AND( F.FILTERID = :THEFILTERID )
> LEFT OUTER JOIN PRMPLUWEEK b ON (A.STOREID=B.STOREID) AND
> (A.SALESDATE=B.SALESDATE) AND (A.PLU=B.PLU)
> LEFT OUTER JOIN PURPLUWEEK D ON (A.STOREID=d.STOREID) AND
> (A.SALESDATE=d.SALESDATE) AND (A.PLU=D.PLU)
> group by S.PLU, S.DESCRIPTION
>
>
> and the PLAN is:
> PLAN SORT (JOIN (JOIN (JOIN (A INDEX (RDB$PRIMARY39),F INDEX
> (RDB$PRIMARY535),S INDEX (RDB$PRIMARY152)),B INDEX (RDB$PRIMARY42)),D
> INDEX (RDB$PRIMARY45)))
>
> STDPROD keeps all the products information and PLU is primary key,
> PRMPLUWEEK and PURPLUWEEK also have primary key storeid/plu/salesdate
> maybe 50,000 products in the list.
>
> Thanks,
> Roc
>

Roc,

I just rewrote your query to be easier to me understand, I moved the
filter criteria to the where clause and let on the join clause just the
linking part (at least this is what I think I did)

SELECT
s.PLU, s.DESCRIPTION,
CAST( 'N' AS VARCHAR(1) ) AS ISLINKED,
CAST( '-1' AS VARCHAR(10) ) AS STOREID,
SUM(A.QUANTITYSOLD*s.PACKQTY) AS QUANTITY,
SUM(B.QUANTITYSOLD*s.PACKQTY) AS PROMQUANTITY,
SUM(D.QUANTITYSOLD*s.PACKQTY) AS PURCHQUANTITY,
SUM(A.SALESEXGST) AS SALES,
SUM(A.SALESEXGST-A.COSTEXGST) AS SALESPROFIT,
SUM(B.SALESEXGST) AS PROMSALES,
SUM(B.SALESEXGST-B.COSTEXGST) AS PROMPROFIT,
SUM(D.COSTEXGST) AS PURCHSALES
FROM
PLUWEEK A JOIN
STDPROD s ON (s.PLU = A.PLU ) JOIN
FILTERSTD F ON( S.PLU= F.CODE1 ) LEFT OUTER JOIN
PRMPLUWEEK b ON (A.STOREID=B.STOREID) AND (A.SALESDATE=B.SALESDATE)
AND (A.PLU=B.PLU) LEFT OUTER JOIN
PURPLUWEEK D ON (A.STOREID=d.STOREID) AND (A.SALESDATE=d.SALESDATE)
AND (A.PLU=D.PLU)
where
(A.SALESDATE BETWEEN :STARTDATE AND :ENDDATE) and
(A.STOREID=:THESTOREID) and
( F.FILTERID = :THEFILTERID )
group by
S.PLU, S.DESCRIPTION


I rarely use composed index (when I use is to enforce unique
constraints, all my PK's are simple integer fields) , so maybe my
conception is misleading, if so, someone will correct me.

You have a Filter condition on A.StoreID and A.SalesDate, but not on
A.Product, since the first table in your plan is A I suppose the index
was picked based on it's selectivity (that is optimal since it's a PK)
but it would be used just for the first segment StoreID what wouldn't
help much IMHO, FB 2.0 probably would have treated this index with the
same weight as an index on A.StoreID alone (FB 2.0 has index statiscts
for each segment) and discarded it (if there is another which provides a
better cost).

Two sugestions:
1.) Create another index on A.SalesDate
2.) Invert your PK on table A to (storeid/salesdate/plu) but this would
lead you to the same problem when you filter on product but not on
salesdate, so I think option 1 would be better.

hth

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br