sql 查询优化，哪为给看看，实在是太慢了，有两分多钟

Question

SELECT DISTINCT TOP (30) PERCENT ID, CustomerNo, CName, EName, Alias,
                          (SELECT     CaseNo
                            FROM          dbo.CIS_ReceptionCase
                            WHERE      (CaseID =
                                                      (SELECT     MAX(CaseID) AS Expr1
                                                         FROM          dbo.CIS_ReceptionCase AS CIS_ReceptionCase_4
                                                         WHERE      (CustomerID = cus.ID)))) AS caseno,
                          (SELECT     CustomerVersion
                            FROM          dbo.CIS_ReceptionCase AS CIS_ReceptionCase_6
                            WHERE      (CaseID =
                                                       (SELECT     MAX(CaseID) AS Expr1
                                                         FROM          dbo.CIS_ReceptionCase AS CIS_ReceptionCase_5
                                                         WHERE      (CustomerID = cus.ID)))) AS CustomerVersion,
                          (SELECT     ReceptionDate
                            FROM          dbo.CIS_ReceptionCase AS CIS_ReceptionCase_3
                            WHERE      (CaseID =
                                                       (SELECT     MAX(CaseID) AS Expr1
                                                         FROM          dbo.CIS_ReceptionCase AS CIS_ReceptionCase_2
                                                         WHERE      (CustomerID = cus.ID)))) AS receptiondate,
                          (SELECT     Email
                            FROM          dbo.CRM_Email
                            WHERE      (ID =  (SELECT     MAX(ID) AS Expr1  FROM          dbo.CRM_Email AS CRM_Email_1  WHERE      (CustomerID = cus.ID) AND (State = 1) AND (FavSort =  (SELECT     MIN(CAST(FavSort AS int)) AS Expr1  FROM          dbo.CRM_Email AS CRM_Email_2   WHERE      (FavSort <> '') AND (CustomerID = cus.ID) AND (State = 1)))))) AS email, (SELECT     TelNo  FROM          dbo.CRM_ContactInfo
                            WHERE      (ID =
                                                       (SELECT     MIN(ID) AS Expr1
                                                         FROM          dbo.CRM_ContactInfo AS CRM_ContactInfo_1
                                                         WHERE      (CustomerID = cus.ID) AND (State = 1) AND (ContactType = 7) AND (LEN(TelNo) = 8) AND (FavSort =
                                                                                    (SELECT     MIN(CAST(FavSort AS int)) AS Expr1
                                                                                      FROM          dbo.CRM_ContactInfo AS CRM_ContactInfo_2
                                                                                      WHERE      (FavSort <> '') AND (CustomerID = cus.ID) AND (State = 1) AND (ContactType = 7) AND (LEN(TelNo) = 8)))))) AS telno,
                          (SELECT     CUnion
                            FROM          dbo.CRM_Address
                            WHERE      (ID =
                                                       (SELECT     MAX(ID) AS Expr1
                                                         FROM          dbo.CRM_Address AS CRM_Address_3
                                                         WHERE      (AddressType = 19) AND (CustomerID = cus.ID) AND (State = 1) AND (FavSort =
                                                                                    (SELECT     MIN(CAST(FavSort AS int)) AS Expr1
                                                                                      FROM          dbo.CRM_Address AS CRM_Address_4
                                                                                      WHERE      (FavSort <> '') AND (AddressType = 19) AND (CustomerID = cus.ID) AND (State = 1)))))) AS cunion,
                          (SELECT     PUnion
                            FROM          dbo.CRM_Address AS CRM_Address_2
                            WHERE      (ID =
                                                       (SELECT     MAX(ID) AS Expr1
                                                         FROM          dbo.CRM_Address AS CRM_Address_1
                                                         WHERE      (AddressType = 21) AND (CustomerID = cus.ID) AND (State = 1) AND (FavSort =
                                                                                    (SELECT     MIN(CAST(FavSort AS int)) AS Expr1
                                                                                      FROM          dbo.CRM_Address AS CRM_Address_5
                                                                                      WHERE      (FavSort <> '') AND (AddressType = 21) AND (CustomerID = cus.ID) AND (State = 1)))))) AS punion,
                          (SELECT     ID
                            FROM          dbo.CRM_Address AS CRM_Address_2
                            WHERE      (ID =
                                                       (SELECT     MAX(ID) AS Expr1
                                                         FROM          dbo.CRM_Address AS CRM_Address_1
                                                         WHERE      (AddressType = 21) AND (CustomerID = cus.ID) AND (State = 1) AND (FavSort =
                                                                                    (SELECT     MIN(CAST(FavSort AS int)) AS Expr1
                                                                                      FROM          dbo.CRM_Address AS CRM_Address_5
                                                                                      WHERE      (FavSort <> '') AND (AddressType = 21) AND (CustomerID = cus.ID) AND (State = 1)))))) AS punionID,
                          (SELECT     ID
                            FROM          dbo.CRM_Address AS CRM_Address_6
                            WHERE      (ID =
                                                       (SELECT     MAX(ID) AS Expr1
                                                         FROM          dbo.CRM_Address AS CRM_Address_3
                                                         WHERE      (AddressType = 19) AND (CustomerID = cus.ID) AND (State = 1) AND (FavSort =
                                                                                    (SELECT     MIN(CAST(FavSort AS int)) AS Expr1
                                                                                      FROM          dbo.CRM_Address AS CRM_Address_4
                                                                                      WHERE      (FavSort <> '') AND (AddressType = 19) AND (CustomerID = cus.ID) AND (State = 1)))))) AS cunionID,
                          (SELECT     RequestType
                            FROM          dbo.CRM_CustomerSpecialRequirements
                            WHERE      (ID =
                                                       (SELECT     MIN(ID) AS Expr1
                                                         FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_8
                                                         WHERE      (RequestType IN
                                                                                    (SELECT     RequestType
                                                                                      FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_7
                                                                                      WHERE      (CustomerID = cus.ID))) AND (RequestType = 2001)))) AS notesEmail,
                          (SELECT     RequestType
                            FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_6
                            WHERE      (ID =
                                                       (SELECT     MIN(ID) AS Expr1
                                                         FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_5
                                                         WHERE      (RequestType IN
                                                                                    (SELECT     RequestType
                                                                                      FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_4
                                                                                      WHERE      (CustomerID = cus.ID))) AND (RequestType = 2002)))) AS notesAddress,
                          (SELECT     RequestType
                            FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_3
                            WHERE      (ID =
                                                       (SELECT     MIN(ID) AS Expr1
                                                         FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_2
                                                         WHERE      (RequestType IN
                                                                                    (SELECT     RequestType
                                                                                      FROM          dbo.CRM_CustomerSpecialRequirements AS CRM_CustomerSpecialRequirements_1
                                                                                      WHERE      (CustomerID = cus.ID))) AND (RequestType = 2000)))) AS notesTel
FROM         dbo.CRM_Customer AS cus
WHERE     (CustomerNo IN
                          (SELECT     CustomerNo
                            FROM          dbo.CIS_ReceptionCase AS CIS_ReceptionCase_1))
ORDER BY ID

Answer 1

嵌套太多了。试着去掉嵌套，用join来处理看看。

Answer 2

子查询里面还有聚集函数（MIN等）肯定慢的，重新梳理下逻辑吧，减少嵌套和子查询数量尽量合并。
你这语句看着晕。太长了

Answer 3

嵌套查询太多，子查询中还有聚集函数建议用join合并减少嵌套，其次先不要ORDER BY排序

Answer 4

这么长的sql能查出来的结果是正确的，不容易啊
1、用存储过程
2、把子查询拆分掉单独查询插入临时表，若数据较多可以为临时表创建索引
3、再进行多表查询