| Subject | Re: Count(id) returns null. although count should never return null... |
|---|---|
| Author | Adam |
| Post date | 2005-09-07T23:59:02Z |
--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:
I do not think it is a big issue because it has a very easy work
around. You are obviously (incorrectly) assuming that all of these
queries are written by reasonable programmers. Many automated tools
generate these sorts of conditions when they have nothing in their
filter, and many databases handle it gracefully.
There are two cases that I have seen in this list where such an
optimisation would be desirable.
1. To ensure 0 records returned
This is usually implemented as a WHERE 1=0 or equivalently impossible
condition clause. The good part about this query is that it can be
easily converted into WHERE ID=0 AND ID=1 which is more efficient.
2. To ensure all records returned
A week or two back, someone had the condition WHERE :USER=-1 OR
USER=:USER. Their business logic was simple enough. They used -1 as a
placeholder for all.
Now of course there is a better way to handle these. For the first
option, it would be faster to not execute anything at all, but the
reality is that means making a code fork and remembering to do a
specific test for empty. Where absolute performance is not so
important, a dummy condition may only cost a few ms. With the current
implementation, 1=0 costs about 2.5 seconds for a table containing
30000 records, ID=0 AND ID=1 costs 15 ms (on my old box).
For the second option, it would be better not to use a where
condition at all when user = -1, but some people prefer not to use
execute statement inside PSQL, and they don't want to re-write each
query twice.
Optimisation is a constant trade off. I know that my queries are
slowed down because Firebird is doing some optimisation that has no
benefit to me, but I benefit from certain optimisations and caching I
probably wouldn't even have considered doing myself. It is simply
finding an acceptable middle ground. I don't believe that it would be
a slow operation to identify which expressions are not reliant on
table data, but I'm also not going to sulk if it is not deemed as
important as other development.
I was merely pointing out that the query he was running was not going
to be efficiently executed in Firebird, whether the optimisation that
could have detected the return no results thing is feasible is
probably for another group (arch or dev). I also provided a work
around that achieves the same result but far quicker.
Adam
<aharrison@i...> wrote:
> Adam wrote:not
> >
> > Just an aside, and a possible gotcha, the condition you use is
> > very efficient in Firebird1.5.2,
> >
> > WHERE 1=0
> >
> > will be evaluated for every record in the table (in Firebird
> > not sure about later versions).full
> >
> > Hopefully some time in the future Firebird will be optimised to
> > improve this, but till then, this works a treat.
> >
>
> That's actually an interesting question. Academic optimizers are
> of little tricks to catch idiotic queries and make them fast, withno
> benefit to queries that a reasonable programmer would ever write.we
> Should we go that way? How much code - and execution time - should
> devote that effort?Ann,
>
I do not think it is a big issue because it has a very easy work
around. You are obviously (incorrectly) assuming that all of these
queries are written by reasonable programmers. Many automated tools
generate these sorts of conditions when they have nothing in their
filter, and many databases handle it gracefully.
There are two cases that I have seen in this list where such an
optimisation would be desirable.
1. To ensure 0 records returned
This is usually implemented as a WHERE 1=0 or equivalently impossible
condition clause. The good part about this query is that it can be
easily converted into WHERE ID=0 AND ID=1 which is more efficient.
2. To ensure all records returned
A week or two back, someone had the condition WHERE :USER=-1 OR
USER=:USER. Their business logic was simple enough. They used -1 as a
placeholder for all.
Now of course there is a better way to handle these. For the first
option, it would be faster to not execute anything at all, but the
reality is that means making a code fork and remembering to do a
specific test for empty. Where absolute performance is not so
important, a dummy condition may only cost a few ms. With the current
implementation, 1=0 costs about 2.5 seconds for a table containing
30000 records, ID=0 AND ID=1 costs 15 ms (on my old box).
For the second option, it would be better not to use a where
condition at all when user = -1, but some people prefer not to use
execute statement inside PSQL, and they don't want to re-write each
query twice.
Optimisation is a constant trade off. I know that my queries are
slowed down because Firebird is doing some optimisation that has no
benefit to me, but I benefit from certain optimisations and caching I
probably wouldn't even have considered doing myself. It is simply
finding an acceptable middle ground. I don't believe that it would be
a slow operation to identify which expressions are not reliant on
table data, but I'm also not going to sulk if it is not deemed as
important as other development.
I was merely pointing out that the query he was running was not going
to be efficiently executed in Firebird, whether the optimisation that
could have detected the return no results thing is feasible is
probably for another group (arch or dev). I also provided a work
around that achieves the same result but far quicker.
Adam