Subject Re: [firebird-support] Inappropriate optimization?
Author Helen Borrie
At 04:25 AM 25/09/2005 +0000, you wrote:
>In pseudo code
>create procedure (param integer)
> for select * from table t
> where (:param is null) or (t.field = :param)
> suspend;
>The intention was to enable procedure to return all
>records if a parameter is not provided. Aditional
>conditions are posible. When parameter is provided
>we have unindexed reads of all records in a table,
>causing extreme slow execution.
>Is there a way to make this possible and fast?

Correct syntax in the search clause would make the engine use an index on
t.field, if there is one, which might make it faster, if it was a useful
index. A parameter is not a variable, it is a placeholder for a value, so
a test like "where Null = Null" is not just logically absurd, it will
prevent an index being used.

If you are going to use a null parameter as a mechanism for not applying a
search, you should code your procedure accordingly.

if (:param is null) then
for select ....
<no where clause>
for select...
where t.field = :param