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 [email protected] <mailto:[email protected]>
ASSFINET-Logo
*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
[email protected] <mailto:[email protected]> • 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.
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
