如何合并含ConcatRelated与Sum的两段SQL查询？优化共享库性能

我们在共享网络驱动器上用数据库跟踪数据、发送通信，近期要给M1签证申请表做预填功能，部分字段得从现有数据计算得出。我写了几个从同两张表取数的SQL，本地桌面运行时速度很快，但部署到共享驱动器后，新查询慢得离谱，其他功能倒是正常。查资料后发现，要连续跑3-4个查询才能填充表单，这才导致了卡顿。现在想把下面两段核心SQL合并成一个，缩短等待时间——我对SQL了解不多，查了两天也没搞定，附上两段查询代码：

原查询1 #

SELECT DISTINCT Request.PermNumber, Request.Status,
     Request.Class, Request.StartDate, Request.Days, 
     ConcatRelated("Class", 
         "MALTESTConfirmationInformationQry2022",
         "PermNumber= " & Request.PermNumber) AS AllClasses
FROM Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
WHERE (((Request.Status)="Confirmed") AND ((Request.StartDate)>Date()))
ORDER BY Request.StartDate;

原查询2 #

SELECT Request.PermNumber, Request.Status, Sum(Request.Days) AS TotalDays
FROM Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
GROUP BY Request.PermNumber, Request.Status 
HAVING (((Request.Status) Like "Confirmed"));

合并后的查询 #

SELECT DISTINCT 
    Request.PermNumber, 
    Request.Status,
    Request.Class, 
    Request.StartDate, 
    Request.Days, 
    ConcatRelated("Class", "MALTESTConfirmationInformationQry2022", "PermNumber= " & Request.PermNumber) AS AllClasses,
    TotalDays.TotalDays
FROM 
    (Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber)
    INNER JOIN 
    (
        SELECT 
            Request.PermNumber, 
            Sum(Request.Days) AS TotalDays
        FROM 
            Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
        WHERE 
            Request.Status = "Confirmed"
        GROUP BY 
            Request.PermNumber
    ) AS TotalDays ON Request.PermNumber = TotalDays.PermNumber
WHERE 
    Request.Status = "Confirmed" 
    AND Request.StartDate > Date()
ORDER BY 
    Request.StartDate;

关键说明 #

• 把原查询2改造成子查询TotalDays，计算每个PermNumber的总天数，同时将HAVING条件移到WHERE中，提升逻辑效率。
• 通过INNER JOIN将子查询与原查询1的主查询关联，关联条件为PermNumber，直接在结果中带出TotalDays字段。
• 保留原查询1的DISTINCT、ConcatRelated函数和过滤条件，确保原有业务逻辑不变。
• 统一处理Status="Confirmed"的过滤条件，避免重复判断。

内容的提问来源于stack exchange，提问作者MalloryP