Re: [firebird-support] Optimizing this select Query

[Alexandre Benson Smith]

Em 10/4/2014 19:14, Marius Labuschagne escreveu:

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 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 !