Subject Re: [firebird-support] performance
Author Svein Erling Tysvaer
Hi Sergio!

> (FIELD1 = 'A' and :par1 = -1) or
> (FIELD1 = 'B' and :par2 = -1) or
> (FIELD1 = 'C' and :par3 = -1) ... etc

The problem with this, is that at prepare time Firebird cannot know
which field to use, hence it can never use an index.

> What would be better (faster) ? The previous approach or just
> construct the SQL statement in the client side with only the
> filters I need?

Deciding the query at the client is what people normally do, and it can
often use an index. Sometimes, people write stored procedures which are
stored on the server.

Generally, I would not recommend the statement you wrote above, although
with only one table involved, you need quite a few rows in TABLE_1 for
the query to become slow.

Sometimes it is better to be practical than theoretical about problems,
and you may find that

AND ((FIELD2 = 'B' AND :PAR1 = -1)
OR (FIELD3 = 'C' AND :PAR1 = -1))

may be a good trade-off.

> Generally speaking: Is a bad practice to construct the SQL on the
> client side? Some times it seems to be no other option...

No, it is not bad practice. What is bad practice is to do things like
rewriting the query for every iteration in a loop, when you might have
written the query once, prepared it and then just changed the parameter
for every iteration. Though that is if you only need to change the
parameter value(s), e.g. from 'A' to 'B'. Changing which field(s) to
include in your WHERE clause is something completely different and
writing a new statement would be the most common solution.