Hi, first of all, I'm not speaking of differences between Fb 1.5 and Fb
2.0, I'm not yet using Fb 2.0 for anything but playing. So my answer is
more general.

Parametres aren't known at prepare time, and at prepare time the plan
for getting the records are determined. There's no way you can get
procedure 2 and 3 to use an index.

It is possible to use a solution between the ideal and the simple,
making use of some, but not all indexes. It will not help if you only
have two values that you do not know whether should be in the where
clause or not, but is more realistic to use than the ideal situation
when you get 5 or 10 such fields. Take a look at my reply to Pepak 12
January:

http://tech.groups.yahoo.com/group/firebird-support/message/83036

HTH,
Set

lele@... wrote:

> Hi all,
>
> I'm facing serious speed issues with FB2.0, where the optimizer
> follows a different path than the one used by FB1.5.
>
> I'm still trying to isolate the various cases, and while I was able to
> reimplement some procedure and get almost the same timings, there's
> one particular thing that seem to have a bad impact on the execution
> plan.
>
> These SPs accept several arguments that may be used to delimit the set
> of records to operate on. Basically, an argument may be either NULL to
> mean "don't consider it" or a particular value for a field.
>
> The attached script shows a simplicistic version of this
> behaviour. The first SP is the "ideal" one wrt the execution time, as
> it always uses the best indexes. The other two try to accomplish the
> same job, using some trick to avoid combinatorial replication of the
> same code: clearly the "ideal" way of doing quickly become impractical
> as the number of arguments grows.
>
> Both the 1.5 and the 2.0 optimizers select a NATURAL scan of the
> table. I tried hard, now and in the past, to find a workaround,
> without luck. This problem, coupled with the different execution plan
> followed by the 2.0 optimizer, is bringing the DB on its knees.
>
> As soon as I heard about 2.0.1 (and in particular about
> http://tracker.firebirdsql.org/browse/CORE-1029), I retried my
> attempts on that version as well, but nothing changed.
>
> What I'd like to know is:
>
> a) Am I doing something wrong? Is there a better/right way to
> implement such behaviour in a stored procedure?
>
> b) If not, is this a known problem, something that may be fixed in the
> future?
>
> Thanks in advance,
> bye, lele.
>
> RECREATE TABLE testbes (
> id INTEGER NOT NULL PRIMARY KEY,
> dat DATE NOT NULL,
> what INTEGER
> );
>
> CREATE INDEX testbes$dat ON testbes (dat);
>
> SET TERM ^;
>
> CREATE OR ALTER PROCEDURE proc$testbes0 (p_dat DATE, p_id INTEGER)
> RETURNS (what INTEGER) AS
> BEGIN
> IF (p_dat IS NULL AND p_id IS NULL) THEN
> -- No arguments, no filter
> FOR SELECT what
> FROM testbes tb
> INTO what DO
> SUSPEND;
> ELSE IF (p_dat IS NULL) THEN
> -- Filter only on the ID
> FOR SELECT what
> FROM testbes tb
> WHERE tb.id = :p_id
> INTO what DO
> SUSPEND;
> ELSE IF (p_id IS NULL) THEN
> -- Filter only on the date field
> FOR SELECT what
> FROM testbes tb
> WHERE tb.dat = :p_dat
> INTO what DO
> SUSPEND;
> ELSE
> -- Filter on both
> FOR SELECT what
> FROM testbes tb
> WHERE tb.id = :p_id
> AND tb.dat = :p_dat
> INTO what DO
> SUSPEND;
> END
> ^
>
> CREATE OR ALTER PROCEDURE proc$testbes1 (p_dat DATE, p_id INTEGER)
> RETURNS (what INTEGER) AS
> BEGIN
> FOR SELECT what
> FROM testbes tb
> WHERE ((:p_id IS NULL) OR tb.id = :p_id)
> AND ((:p_dat IS NULL) OR tb.dat = :p_dat)
> INTO what DO
> SUSPEND;
> END
> ^
>
> CREATE OR ALTER PROCEDURE proc$testbes2 (p_dat DATE, p_id INTEGER)
> RETURNS (what INTEGER) AS
> BEGIN
> FOR SELECT what
> FROM testbes tb
> WHERE tb.id = coalesce(:p_id, tb.id)
> AND tb.dat = coalesce(:p_dat, tb.dat)
> INTO what DO
> SUSPEND;
> END
> ^
>
> SET TERM ;^