Hi users,
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:
SELECT
COUNT(THIS.EVALUATIONGROUPS_ID)
FROM EVALUATIONGROUPS THIS
WHERE 6912 = THIS.MODEL
AND THIS.ACTIVE = 'Y'
AND THIS.DELETED = 'N'
AND THIS.RELEASED = 'N'
AND
(
EXISTS
(
SELECT
1
FROM EVALUATIONGROUPS2EVALUABLES THIS_EVALUABLES_VAR_EVL ,
EVALUABLES UNBOUND_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
