On Thu, Nov 5, 2015 at 3:00 PM, Alan J Davies Alan.Davies@... [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
     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-digest@yahoogroups.com
    firebird-support-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    firebird-support-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/