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
