we have a table EVALUATIONGROUPS which has some 6000 records and another table EVALUABLES mapped to each other by table EVALUATIONGROUPS2EVALUABLES which has 25000 records.
The following JDO generated query takes our Derby 10.5.1.1 database 10 minutes to return 0 or 4 results. All indexes are properly set (recently refreshed with SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SCHEMA', 'EVALUABLES', 1)). Is there any experience around or do you have any suggestions on how to make this query (which is not too fancy) runnable? Is it a problem of the query optimizer?
The select is:
FROM EVALUATIONGROUPS THIS
WHERE 6912 = THIS.MODEL
AND THIS.ACTIVE = 'Y'
AND THIS.DELETED = 'N'
AND THIS.RELEASED = 'N'
FROM EVALUATIONGROUPS2EVALUABLES THIS_EVALUABLES_VAR_EVL ,
WHERE THIS_EVALUABLES_VAR_EVL.EVALUATIONGROUPS_ID_OID = THIS.EVALUATIONGROUPS_ID
AND UNBOUND_VAR_EVL.EVALUABLES_ID = THIS_EVALUABLES_VAR_EVL.EVALUABLES_ID_EID
AND NOT (UNBOUND_VAR_EVL.TEMPLATE_REF_EVALUABLES_ID_OID IS NULL)
AND 29308 = UNBOUND_VAR_EVL.TEMPLATE_REF_EVALUABLES_ID_OID
AND UNBOUND_VAR_EVL.DELETED = 'N'
Any hints appreciated. Thx