我正在使用Firebird 2.1,并且需要一些帮助来优化此查询:(也许是通过用JOINS替换IN-s或通过某种方式来加快它的速度,因为它非常慢)

SELECT ClientID, ClientType, ClientName
FROM Clients
WHERE
    (
        AccessRights = 0 OR
        OwnerUserID = :uid OR
        (
            AccessRights = 2 AND
            ClientID IN (SELECT ClientID
                            FROM ClientRights
                            WHERE UserID = :uid)
        )
    )
    AND ClientID IN (SELECT CC.ClientID
                    FROM CaseClients CC
                    WHERE CC.CaseID IN (SELECT DISTINCT CaseID
                                        FROM TimeSheet
                                        WHERE IsBilled = 0)
                        AND CC.ClientToBill = 1
                        AND (SELECT BillingType
                                FROM Cases
                                WHERE CaseID = CC.CaseID) = 2
    );


谢谢!

最佳答案

SELECT ClientID, ClientType, ClientName FROM Clients
WHERE
(
    AccessRights = 0 OR
    OwnerUserID = :uid OR
    (
        AccessRights = 2 AND
        EXISTS(SELECT * FROM ClientRights r WHERE r.UserID = :uid and r.ClientId=Clients.ClientID)
    )
)
AND EXISTS(SELECT *
                FROM CaseClients CC
                WHERE
                CC.ClientID=Clients.ClientID and
                   EXISTS(SELECT * FROM TimeSheet
                          WHERE IsBilled = 0 and TimeSheet.CaseID=CC.CaseID)
                    AND CC.ClientToBill = 1
                    AND EXISTS(SELECT BillingType
                            FROM Cases
                            WHERE CaseID = CC.CaseID and BillingType=2)
);

关于sql - 如何在Firebird 2.1中优化此查询?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9197887/

10-13 03:24