Re: [firebird-support] Re: Request new feature - better perfomance
For such complicated updates, you should create stored procedure, or something like: execute block as declare variable :sum; begin select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3 into :sum; update rprbs s set rezerva = iif(:sum is null,0, :sum) where s.pgod = 2014 and (s.sklad = 1 or s.sklad = 10) and s.rezerva iif(:sum is null,0, :sum) end ---In firebird-support@yahoogroups.com, softsistem@... wrote : Thanks Sean, this was small example. There could be much more sofisticated and harder examples. For example subqueary with iif() statement almost always result with double select. For example this query works with no problem but it causes to select same query 4 times. update rprbs s set rezerva = iif((select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3) is null,0, (select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3) ) where s.pgod = 2014 and (s.sklad = 1 or s.sklad = 10) and s.rezerva iif((select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3) is null,0,(select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3)) and when your database dealing with big data then your query last for more then few minutes. For me this is important perfomance feature. Best regards, Djordje Radovanovic From: mailto:firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Sent: Monday, October 20, 2014 9:29 PM To: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Subject: RE: [firebird-support] Re: Request new feature - better perfomance Try this one: select x.* from ( select something, anotherthing, (select sum(thirdthing) from second b where b.something = a.something) total from a ) x where x.total 10 While all of the suggestions are appropriate for an immediate work around, the OP original point is still valid. Firebird engine should be doing a better job of analyzing queries and looking to reduce the unrequired loop/sub-queries due to duplicate sub-queries. Sean s
Re: [firebird-support] Re: Request new feature - better perfomance
Yes, after first execution I went to store procedure. for select robid,kolicina,rezerva from rprbs where pgod = :i_pgod and sklad = :i_sklad into :v_robid,:v_kolicina,:v_rbsrezerva do begin select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = :i_pgod and p.sklad = :i_sklad and p.robid = :v_robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3 into :v_rezerva; v_rezerva = iif(v_rezerva is null,0,v_rezerva); if (v_rezerva v_rbsrezerva) then update rprbs set rezerva = :v_rezerva where pgod = :i_pgod and sklad = :i_sklad and robid = :v_robid; end But problem still exists and optimization improvement could make this query more readable, shorter and of course much faster. Still vote for optimization improvement. Best regards, Djordje Radovanovic From: mailto:firebird-support@yahoogroups.com Sent: Tuesday, October 21, 2014 9:26 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Request new feature - better perfomance For such complicated updates, you should create stored procedure, or something like: execute block as declare variable :sum; begin select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3 into :sum; update rprbs s set rezerva = iif(:sum is null,0, :sum) where s.pgod = 2014 and (s.sklad = 1 or s.sklad = 10) and s.rezerva iif(:sum is null,0, :sum) end ---In firebird-support@yahoogroups.com, softsistem@... wrote : Thanks Sean, this was small example. There could be much more sofisticated and harder examples. For example subqueary with iif() statement almost always result with double select. For example this query works with no problem but it causes to select same query 4 times. update rprbs s set rezerva = iif((select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3) is null,0, (select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3) ) where s.pgod = 2014 and (s.sklad = 1 or s.sklad = 10) and s.rezerva iif((select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3) is null,0,(select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ 3)) and when your database dealing with big data then your query last for more then few minutes. For me this is important perfomance feature. Best regards, Djordje Radovanovic From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 20, 2014 9:29 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Re: Request new feature - better perfomance Try this one: select x.* from ( select something, anotherthing, (select sum(thirdthing) from second b where b.something = a.something) total from a ) x where x.total 10 While all of the suggestions are appropriate for an immediate work around, the OP original point is still valid. Firebird engine should be doing a better job of analyzing queries and looking to reduce the unrequired loop/sub-queries due to duplicate sub-queries. Sean s
[firebird-support] PSQL: Getting both aggregation and individual rows
Hi! Is there a simple way to get an aggregation of some query as well as the individual rows from within a PSQL? I.e., I have a stored procedure GEN_DATA which produces individual rows (ID, Name, DateAndTime, Value). I am processing the output of GEN_DATA in another stored procedure PROCESS_DATA: FOR SELECT id, name, dateandtime, value FROM gen_data(...) INTO ... DO ... Within this loop, I need the individual records, but I also need some aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could run GEN_DATA twice, then it would be easy (in the first run I would calculate the aggregations, in the second run the individual values), but I can't. If I could perform the processing in my application (rather than the stored procedure), it would be easy, too (I would simply traverse the result dataset of GEN_DATA twice), but again I can't do that. Would Common Table Expressions or perhaps Derived Tables help me? Thanks, Josef ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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] PSQL: Getting both aggregation and individual rows
Seems like I will have to use a temporary table for this purpose. But if there is another way, I would be interested in knowing it. Josef Hi! Is there a simple way to get an aggregation of some query as well as the individual rows from within a PSQL? I.e., I have a stored procedure GEN_DATA which produces individual rows (ID, Name, DateAndTime, Value). I am processing the output of GEN_DATA in another stored procedure PROCESS_DATA: FOR SELECT id, name, dateandtime, value FROM gen_data(...) INTO ... DO ... Within this loop, I need the individual records, but I also need some aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could run GEN_DATA twice, then it would be easy (in the first run I would calculate the aggregations, in the second run the individual values), but I can't. If I could perform the processing in my application (rather than the stored procedure), it would be easy, too (I would simply traverse the result dataset of GEN_DATA twice), but again I can't do that. Would Common Table Expressions or perhaps Derived Tables help me? Thanks, Josef ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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] How do I return an accurate COUNT(*) when a JOIN is involved?
Good Morning All, I'm back from vacation and wanted to check in to see if there was any other ideas on this issue since I sent this reply on Oct 14th. Thanks, Mike - Original Message - From: 'Softtech Support' stwiz...@att.net [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, October 14, 2014 7:24 AM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings Set, I appreciate you joining in. Note: Because STATUS_DATE is a TimeStamp I modified your example for solution A to: SELECT COUNT(DISTINCT DCD.ACCT_ID||'-'||DCD.CASE_ID||'-'||DCD.DEBT_NO) FROM DEBTOR_CASE_DEBT DCD JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE || ' 00:00:00' AND :V_END_DATE || ' 23:59:59' AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID Solution A returned accurate results but was painfully slow. 09/01/2014 thru 09/30/2014 took 7.25 secs - 34 Count 01/01/2014 thru 09/30/2014 took 1 Min, 6.312 secs - 196 Count Here is the plan it used: PLAN JOIN (AC INDEX (REFCLIENT457),DCD INDEX (REFDEBT134,IX_DCD_STATUS_DATE_AND_CODE)) REFCLIENT457 is a FK to CLIENT which uses CLT_ID(Integer) for the PK REFDEBT134 is a FK to DEBT which uses ACCT_ID(Integer), DEBT_NO(SmallInt) for the PK IX_DCD_STATUS_DATE_AND_CODE is a new index I just added uses STATUS_DATE(TimeStamp) and STATUS_CODE(Char(1)) Solution B returned inaccurate results but was quick 09/01/2014 thru 09/30/2014 took 0.063 secs - 35 Count 01/01/2014 thru 09/30/2014 took 0.031 secs - 205 Count By changing solution B to the following I was able to determine it was not counting distinct records as in 167565-3-3 was listed twice for 09/01/2014 thru 09/30/2014 and similar duplicates for the YTD results. SELECT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO FROM DEBTOR_CASE_DEBT DCD WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE || ' 00:00:00' AND :V_END_DATE || ' 23:59:59' AND DCD.STATUS_CODE = 'B' AND EXISTS(SELECT * FROM ACCT_CASE AC WHERE AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID AND AC.CLT_ID = :V_CLT_ID) ORDER BY 1,2,3 Here is the plan it used: PLAN (AC INDEX (PK_CASE)) Set, you mention The only (minor) thing lacking is a reason for you not wanting CLT_ID included... I'm not sure what you meant here. CLT_ID is found only in the ACCT_CASE and CLIENT tables. DEBTOR_CASE_DEBT does not include CLT_ID soas to normalize the data, thus the join from DEBTOR_CASE_DEBT to ACCT_CASE to use CLT_ID. Am I missing something? Thanks again to both Martijn and Set for your help, Mike PS: I'll be on vacation starting today thru next Monday, so may be slow to respond. - Original Message - From: Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, October 14, 2014 2:23 AM Subject: RE: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? ACCT_CASE: Case Management table ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(1)NOT NULL What am I attempting to do? I need to know how many records are in the DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and '09/30/14' and the STATUS_CODE = 'B (Bankruptcy Filed) and is for a specific CLT_ID (thus the join to ACCT_CASE to use CLT_ID). I do not want to include the PERSON_ID when fetching a COUNT() of the record, I only need to know how many debts are in this status for the client. So only concerned with ACCT_ID, CASE_ID and DEBT_NO. So this SQL will return the correct number of records, now I just have to figure out how to return a count in one record. SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO FROM DEBTOR_CASE_DEBT DCD JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID Did I provide enough information this time? If not feel free to ask... This is close to a perfect problem description, Mike, well done! The only (minor) thing
[firebird-support] Re: firebird version vs version flamerobin to install using the apt-get command Ubuntu/Debian
Hello for ubuntu there is a ppa with 2.5.3 if you are using ubuntu 12.04 or 14.04 https://help.ubuntu.com/community/Firebird2.5 https://help.ubuntu.com/community/Firebird2.5 Also you can use firebird server 2.5.3 installed from tar.gz with fbclient 2.5.x installed by flamerobin as dependency
RE: [firebird-support] PSQL: Getting both aggregation and individual rows
Josef, Not sure if this will be efficient depending on number of rows, but this is another way without using a TEMP_TABLE. SELECT gen_data.id, gen_data.name, gen_data.dateandtime, gen_data.value, T1.min_dateandtime, T1.total_value FROM gen_data(...) INNER JOIN (SELECT name, min(dateandtime) min_dateandtime, sum(value) total_value from Gen_Data(...) group by name ) T1 on (T1.name = gen_data.name) INTO... DO... Thanks, Edward Mendez -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird- supp...@yahoogroups.com] Sent: Tuesday, October 21, 2014 4:12 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] PSQL: Getting both aggregation and individual rows Seems like I will have to use a temporary table for this purpose. But if there is another way, I would be interested in knowing it. Josef Hi! Is there a simple way to get an aggregation of some query as well as the individual rows from within a PSQL? I.e., I have a stored procedure GEN_DATA which produces individual rows (ID, Name, DateAndTime, Value). I am processing the output of GEN_DATA in another stored procedure PROCESS_DATA: FOR SELECT id, name, dateandtime, value FROM gen_data(...) INTO ... DO ... Within this loop, I need the individual records, but I also need some aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could run GEN_DATA twice, then it would be easy (in the first run I would calculate the aggregations, in the second run the individual values), but I can't. If I could perform the processing in my application (rather than the stored procedure), it would be easy, too (I would simply traverse the result dataset of GEN_DATA twice), but again I can't do that. Would Common Table Expressions or perhaps Derived Tables help me? Thanks, Josef ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
Re: [firebird-support] PSQL: Getting both aggregation and individual rows
What about FOR WITH TMP(MyDateTime, MySum) as (SELECT MIN(dateandtime), SUM(Value) FROM gen_data(...)) SELECT g.id, g.name, g.dateandtime, g.value, t.MyDateTime, tMySum FROM gen_data(...) g CROSS JOIN tmp t Of course, you may want a different join to CROSS JOIN. HTH, Set Fra: firebird-support@yahoogroups.com [firebird-support@yahoogroups.com] Sendt: 21. oktober 2014 09:39 Til: Firebird Support Emne: [firebird-support] PSQL: Getting both aggregation and individual rows Hi! Is there a simple way to get an aggregation of some query as well as the individual rows from within a PSQL? I.e., I have a stored procedure GEN_DATA which produces individual rows (ID, Name, DateAndTime, Value). I am processing the output of GEN_DATA in another stored procedure PROCESS_DATA: FOR SELECT id, name, dateandtime, value FROM gen_data(...) INTO ... DO ... Within this loop, I need the individual records, but I also need some aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could run GEN_DATA twice, then it would be easy (in the first run I would calculate the aggregations, in the second run the individual values), but I can't. If I could perform the processing in my application (rather than the stored procedure), it would be easy, too (I would simply traverse the result dataset of GEN_DATA twice), but again I can't do that. Would Common Table Expressions or perhaps Derived Tables help me? Thanks, Josef
Re: [firebird-support] PSQL: Getting both aggregation and individual rows
On Tue, 21 Oct 2014 09:39:44 +0200, Josef Kokeš j.ko...@apatykaservis.cz [firebird-support] firebird-support@yahoogroups.com wrote: Hi! Is there a simple way to get an aggregation of some query as well as the individual rows from within a PSQL? I.e., I have a stored procedure GEN_DATA which produces individual rows (ID, Name, DateAndTime, Value). I am processing the output of GEN_DATA in another stored procedure PROCESS_DATA: FOR SELECT id, name, dateandtime, value FROM gen_data(...) INTO ... DO ... Within this loop, I need the individual records, but I also need some aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could run GEN_DATA twice, then it would be easy (in the first run I would calculate the aggregations, in the second run the individual values), but I can't. If I could perform the processing in my application (rather than the stored procedure), it would be easy, too (I would simply traverse the result dataset of GEN_DATA twice), but again I can't do that. Would Common Table Expressions or perhaps Derived Tables help me? Not really an answer right now, but with Firebird 3 you would be able to apply the window functions to obtain aggregate values inside the individual rows, although I am unsure if this plays nice with the record stream of a selectable stored procedure. Mark
[firebird-support] how to have different (conditional) order by clause with same select
Hi, I have a number of SPs with several joined tables, sub-selects and case statements. The only variation is the index or order by clause used. When user requirements change, I have to ensure that all the different SPs are updated (a Pain). Any help/advice would be welcome, thanks. Ideally, if I could have something like this, it would do it, but it throws up this error: Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 24, column 25. ,. create or alter procedure my_SP1 ( index_by integer) /* the order I want */ returns ( myfield1_out char(15), myfield2_out char(15)) AS begin for select myfield1,myfield2 from mytable case when index_by=1 then order by myfield1,myfield2 when index_by=2 then order by myfield2,myfield1 end into :myfield1_out,:myfield2_out do suspend; end Simplifying things, I have: create or alter procedure my_SP1 ( index_by integer) /* the order I want */ returns ( myfield1_out char(15), myfield2_out char(15)) AS begin for select myfield1,myfield2 from mytable order by myfield1,myfield2 into :myfield1_out,:myfield2_out do suspend; end create or alter procedure my_SP1 ( index_by integer) /* the order I want */ returns ( myfield1_out char(15), myfield2_out char(15)) AS begin for select myfield1,myfield2 from mytable order by myfield2,myfield1 into :myfield1_out,:myfield2_out do suspend; end Alan J Davies Aldis
