--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 04:25 AM 25/09/2005 +0000, you wrote:
> >In pseudo code
> >
> >create procedure (param integer)
> >begin
> > for select * from table t
> > where (:param is null) or (t.field = :param)
> > suspend;
> >end
> >
> >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.

This does not work, because even if it did use the index to quickly
identify whether t.Field = :Param, it would still need to evaluate
the other side of the OR condition (:Param is NULL). Ideally,
Firebird should recognise that this will always be either True or
False depending on :Param, but it doesn't, it will evaluate it for
each record of the table.

The same sort of problem occurs with

select field
from table
where 1=0

Btw, There was a discussion on this a few weeks back, so do a search
if you want to find out more. There is obviously a trade off between
the optimisations that are performed and the length of time it takes
to perform the optimisations. You could argue that both of these are
nonesense queries, but you could argue that duplicating the query
logic is bad programming practice, take your pick.

To solve your statement, store your query inside a varchar and then
append another clause to the appropriate "where" statement if (:Param
is NULL) (in a union their may be several).

eg.

MyQuery = 'select blah from Table t'

if (:Param is not null) then
begin
MyQuery = :MyQuery || ' where t.field = :param';
end

for execute statement MyQuery into :blah do
begin
suspend;
end

Adam