db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul Sterk <Paul.St...@Sun.COM>
Subject Derby perf bug?
Date Fri, 25 May 2007 21:03:57 GMT

Team,

I am working with Derby 10.2.2.  This SQL query has an unacceptable
performance:

SELECT * FROM registryobject WHERE id IN (SELECT targetObject FROM
association WHERE UPPER(sourceobject) = UPPER(?))

The registryobject table is a UNION of around 20 other tables.  When
these tables have several thousand records, the query above runs for a
very long time.  AFAIK, this is not a problem on Oracle.

Now, if I break this query into two:

SELECT targetObject FROM association WHERE UPPER(sourceobject) = UPPER(?)

SELECT * FROM registryobject WHERE id IN ('targetObject.id1',
'targetObject.id2', ...)

I get acceptable performance.  Is this a bug in derby or something
else?  Am I missing something?

Paul




Mime
View raw message