db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: Poor query optimizer choices is making Derby unusable for large tables
Date Fri, 11 Nov 2005 19:13:02 GMT

>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
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 


Mime
View raw message