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 [email protected] [firebird-support] < [email protected]> 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 > from stocks s > join supplier 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, > :real_inv_date,:real_sell_date,:acno,:cust_no > do > suspend; > end > > > > Alan J Davies > Aldis > > > ------------------------------------ > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > 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 > > > >
