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 <*> 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/