--- In firebird-support@yahoogroups.com, "Stanislav Stratiev"
<sstratiev@v...> wrote:

> Hi,
>
> I have the following problem with the Plan Analyzer.
>
> let's say i have a simple procedure GETCONTACTS(:P_IMPORTID) which

returns recordset with Contacts from the CONTACTS table.

>
> the SQL query in the procedure looks like this
>

Hello Stanislav,

The problem your plan has is that P_IMPORTID is checked for null for
every record, so you pretty much force Firebird to do a table scan.
The Firebird optimiser can't (or maybe it can but it doesn't) evaluate
expressions like this.

select id
from contact
where 1 = 0

will also do a full table scan, same problem.

Firebird 1.5 allows you to dynamically build the SQL inside the Stored
procedure. Try something like this (see release notes, p20):

...
DECLARE VARIABLE sql varchar(100);
BEGIN
SQL = 'SELECT C.ID FROM CONTACTS';
IF (:P_IMPORTID IS NOT NULL) THEN
BEGIN
SQL = SQL || ' WHERE C.IMPORTID = :P_IMPORTID';
END

FOR EXECUTE STATEMENT :SQL INTO :ID
DO
BEGIN
SUSPEND;
END
END
^

Hope that helps

Adam.