|Subject||Optimizing this select Query|
Is it possible to optimize the following select query?
I make use of Firebird 184.108.40.206540 in Super Server mode.
The query looks like this:
Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX)
INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE = SALEITEMS.LINECODE
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.
Marius J. Labuschagne