Subject Re: [firebird-support] Conditional where statements
Author Steve Wiser
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.


On Thu, Nov 5, 2015 at 3:00 PM, Alan J Davies Alan.Davies@... [firebird-support] <> 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))
/*  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;
     for select  s.stock_no,
                     when s.inv_date is null then ''
                     else datetostr(s.inv_date,'%d/%m/%y')
                 floattostr(s.invoice_price,'% 12.2f'),
                 floattostr(cost_price,'% 12.2f'),
                 floattostr(s.ret_price,'% 12.2f'),
                 floattostr(s.act_price,'% 12.2f'),
                     when s.sell_date='01/01/2000' then ''
                     else datetostr(s.sell_date,'%d/%m/%y')
     from        stocks s
     join        supplier su
     on          su.acno=s.acno
     and         s.location is not distinct from
     and         s.trans_type is not distinct from
     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,

Alan J Davies




Visit 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


Yahoo Groups Links

<*> To visit your group on the web, go to:

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    (Yahoo! ID required)

<*> To change settings via email:

<*> To unsubscribe from this group, send an email to:

<*> Your use of Yahoo Groups is subject to: