[firebird-support] Dynamic Memory Allocation
Hi Todd, your description sounds like a problem with the concurrent connections. SuperServer works mostly sequential. So maybe there is a long taking query blocking the others. Maybe you should give a try to SuperClassic. For more information see http://www.firebirdsql.org/manual/qsg25-appx-architectures.html Regards Martin
[firebird-support] Upgrade from 2.5.2 to 2.5.4
Is a Backup and Restore Required?
[firebird-support] Dynamic Memory Allocation
I have a client that is running Windows Server 2008 R2. All that runs on this server is our application that is using Firebird Version 2.5.2. Superserver. When I look at the Task Manager it shows that there is a total of 2GB of RAM. That the system is typically using 80% of this. I thought they might need to increase the amount of RAM on the server. They tell me the server uses Dynamic Memory Allocation. That the server will use more memory if the application requires it (up to 8GB). Does Firebird know about Dynamic Memory Allocation? Do I need to do anything to get Firebird to use more RAM? Recently we have an issue where all client machines freeze periodically for 20 to 60 seconds. The application goes white and nobody can operate (usually 15 to 20 connections). No error message. After the 'freeze' everyone goes back to working normally until the next freeze. Some days this happens a couple of times. Some days it happens several times. We are not sure what might be causing this freeze. If anyone has any ideas about this issue, I would appreciate it. Todd Brasseur
Re: [firebird-support] Conditional where statements
My mail was not complete yet :-) Another solution could be building the query up with the conditions and use "EXECUTE STATEMENT " in the stored procedure But note that this has also drawbacks, the query need to be prepared every time the SP is called, but this could still win due the beter optimizing for every different call that can be reached. Not a real nice solution IMO, but something to consider. Kind Regards, Arno Brinkman ABVisie -Oorspronkelijk bericht- From: Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] Sent: Thursday, November 5, 2015 9:00 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Conditional where statements Hi, I have a SP that has been running as is for many years on various servers and the performance is very acceptable. However, I have just replaced a dual Xeon test server with - wait for it! - a Raspberry PI 2 running a customised Linux OS. It runs perfectly well and I've benchmarked a number of SPs and the difference is 0.5 - 1 second or so which is great for testing updates where there's only 2-3 users, but importantly it highlights the inefficiency of my code. The upside is that there is a huge benefit in power consumption!! This is one of my SPs that I've shown in full rather than a cut-down version, which is what I would normally do. This takes a second or less on the customer's system and 11-12 seconds on the Raspberry and the reason for that is the OR statements. If I remove them it comes down to the 0.5 seconds result. That may seem obvious to some but I was unaware of the big performance hit that was disguised by the brute force of the servers. I could, of course, have 8 different SPs - one for each variant of locsearch ( locsearch is passed as a parameter from Delphi - from a stock number to an invoice number etc.) but that means I have to ensure that a change to one is replicated to all others which is why I do it this way. (its too easy to miss one version) My question, therefore, is 'Is that what I have to do and just be careful?' or is there a more elegant but high-performance solution. create or alter procedure stocks_many_sel ( locsearch varchar(30), loclocation char(1), loctransaction char(1), fromdate date_char, uptodate date_char, locindex integer) returns ( trans_type char(1), location shop_location, stock_no stock_nmbr, mini_desc char_40, ref char_30, stone_wt stone_weight, cost_price float_as_char, ret_price float_as_char, act_price float_as_char, sell_date date_no_century, inv_date date_no_century, invoice_price float_as_char, sequence_no decimals_0, real_inv_date date_domain, real_sell_date date_domain, acno account, cust_no decimals_0, tickettype char(1)) as begin /* search conditions - location and trans_type, e.g. W C, H D any 2 combinations when entered must be matched however, if left blank will be found just by stock_no set loclocation & loctransaction to null to make the not distinct work */ if (loclocation='') then loclocation=null; if (loctransaction='') then loctransaction=null; locsearch=trim(locsearch); for select s.stock_no, case when s.inv_date is null then '' else datetostr(s.inv_date,'%d/%m/%y') end, s.ref, floattostr(s.invoice_price,'% 12.2f'), floattostr(cost_price,'% 12.2f'), floattostr(s.ret_price,'% 12.2f'), floattostr(s.act_price,'% 12.2f'), s.location,s.trans_type, case when s.sell_date='01/01/2000' then '' else datetostr(s.sell_date,'%d/%m/%y') end, s.stone_wt,s.tickettype, left(s.desc_blob,40), s.sequence_no, s.inv_date,s.sell_date,s.acno,s.cust_no fromstocks s joinsupplier su on su.acno=s.acno and s.location is not distinct from coalesce(:loclocation,s.location) and s.trans_type is not distinct from coalesce(:loctransaction,s.trans_type) and s.inv_date between :fromdate and :uptodate /* search by field passed in locindex */ where (:locindex=0/* stock no */ and s.stock_no=:locsearch) or (:locindex=1/* stock class */ and s.stkclass=:locsearch) or (:locindex=2/* ref */ and s.ref containing :locsearch) or (:locindex=3/* certificate */ and s.certificate containing :locsearch) or (:locindex=4/* inv_nmbr */ and s.inv_nmbr containing :locsearch) or (:locindex=5
Re: [firebird-support] Conditional where statements
Hi, Another solution could be building the query up with the conditions and use "EXECUTE STATEMENT " in the stored procedure Kind Regards, Arno Brinkman ABVisie -Oorspronkelijk bericht- From: Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] Sent: Thursday, November 5, 2015 9:00 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Conditional where statements Hi, I have a SP that has been running as is for many years on various servers and the performance is very acceptable. However, I have just replaced a dual Xeon test server with - wait for it! - a Raspberry PI 2 running a customised Linux OS. It runs perfectly well and I've benchmarked a number of SPs and the difference is 0.5 - 1 second or so which is great for testing updates where there's only 2-3 users, but importantly it highlights the inefficiency of my code. The upside is that there is a huge benefit in power consumption!! This is one of my SPs that I've shown in full rather than a cut-down version, which is what I would normally do. This takes a second or less on the customer's system and 11-12 seconds on the Raspberry and the reason for that is the OR statements. If I remove them it comes down to the 0.5 seconds result. That may seem obvious to some but I was unaware of the big performance hit that was disguised by the brute force of the servers. I could, of course, have 8 different SPs - one for each variant of locsearch ( locsearch is passed as a parameter from Delphi - from a stock number to an invoice number etc.) but that means I have to ensure that a change to one is replicated to all others which is why I do it this way. (its too easy to miss one version) My question, therefore, is 'Is that what I have to do and just be careful?' or is there a more elegant but high-performance solution. create or alter procedure stocks_many_sel ( locsearch varchar(30), loclocation char(1), loctransaction char(1), fromdate date_char, uptodate date_char, locindex integer) returns ( trans_type char(1), location shop_location, stock_no stock_nmbr, mini_desc char_40, ref char_30, stone_wt stone_weight, cost_price float_as_char, ret_price float_as_char, act_price float_as_char, sell_date date_no_century, inv_date date_no_century, invoice_price float_as_char, sequence_no decimals_0, real_inv_date date_domain, real_sell_date date_domain, acno account, cust_no decimals_0, tickettype char(1)) as begin /* search conditions - location and trans_type, e.g. W C, H D any 2 combinations when entered must be matched however, if left blank will be found just by stock_no set loclocation & loctransaction to null to make the not distinct work */ if (loclocation='') then loclocation=null; if (loctransaction='') then loctransaction=null; locsearch=trim(locsearch); for select s.stock_no, case when s.inv_date is null then '' else datetostr(s.inv_date,'%d/%m/%y') end, s.ref, floattostr(s.invoice_price,'% 12.2f'), floattostr(cost_price,'% 12.2f'), floattostr(s.ret_price,'% 12.2f'), floattostr(s.act_price,'% 12.2f'), s.location,s.trans_type, case when s.sell_date='01/01/2000' then '' else datetostr(s.sell_date,'%d/%m/%y') end, s.stone_wt,s.tickettype, left(s.desc_blob,40), s.sequence_no, s.inv_date,s.sell_date,s.acno,s.cust_no fromstocks s joinsupplier su on su.acno=s.acno and s.location is not distinct from coalesce(:loclocation,s.location) and s.trans_type is not distinct from coalesce(:loctransaction,s.trans_type) and s.inv_date between :fromdate and :uptodate /* search by field passed in locindex */ where (:locindex=0/* stock no */ and s.stock_no=:locsearch) or (:locindex=1/* stock class */ and s.stkclass=:locsearch) or (:locindex=2/* ref */ and s.ref containing :locsearch) or (:locindex=3/* certificate */ and s.certificate containing :locsearch) or (:locindex=4/* inv_nmbr */ and s.inv_nmbr containing :locsearch) or (:locindex=5 /* supplier */ and s.acno=:locsearch) or (:locindex=-1 /* pursuit # */ and s.pursuit_no=:locsearch) or (:locindex=6 /* description */ and
Re: [firebird-support] Conditional where statements
Hi Alan, It might not be an elegant solution, but I think you could also union the same query together 8 times and have the where clause "short circuit" if it is not supposed to execute. I would love to hear if anyone has any better ideas though, as this slowdown is something we have also seen. -steve On Thu, Nov 5, 2015 at 3:00 PM, Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] < firebird-support@yahoogroups.com> wrote: > Hi, I have a SP that has been running as is for many years on various > servers and the performance is very acceptable. However, I have just > replaced a dual Xeon test server with - wait for it! - a Raspberry PI 2 > running a customised Linux OS. It runs perfectly well and I've > benchmarked a number of SPs and the difference is 0.5 - 1 second or so > which is great for testing updates where there's only 2-3 users, but > importantly it highlights the inefficiency of my code. The upside is > that there is a huge benefit in power consumption!! > > This is one of my SPs that I've shown in full rather than a cut-down > version, which is what I would normally do. > This takes a second or less on the customer's system and 11-12 seconds > on the Raspberry and the reason for that is the OR statements. If I > remove them it comes down to the 0.5 seconds result. That may seem > obvious to some but I was unaware of the big performance hit that was > disguised by the brute force of the servers. > I could, of course, have 8 different SPs - one for each variant of > locsearch ( locsearch is passed as a parameter from Delphi - from a > stock number to an invoice number etc.) but that means I have to ensure > that a change to one is replicated to all others which is why I do it > this way. (its too easy to miss one version) > > My question, therefore, is 'Is that what I have to do and just be > careful?' or is there a more elegant but high-performance solution. > > create or alter procedure stocks_many_sel ( > locsearch varchar(30), > loclocation char(1), > loctransaction char(1), > fromdate date_char, > uptodate date_char, > locindex integer) > returns ( > trans_type char(1), > location shop_location, > stock_no stock_nmbr, > mini_desc char_40, > ref char_30, > stone_wt stone_weight, > cost_price float_as_char, > ret_price float_as_char, > act_price float_as_char, > sell_date date_no_century, > inv_date date_no_century, > invoice_price float_as_char, > sequence_no decimals_0, > real_inv_date date_domain, > real_sell_date date_domain, > acno account, > cust_no decimals_0, > tickettype char(1)) > as > begin > /* search conditions - location and trans_type, e.g. W C, H D > any 2 combinations when entered must be matched > however, if left blank will be found just by stock_no > set loclocation & loctransaction to null to make the not distinct work > */ > if (loclocation='') then loclocation=null; > if (loctransaction='') then loctransaction=null; > locsearch=trim(locsearch); > for select s.stock_no, > case > when s.inv_date is null then '' > else datetostr(s.inv_date,'%d/%m/%y') > end, > s.ref, > floattostr(s.invoice_price,'% 12.2f'), > floattostr(cost_price,'% 12.2f'), > floattostr(s.ret_price,'% 12.2f'), > floattostr(s.act_price,'% 12.2f'), > s.location,s.trans_type, > case > when s.sell_date='01/01/2000' then '' > else datetostr(s.sell_date,'%d/%m/%y') > end, > s.stone_wt,s.tickettype, > left(s.desc_blob,40), > s.sequence_no, > s.inv_date,s.sell_date,s.acno,s.cust_no > fromstocks s > joinsupplier su > on su.acno=s.acno > and s.location is not distinct from > coalesce(:loclocation,s.location) > and s.trans_type is not distinct from > coalesce(:loctransaction,s.trans_type) > and s.inv_date between :fromdate and :uptodate > /* search by field passed in locindex */ > where (:locindex=0/* stock no */ > and s.stock_no=:locsearch) > or (:locindex=1/* stock class */ > and s.stkclass=:locsearch) > or (:locindex=2/* ref */ > and s.ref containing :locsearch) > or (:locindex=3/* certificate */ > and s.certificate containing :locsearch) > or (:locindex=4/* inv_nmbr */ > and s.inv_nmbr containing :locsearch) > or (:locindex=5 /* supplier */ > and s.
[firebird-support] Conditional where statements
Hi, I have a SP that has been running as is for many years on various servers and the performance is very acceptable. However, I have just replaced a dual Xeon test server with - wait for it! - a Raspberry PI 2 running a customised Linux OS. It runs perfectly well and I've benchmarked a number of SPs and the difference is 0.5 - 1 second or so which is great for testing updates where there's only 2-3 users, but importantly it highlights the inefficiency of my code. The upside is that there is a huge benefit in power consumption!! This is one of my SPs that I've shown in full rather than a cut-down version, which is what I would normally do. This takes a second or less on the customer's system and 11-12 seconds on the Raspberry and the reason for that is the OR statements. If I remove them it comes down to the 0.5 seconds result. That may seem obvious to some but I was unaware of the big performance hit that was disguised by the brute force of the servers. I could, of course, have 8 different SPs - one for each variant of locsearch ( locsearch is passed as a parameter from Delphi - from a stock number to an invoice number etc.) but that means I have to ensure that a change to one is replicated to all others which is why I do it this way. (its too easy to miss one version) My question, therefore, is 'Is that what I have to do and just be careful?' or is there a more elegant but high-performance solution. create or alter procedure stocks_many_sel ( locsearch varchar(30), loclocation char(1), loctransaction char(1), fromdate date_char, uptodate date_char, locindex integer) returns ( trans_type char(1), location shop_location, stock_no stock_nmbr, mini_desc char_40, ref char_30, stone_wt stone_weight, cost_price float_as_char, ret_price float_as_char, act_price float_as_char, sell_date date_no_century, inv_date date_no_century, invoice_price float_as_char, sequence_no decimals_0, real_inv_date date_domain, real_sell_date date_domain, acno account, cust_no decimals_0, tickettype char(1)) as begin /* search conditions - location and trans_type, e.g. W C, H D any 2 combinations when entered must be matched however, if left blank will be found just by stock_no set loclocation & loctransaction to null to make the not distinct work */ if (loclocation='') then loclocation=null; if (loctransaction='') then loctransaction=null; locsearch=trim(locsearch); for select s.stock_no, case when s.inv_date is null then '' else datetostr(s.inv_date,'%d/%m/%y') end, s.ref, floattostr(s.invoice_price,'% 12.2f'), floattostr(cost_price,'% 12.2f'), floattostr(s.ret_price,'% 12.2f'), floattostr(s.act_price,'% 12.2f'), s.location,s.trans_type, case when s.sell_date='01/01/2000' then '' else datetostr(s.sell_date,'%d/%m/%y') end, s.stone_wt,s.tickettype, left(s.desc_blob,40), s.sequence_no, s.inv_date,s.sell_date,s.acno,s.cust_no fromstocks s joinsupplier su on su.acno=s.acno and s.location is not distinct from coalesce(:loclocation,s.location) and s.trans_type is not distinct from coalesce(:loctransaction,s.trans_type) and s.inv_date between :fromdate and :uptodate /* search by field passed in locindex */ where (:locindex=0/* stock no */ and s.stock_no=:locsearch) or (:locindex=1/* stock class */ and s.stkclass=:locsearch) or (:locindex=2/* ref */ and s.ref containing :locsearch) or (:locindex=3/* certificate */ and s.certificate containing :locsearch) or (:locindex=4/* inv_nmbr */ and s.inv_nmbr containing :locsearch) or (:locindex=5 /* supplier */ and s.acno=:locsearch) or (:locindex=-1 /* pursuit # */ and s.pursuit_no=:locsearch) or (:locindex=6 /* description */ and s.desc_blob containing :locsearch) into:stock_no, :inv_date, :ref, :invoice_price, :cost_price, :ret_price, :act_price, :location,:trans_type, :sell_date, :stone_wt,:tickettype, :mini_desc, :sequence_no,
Re: [firebird-support] FB 2.5.4 - new handling of IN SUBSELECT?
Den 05.11.2015 12:12, skrev Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]: > Hi! > > I wonder: Was there any change in FB 2.5.4 which would slow down > processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I > have been performing tests because a user of mine complained about slow > speed of my database, and found out that my query in the form of: > > SELECT COUNT(*) > FROM a_complex_view > WHERE field1=1 AND field2<>2 > AND key_field IN (SELECT key_field FROM key_field_list) > > went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. After > rewriting the last condition to EXISTS: > > SELECT COUNT(*) > FROM a_complex_view > WHERE field1=1 AND field2<>2 > AND EXISTS (SELECT key_field FROM key_field_list WHERE > key_field=a_complex_view.key_field) > > the speed returned to 2 seconds even in FB 2.5.4. But what could be the > cause? I went over all changes described in the 2.5.4 Release Notes and > couldn't find anything which would seem relevant. > > Thanks, > > Josef I generally stopped using IN shortly after I started using Firebird, version 0.9.4. Sure, the handling of IN have improved considerably since then, but knowing that it will at best be as efficient as EXISTS and at worst considerably worse (like your case or even worse), I've never seen a good reason for using IN . Nevertheless, I am surprised about the difference between 2.5.3 and 2.5.4 that you report. Your subselect is not correlated to the main select (i.e. you have no reference to a_complex_view inside your subselect), so in theory I'd expect it to be possible for the optimizer to change it to an EXISTS as part of its optimization (or isn't key_field_list a normal table?). Set
[firebird-support] Re: FB 2.5.4 - new handling of IN SUBSELECT?
05.11.2015 14:12, Josef Kokeš j.ko...@apatykaservis.cz wrote: > > I wonder: Was there any change in FB 2.5.4 which would slow down > processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I > have been performing tests because a user of mine complained about slow > speed of my database, and found out that my query in the form of: > > SELECT COUNT(*) > FROM a_complex_view > WHERE field1=1 AND field2<>2 > AND key_field IN (SELECT key_field FROM key_field_list) > > went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. What is execution plan for v2.5.3 and v2.5.4? Also worth trying a v2.5.5 snapshot. Dmitry
[firebird-support] FB 2.5.4 - new handling of IN SUBSELECT?
Hi! I wonder: Was there any change in FB 2.5.4 which would slow down processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I have been performing tests because a user of mine complained about slow speed of my database, and found out that my query in the form of: SELECT COUNT(*) FROM a_complex_view WHERE field1=1 AND field2<>2 AND key_field IN (SELECT key_field FROM key_field_list) went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. After rewriting the last condition to EXISTS: SELECT COUNT(*) FROM a_complex_view WHERE field1=1 AND field2<>2 AND EXISTS (SELECT key_field FROM key_field_list WHERE key_field=a_complex_view.key_field) the speed returned to 2 seconds even in FB 2.5.4. But what could be the cause? I went over all changes described in the 2.5.4 Release Notes and couldn't find anything which would seem relevant. Thanks, Josef
