Re: Poor query optimizer choices is making Derby unusable for large tables

[Jeffrey Lichtman]

I've described the problem in detail below, and I'd appreciate any 
assistance. Specifically:

i) Does anyone have any plans to fix this problem?

I believe the real problem is that Derby doesn't have any strategy 
for doing multiple scans for OR/IN clauses. This is a useful feature, 
but I don't know if anyone has entered an enhancement request.

ii) In the meantime, are there any work-arounds? I'd appreciate any 
suggestions that would decrease the execution time of my second 
query below (the one with with two search terms). Likewise, any 
general strategies for avoiding this problem with IN clauses would 
be appreciated.

You could re-write the query to use UNION:

SELECT ObjectId, SUM(WordLocation) AS Score
FROM tblSearchDictionary
WHERE Word = 'CONTACT' OR Word = 'ADD'
GROUP BY ObjectId;

would become:

SELECT ObjectId, SUM(WordLocation) AS Score
FROM
    (SELECT ObjectId, WordLocation
        FROM tblSearchDictionary
        WHERE Word = 'CONTACT'
     UNION ALL
     SELECT ObjectId, WordLocation
        FROM tblSearchDictionary
        WHERE Word = 'ADD') t
GROUP BY ObjectId;


                       -        Jeff Lichtman
                                [EMAIL PROTECTED]
                                Check out Swazoo Koolak's Web Jukebox at
                                http://swazoo.com/