Oops, forgot something:

Should look like:

select
sum(i.QUANTITY),
sum(i.QUANTITY * i.COSTPRICEEX)
from (select
cast(:vStartDate as date) as vStartDate,
cast(:vEndDate as date) as vEndDate,
cast(:vSKU as bigint) as vSKU
from RDB$DATABASE) p
join SALES s on s.SALE_DATE between p.vStartDate and p.vEndDate
join SALEITEMS i on s.LINECODE = i.LINECODE and i.SKU = p.vSKU
where
s.POSTSTATUS = 'Posted' and
s.CASHCREDIT = 'Cash'

Thomas

Am 11.04.2014 00:34, schrieb Thomas Beckmann:

> Hi Marius,
>
> my first try would look like this:
>
> select
> sum(i.QUANTITY),
> sum(i.QUANTITY * i.COSTP RICEEX)
> from (select
> cast(:vStartDate as date) as vStartDate,
> cast(:vEndDate as date) as vEndDate,
> cast(:vSKU as bigint) as vSKU
> from RDB$DATABASE) p
> join SALES s
> join SALEITEMS i on s.LINECODE = i.LINECODE and i.SKU = p.vSKU
> where
> s.POSTSTATUS = 'Posted' and
> s.CASHCREDIT = 'Cash' and
> s.SALE_DATE between p.vStartDate and p.vEndDate
>
> (while I don't know the proper type of vSKU)
>
> Do you need an explanation?
>
> Thomas
>
> Am 11.04.2014 00:14, schrieb Marius Labuschagne:
>>
>>
>> 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.COSTP RICEEX)
>>
>> 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 (SA LEITEMS 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 bein g the link between the
>> two tables.
>>
>>
>>
>> Any advise much appreciated.
>>
>>
>>
>>
>>
>>
>>
>> Regards
>> Marius J. Labuschagne
>>
>>
>>
>>
>

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.