Hi Pepak!

There's nothing wrong with mixing approaches. I agree that two or three
parametres would be about maximum if you wanted to make the best
possible query in all circumstances. But what about making sure that at
least one indexed field is specified completely so that the index can be
used?

E.g.

if (:telephone is not null) then
select * from firebirdtable
where telephone = :telephone
and (town = :town or :town is null)
and (name = :name or :name is null)
and (age = :age or :age is null)
else if (:name is not null) then
select * from firebirdtable
where name = :name
and (town = :town or :town is null)
and (telephone = :telephone or :telephone is null)
and (age = :age or :age is null)
else if (:town is not null) then
select * from firebirdtable
where town = :town
and (name = :name or :name is null)
and (telephone = :telephone or :telephone is null)
and (age = :age or :age is null)
else if (:age is not null) then
select * from firebirdtable
where age = :age
and (name = :name or :name is null)
and (telephone = :telephone or :telephone is null)
and (town = :town or :town is null)
else
select * from firebirdtable

This example has four parameters, but only five queries as compared to
the sixteen that would be necessary if implementing solution 2
completely. Although it won't be as quick as solution 2, it could be
considerably faster than solution 3. The benefit of this approach
compared to solution 2 is that it is far more expandable in that it will
reduce the number of queries from (x^2) to (x+1), where x is the number
of indexed parametres. You know your data and this idea may have to be
further refined to give you decent performance in many cases, e.g. you
may know that 98% of orders have the status paid so you treat that value
the same way as NULL or you can say that town and age are so general
that both indexes should be used if both are specified and name and
telephone aren't.

HTH,
Set

PenWin wrote:

> I am still trying to find a solution to this problem myself and still can't
> find any that's good:
>
> 1) Building queries dynamically is only viable if the application has an
> access to the actual tables.
> In my application, I can't afford to do that - the application is accessible
> from the internet and there's a reasonable assumption that the actual
> frontend will be written by other programmers, possibly those who have no
> idea about security. To solve that, I am only providing access to some
> stored procedures which do the actual work, and dynamic building of queries
> has several serious problems (such as "how to escape apostrophes" and the
> problem that EXECUTE QUERY would still require that the user had access to
> the actual tables).
>
> 2) Using several different queries and branching between them according to
> the parameter is only usable if you have two or three parameters at most -
> the number of required queries grows too quickly.
>
> 3) Using queries with conditions such as (NAME=:name OR :name IS NULL) seems
> to be the best solution so far, but the problem is that it is very slow.
> While the actual time to run a query with (NAME=:name) is negligible (some
> 0.01 sec), query with the same parameter but condition (NAME=:name OR :name
> IS NULL) could very well take as much as three seconds or more. Still, it is
> a better solution than the two above.
>
> Pepak