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 :locse
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 (:
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,
