At 08:48 AM 6/11/2006, you wrote:

>Hi
>
>We have the following structure...
>
>Book (ISBN13 PK, ....) 50000 recs
>
>BookKeyWord (ISBN13, WordRef (CPK)) -> We have also created an index on
>WordRef as this improve performance on some queries. 790000 recs
>
>KeyWord (WordRef PK, Word) -> Word is indexed. 81000 recs
>
>
>We are trying to write a query that will return a list of books where
>the book contains all of a list of words. We have the following attemp
>
>
>SELECT bk.ISBN13, bk.FTI
>FROM Book bk
>WHERE bk.ISBN13 IN (
>
>SELECT bkw.ISBN13
>FROM KeyWord kw
>JOIN BookKeyWord bkw ON bkw.WordRef = kw.WordRef
>WHERE kw.Word = 'WET'
>OR kw.Word = 'WILD'
>GROUP BY bkw.ISBN13
>HAVING COUNT(bkw.ISBN13) = 2)
>
>
>Where this SQL will be generated in code. It takes 4 secs with the
>following plan
>
>PLAN SORT (JOIN (KW INDEX (KEYWORD_WORD,KEYWORD_WORD),BKW INDEX
>(BOOKKEYWORD_PK)))
>PLAN (BK NATURAL)
>
>The problem appears to be the index on ISBN13 on Book isn't being used.
>If I create a view based on the select used in the 'IN' part of the
>above and then join this view to book the performance is ms and all
>indexes are used.
>
>I have considered using UNION but can't find Union clause that only
>returns the intersection of two (or more) SQLs.
>
>I would greatly appreciate any suggestions on how to improve this.
>Ironically it is currently faster to create a temporary view, join of
>this, display results and then delete the view !
>
>We are expecting the data to be significantly larger in the future.

Don't use subqueries for predicating huge searches - make a view to
denormalise the keyword refs and use the inner join directly:

create view v_wordsearch
as
SELECT bk.ISBN13. kw.Word
FROM Book bk
JOIN BookKeyWord bkw ON bkw.ISBN13 = bk.ISBN13
JOIN Keyword kw on bkw.WordRef = kw.WordRef
GROUP BY 1
HAVING COUNT(bkw.ISBN13) = 2

Then, for your runtime search:

SELECT bk.ISBN13, bk.FTI
FROM Book bk
JOIN v_wordsearch vw on vw.ISBN13 = bk.ISBN13
where vw.Word in ('WET', 'WILD')

-- or this might go faster (compare the plans):

SELECT bk.ISBN13, bk.FTI
FROM Book bk
JOIN v_wordsearch vw on vw.ISBN13 = bk.ISBN13
and vw.Word in ('WET', 'WILD')


This approach restricts you to ISBNs having an exact count of 2,
though, which isn't very flexible. You'd need one view for each
possible number of matches you were interested in. In Fb 2.0 you can
make it more "configurable" by replacing the view with a derived
table expression. If you are sticking with 1.5, I'd suspect that you
really need a proper SP that can handle all the requirements.

FWIW, I recommend that you keep right away from a solution that
requires joining to or subquerying stored procedures. I would rather
lie down in front of a train.

./heLen