We're not talking about optimizing a bizarre join, but a constant value.
IMNSHO the optimizer should be able to check for constants.
I've got queries like this, as a separate block of code for each
possible condition would make the code unmanageable. Imagine if
conditional statements in a given language could only have one logical
statement, the resulting nested IF statements would be spaghetti.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Sunday, September 25, 2005 7:35 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Inappropriate optimization?

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





------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links