|Subject||Re: [firebird-support] Conditional where statements|
I would love to hear if anyone has any better ideas though, as this slowdown is something we have also seen.
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.
On Thu, Nov 5, 2015 at 3:00 PM, Alan J Davies Alan.Davies@... [firebird-support] <email@example.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 (
/* 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 ''
when s.sell_date='01/01/2000' then ''
from stocks s
join supplier su
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 */
or (:locindex=1 /* stock class */
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 */
or (:locindex=-1 /* pursuit # */
or (:locindex=6 /* description */
and s.desc_blob containing :locsearch)
Alan J Davies
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:
<*> 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: