Subject RE: [firebird-support] Optimizing this select Query
Author Marius Labuschagne
Post date 2014-04-10T22:32:57Z

The first thing...

Are the indices statistics up to date ?

If so...

You could try this one:

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+0 = :vSKU AND

  SALES.CASHCREDIT = 'Cash'


and see if it uses the Date index.

see you !

__,_._,__

[Marius Labuschagne]

 

Hello Alexandre,

 

Thanks for taking the time to have a look.

 

Index stats is up to date yes.

 

If I change the query to what you suggested then the following index is used: (I just changed the +0 to ||'' as SKU is a varchar field)

 

PLAN JOIN (SALES INDEX (I_SALES_SALEDATE, I_SALES_SALETYPE), SALEITEMS INDEX (I_SALEITEMS_LINECODE))

 

I am sure this will speed up my calculations significantly, because it is now eliminating fetching basically all the Detail records for many years history of a particular SKU.

 

Thanks again for the advise.

 

Regards

Marius 

 

 

_