Hi,

Every developer encounters following problem in stored procedures/queries:

A user ask for something specific or all, especially in reports.

Most of programmers write following queries to solve the problem:
I use the * only for example, never use it in real applications !

1)
for
select *
from table_a a
where (a.pk = :value) or (:value = -1)
into ...
do begin
<do something>
end

2)
for
select *
from table_a a
where (a.pk = :value) or (:value is null)
into ...
do begin
<do something>
end

3)
if (value is null) then
begin
for
select *
from table_a a
into ...
do begin
<do something>
end
end
else begin
for
select *
from table_a a
where (a.pk = :value)
into ...
do begin
<do something>
end
end

4)
for
select *
from table_a a
where a.pk = :value
union all
from table_a a
where (:value = -1)
into ...
do begin
<do something>
end

So, what we have now is:
(1) and (2) are bad examples because the possible index on the search
field is not being used because of the 'or' operation. Normally the
report reduces the search to a natural scan.

(3) Is best of two worlds and give the fastest answers, but the
complication here is that all the work in the loop needs to be
duplicated or transferred to another stored procedure. Probably in a
complex loop with a lot of in and out variables. Not very friendly.

(4) Should be the best solution, no code duplication, BUT it seems
that FB doesn't see that the expression (:value = -1) evolves into
false when :value is different from -1 (so then second part of the
union is executed and gives the results). This means it does a lot of
work too get no records in the first part of the union if :value is
different from -1!! I'm not a big fan of interbase (the optimization
is not as smart as in FB2.0.x) but in these examples it does the
correct job for it! Using union to solve this problem give really
fast response on a IB server.

So, Martin (the optimizer master :)) can you check this anomalies?

It should be nice that the first part of the union should give no
result without doing fetches to the page-buffers when :value is
different from -1. Because in that case the where is always
evaluating to false.

Thanx for the possible replications.

Van den Wouwer Danny
Peopleware NV