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