Hi,

Is it possible to optimize the following select query?

I make use of Firebird 2.5.2.26540 in Super Server mode.

The query looks like this:

SELECT

  Sum(SALEITEMS.QUANTITY),

  Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX)

FROM

  SALES SALES

  INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE = SALEITEMS.LINECODE

WHERE

  SALES.POSTSTATUS = 'Posted' AND

  SALES.SALE_DATE BETWEEN :vStartDate AND :vEndDate AND

  SALEITEMS.SKU = :vSKU AND

  SALES.CASHCREDIT = 'Cash'

The plan being utilized is as follow: PLAN JOIN (SALEITEMS INDEX (I_SALEITEMS_SKU), SALES INDEX (I_SALES_ULINECODE))

Would the result not be much faster if I can get this query to utilize the index on the SALES table on the SALE_DATE field (which exists and is active)?  Looking at the plan that is being utilised I get the feeling that all records with the particular SKU (:vSKU) is first selected (Detail table), and there can be millions of these, whereas only hundreds or thousands of records would exist if the plan would first get the subset of sales records based on the master table SALES, where the SALE_DATE are between 2 dates?

The SALES and SALEITEMS tables has a 1:1M relationship, a typical Master Detail relationship, with the LINECODE field being the link between the two tables.

Any advise much appreciated.

Regards
Marius J. Labuschagne