[firebird-support] Re: Strange behaviour error writing data to connection - but after some time connection is back.
Awesome, thanks.
Re: [firebird-support] Optimizing this select Query
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 !
RE: [firebird-support] Optimizing this select Query
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 _
Re: [firebird-support] Optimizing this select Query
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.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Optimizing this select Query
Hm, this is just getting rid of using the index on sku - depending on it's selectivity, this might be a way, but than, the index might be of no use... SALEITEMS.SKU+0 = :vSKU AND You might check combined indexes... -- 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.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Optimizing this select Query
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.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Optimizing this select Query
Em 10/4/2014 19:42, Thomas Beckmann escreveu: Hm, this is just getting rid of using the index on sku - depending on it's selectivity, this might be a way, but than, the index might be of no use... SALEITEMS.SKU+0 = :vSKU AND You might check combined indexes... It's avoiding to use the index in this particular query, it does not mean it has no use... In a diferent query it could be a good index. And I think this index is generated by a FK constraint, and could not be removed. How he can combine the index of two distinct tables ? To the OP: If you change the query to use fixed values instead of parameters does it change anything ? I cant remember if the FB optimizer take in count the range of the between, I don't think so, but you could give it a try. ++ 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Declare Arrays in PSQL
Is it possible declare arrays for use in a stored procedure. eg declare iArray integer[3];
