Hello John,

> SELECT COUNT(*) AS TOTALS FROM BOOKS A
> INNER JOIN BOOKS B
> ON (UPPER(A.AUTHOR) = UPPER(B.AUTHOR))
> AND
> (UPPER(A.TITLE)=UPPER(B.TITLE))
> AND
> (A.PRICE = B.PRICE) /* price is is INTEGERfield */
> AND
> (A.YEAR = B.YEAR) /* YEAR is INTEGER field */

--8<--

>
> The above is the simplified version. The real SP does more calculation
> on datefields and other real-numbers using UDF's. The suspend is because
> the result is read from a Query like
>
> This works. But I have the feeling it is kinda slow. Using a table of
> about 2000 books, it takes allmost 2 seconds. In real life, it may have
> to run on a table of 100's thousands of records and a more dedicated
> version has to return a list of comparable BOOK_ID's or even a
> percentage that says how many fields had the same or almost the same
> content.
>
> So I wonder if this is the right approach. I tried to put indexes on
> some fields like AUTHOR and TITLE, but I think the use of UPPER in the
> ON-clause prevents using these indexes.

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.

> Is the ON_Clause always completely evaluated? Or does it stop after a
> FALSE in the first condition?
>
> Would/Should there be a performance-gain if I move some of the ON
> conditions in the WHERE clause?
>
>
> Thank you for any suggestions!

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