Subject Re: [firebird-support] Two questions regarding performance
Author Milan TomeŇ° - Position
Dne 29.04.2010 15:09, Sergio H. Gonzalez napsal(a):
> Hi! Probably these questions were asked before, but I can't find
> nothing on the
> web (perhaps I'm not seeking properly)
> 1) Having some clauses concatenated with "and" in a where statement,
> does it
> matter the order of the clauses? I mean: I always tend to put in the
> first order
> the clauses that have an index (ie: PK) and then the clauses that
> don't have
> index. Is that OK? or Firebird just "know" what sentence is better to
> evaluate
> first?

AFAIK it doesn't.

> 2) Does the "or" operator decrease the performance? I have some where
> like this:
> where (ID = :ID) or (:ID = -1)

It can, because you can specify more than 1 value for a single column so
execution plan can be more complex.
But in this case I would recommend to use reverse order:
where (:ID = -1) or (ID = :ID)
because or evaluation of booleans (when first value is evaluated to true
there is no need to evaluate second part because result will be true
regardless of result of second part)

> So I pass -1 when I want all the records. Is that OK? Or is better to
> reconstruct the select in the client, depending what I want '

IMO - I'll post another select to the server.


[Non-text portions of this message have been removed]