优化 InterBase 查询
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.PARENT_ID
AND SUBSTR(b.AP_1028, 1, 4) >= '2000'
)
GROUP BY AI_636.PARENT_ID
该查询只是较大查询的一小部分，它会导致整个调用执行速度非常慢。
基本上，有一个父操作，然后在其下面有几个子操作。操作的估计结束日期与实际结束日期仅存储在子级别，因此为了导出父级别的结束日期，我试图找到子级别的最大日期。我遇到的问题是，当子操作未完成时，它的结束日期为 NULL，而 MAX() 函数会忽略这些。我通过将子操作表连接到自身并将其缩小到仅包含其同级都具有非 NULL 结束日期的子操作来解决此问题。
有什么方法可以优化对具有非 NULL 结束日期的子项的父操作的搜索吗？
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.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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我上次的回答有一个错误，但这也许会有所帮助：
My last answer had a mistake, but maybe this will help:
您必须创建降序索引以加速 MAX()
You must create an descending index to speed up the MAX()