--- In firebird-support@yahoogroups.com, Robert martin <rob@...> wrote:

>
> Hi
>
> Thanks Adam. Yes the IN clause is the bottleneck. I have just

solved ?

> the issue by creating a stored procedure that handles the IN part so my
> query now looks like...
>
>
> SELECT bk.ISBN13, bk.FTI
> FROM Book bk
> JOIN Pr_BookWordAndSearch('WET', 'WILD', NULL, NULL, NULL, NULL, NULL,
> NULL, NULL, NULL) p ON p.ISBN13 = bk.ISBN13
>
>
> This produces records in .1 < ms and should work great.

I would make a minor change to prevent a 'no current record' exception
from possibly occurring.

SELECT bk.ISBN13, bk.FTI
FROM Pr_BookWordAndSearch('WET', 'WILD', NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL) p
LEFT JOIN Book bk ON p.ISBN13 = bk.ISBN13
WHERE BK.ISBN13 IS NOT NULL;

That will ensure the join order starts with the stored procedure.

> p.s. your solution query didn't improve the situation.

I was really doubtful it would. The only other thing you may want to
check is whether your statistics are up to date, but considering the
|| '' did nothing to improve things, I doubt it is statistics.

Adam