Hi Roc!

Excepting that I'd use

where (A.SALESDATE BETWEEN :STARTDATE AND :ENDDATE)
and (A.STOREID=:THESTOREID)
and (F.FILTERID = :THEFILTERID)

rather than put it in the JOIN clause, I find your sql nice and easy to read. Though I wonder about the definition of A INDEX (RDB$PRIMARY39) and F INDEX (RDB$PRIMARY535) and how many records we are talking about. It's many years since I (in general) used composite primary keys, but if the primary key of PLUWEEK A is (STOREID) or (STOREID, SALESDATE) and the primary key of FILTERSTD F is (F.CODE1), (F.FILTERID), (F.CODE1, F.FILTERID) or (F.FILTERID, F.CODE1) then I surely wonder how big your tables are and how many records are involved in this query. Joining two tables to count the records in Fb 1.5, I need about 3 minutes to count 4.5 million rows and if you use 6 hours...

Guessing a bit: PLUWEEK has (storeid, plu, salesdate) as the primary key. That is not ideal for this query since it means that your query will only use the (storeid) part of this index (since it is first in the plan, it cannot use an index for PLU and hence not any fields later in the index - this would be easier to see if you did not use composite indexes/keys). Try creating another index (storeid, salesdate) or force a change in the plan through changing to

JOIN STDPROD s ON (s.PLU+0 = A.PLU).

HTH,
Set

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

[Non-text portions of this message have been removed]