My mail was not complete yet :-)

Another solution could be building the query up with the conditions and use
"EXECUTE STATEMENT <statement>" in the stored procedure
But note that this has also drawbacks!!!!, the query need to be prepared
every time the SP is called, but this could still win due the beter
optimizing for every different call that can be reached.
Not a real nice solution IMO, but something to consider.

Kind Regards,
Arno Brinkman
ABVisie




-----Oorspronkelijk bericht-----
From: Alan J Davies Alan.Davies@... [firebird-support]
Sent: Thursday, November 5, 2015 9:00 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Conditional where statements

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



[Non-text portions of this message have been removed]



------------------------------------
Posted by: "Arno Brinkman" <fbsupport@...>
------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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