Hi,

Another solution could be building the query up with the conditions and use "EXECUTE STATEMENT <statement>" in the stored procedure


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]