Hello John,

> > That is correct, UPPER removes the ability to use an index. In
> > Firebird 2, you could use expression based indices, in Firebird
> > 1.5 or lower, I suggest you add a shadow field, eg: TITLE_UPPER,
> > which you populate by using a before insert/update trigger and
> > use that to do your compare... Considering the number of books
> > with the same title, this should be quick quicker.
>
> Right, yes, that speeds up! :-)
>
> But how about moving conditions from ON to WHERE?
>
> Some timings:
>
> ALL conditions (except the PK) in the ON-clause: 800 ms
> HALF of the conditions in the ON, half in the WHERE clause: 380 mS
> Wow, looks good!

Hmm, you can easily confuse the query optimizer by doing this
mixing the so-called SQL89 (join conditions in the WHERE clause)
and SQL92 (and up) syntax. I suggest you continue writing your
join conditions in the ON clause as it makes things much more clear.

> Now enthousiastic and moving all conditions to the WHERE-clause: 800 mS
>
> I'm trying to move some of the conditions from ON to WHERE, all
> producing the same The_result, but all different execution-times.
>
> So still the question is: When to put a condition in the ON or when to
> put it in the WHERE clause.

Rule of thumb: join conditions in the ON clause, filtering conditions
(for the FINAL resultset, that is, AFTER doing the JOIN) go in the
WHERE clause.

Think of a "joined" result as a new table. You can apply a WHERE
filter to it, but no longer a JOIN, as it's a single "table".

>And the other question remains: does FB stop
> evaluating the ON or WHERE when the first condition evaluates to FALSE
> and all other conditions are ANDed with the first? If so, it would make
> sense to put a condition that often likely evaluates to False at the
> beginning of the expression, wouldn't it?

I believe it evaluates everything, but I might be wrong here ...

Gotta wait on a definitive answer on that one.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com