Optimize an InterBase Query

SELECT
  AI_636.PARENT_ID AS PART,
  MAX(b.AP_1036) AS ESTEND,
  MAX(a.AP_3222) AS ACTEND
FROM
  AI_636
  LEFT OUTER JOIN AI_665 a
    ON
    (
      a.AP_1033 = AI_636.PARENT_ID
      AND SUBSTR(a.AP_1028, 1, 4) >= '2000'
      AND a.AP_1030 NOT IN ('994')
      AND
      (
        a.AP_1033 NOT IN
        (
          SELECT AI_665.AP_1033 FROM AI_665 WHERE AI_665.AP_3222 IS NULL
        )
      )
    )
  JOIN AI_665 b
    ON
    (
      b.AP_1033 = AI_636开发者_C百科.PARENT_ID
      AND SUBSTR(b.AP_1028, 1, 4) >= '2000'
    )
  GROUP BY AI_636.PARENT_ID

This query is a small part of a larger one and it causes the entire call to execute very slowly.

Basically, there is a parent operation and then several child operations underneath it. The estimated vs actual end dates of the operations are only stored at the child level, so to derive one for the parent level, I am trying to find the largest dates at the child level. The problem I run into is when a child operation is not completed, it has a NULL end date, and the MAX() function ignores these. I am getting around this by joining the child operations table to itself and narrowing it down to only include child operations whose siblings all have non-NULL end dates.

Is there any way I can optimize the search for parent operations with children with non-NULL end dates?

---

My last answer had a mistake, but maybe this will help:

AND NOT EXISTS (SELECT NULL
                FROM   AI_665 a2
                WHERE  a2.AP_1033 = a.AP_1033
                AND    a2.AP_3222 IS NULL)

---

You must create an descending index to speed up the MAX()