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 ;^