具有多个相似子查询的 sproc 的 TSQL 优化

Question

所以，我继承了某人的产品，虽然我查看了很多代码，但我觉得其中很多都可以改进。我的首要任务是优化以下存储过程，我希望有人能让我走上正轨。虽然我很菜，但我不禁觉得必须有更好的方法来做到这一点……运行需要4分钟以上。

在 sproc 中，多次进行相同的连接。我真的不是在要求某人做我的工作，但请有人给我一个关于如何更好地构建以下内容的开始吗？:

我应该创建一个临时表而不是执行这么多嵌套连接吗？

谢谢，

BEGIN

DECLARE  @District VARCHAR(50)
SET @District =  '42'

    SET NOCOUNT ON;
    DECLARE @today varchar(30) 
    DECLARE @ToDatestr varchar(20) 
    DECLARE @ToDate15 varchar(20) 
    DECLARE @BOYear varchar(30) 
    DECLARE @BOMonth varchar(30) 
    DECLARE @BOWeek varchar(30) 
    SET @today = RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + '/' + RIGHT('00'+CAST(DAY(getdate()) as varchar), 2) + '/' + CAST(YEAR(getdate()) as varchar) 
    SELECT  d.utilitydistrictnumber AS "District #",
            emr.ExistingMeterID,
            emr.isvc AS "ISVC #",
            r.Utilityrouteid AS "Utility Route #",
            emr.cyclenumber AS "Utility Cycle #",
            pd."Name",
            REPLACE(REPLACE(pd."Address",CHAR(10),''),',',';') AS 'Address',
            CONVERT(float,(CASE WHEN ISNULL(p.Latitude,'.000000') = '.000000' THEN dw_p.Lat ELSE p.Latitude END)) AS 'Latitude',
            CONVERT(float,(CASE WHEN ISNULL(p.Longitude,'.000000') = '.000000' THEN dw_p.Long ELSE p.Longitude END)) AS 'Longitude',
            WeekendCustContact.mCount AS 'Weekend CustContact',
            After5PMCustContact.mCount AS 'After 5PM CustContact',
            TotalCustContact.mCount AS 'Total CustContact',
            AppointmentArranged.mCount AS 'Appointment Arranged',
            FieldUTC.mCount AS 'Total FieldUTCs',
            Letters.TotalHTALetter ,
            emr.UtilityOnHold,
            emr.DeploymentOnHold,
            emr.DeploymentOnHoldReason,
            ,o.ActivityName
        From  Product_CompanyProd_Repository.dbo.Existingmetersroutes emr (NOLOCK)
        INNER JOIN
        Product_CompanyProd_Repository.dbo.ExistingmetersPremises emp (NOLOCK)
                ON emp.existingmeterid = emr.existingmeterid
        INNER JOIN
        Product_CompanyProd_Repository.dbo.Premises p (NOLOCK)
                ON p.premiseid = emp.premiseid
        LEFT JOIN
        [ProductMAIN-ALIAS].[DW_Company].[dbo].[Premise_LatLongs] dw_p (NOLOCK)
                ON dw_p.premiseid = p.premiseid
        INNER JOIN
        [Product_CompanyPROD_Repository].[dbo].[routes] AS r     (NOLOCK)
            ON r.routeid = emr.routeid
        INNER JOIN 
        [Product_CompanyPROD_Repository].[dbo].[Districts] AS d  (NOLOCK)
            ON d.districtid = r.districtid AND d.utilitydistrictnumber = @District
        LEFT JOIN [Product_CompanyProd].[dbo].[ODMorders] o
            ON o.summary = emr.isvc AND o.StatusID < 9
        LEFT JOIN 
            (SELECT oo.Summary AS ISVC, COUNT(*) AS mcount
                    FROM Product_CompanyProd.dbo.ODMOrders AS oo  (NOLOCK)
                        INNER JOIN
                        Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa  (NOLOCK)
                                ON oa.Orderid = oo.Orderid  AND oa.UTCCode <> '' 
                                    AND oa.district = @District 
                    WHERE oo.StatusID IN (9,10) 
                    GROUP BY oo.summary 
            ) AS FieldUTC ON FieldUTC.isvc=emr.isvc
        LEFT JOIN 
            (SELECT e.isvc, COUNT(*) AS mcount
                FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e  (NOLOCK)
                INNER JOIN
                [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
                    on e.existingmeterid = p.existingmeterid
                INNER JOIN 
                [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
                    on pn.premiseid = p.premiseid
                        AND DATEPART(dw, pn.autotimestamp) IN (7,1)
                WHERE category = 'Call attempt'
                GROUP BY e.isvc

            ) AS WeekendCustContact ON WeekendCustContact.isvc=emr.isvc
        LEFT JOIN
            (SELECT e.isvc, COUNT(*) AS mcount
                FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e  (NOLOCK)
                INNER JOIN
                [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
                    on e.existingmeterid = p.existingmeterid
                INNER JOIN 
                [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
                    on pn.premiseid = p.premiseid
                        AND datepart(hh,pn.autotimestamp) >= 17
                WHERE category = 'Call attempt'
                GROUP BY e.isvc

            ) AS "After5PMCustContact" ON After5PMCustContact.isvc=emr.isvc
        LEFT JOIN 
            (SELECT e.isvc, COUNT(*) AS mcount
                FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e (NOLOCK)
                INNER JOIN
                [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
                    on e.existingmeterid = p.existingmeterid
                INNER JOIN 
                [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
                    on pn.premiseid = p.premiseid
                WHERE category IN ('Call attempt','Door hanger','Letter received by customer','Call to Company who referred the caller to OurCompany')
                GROUP BY e.isvc

            ) AS "TotalCustContact" ON TotalCustContact.isvc=emr.isvc
        LEFT JOIN
            (SELECT oo.Summary AS ISVC, COUNT(*) AS mcount
                    FROM Product_CompanyProd.dbo.ODMOrders AS oo (NOLOCK) 
                    WHERE oo.ActivityName = 'CompanyExchangeAppt' AND oo.StatusID < 9 
                    GROUP BY oo.summary
            ) AS "AppointmentArranged" ON AppointmentArranged.isvc=emr.isvc
        LEFT JOIN

            (SELECT emr.ISVC,ema.ColumnValue AS "TotalHTALetter"
            FROM 
                Product_CompanyProd_Repository.dbo.Existingmetersroutes emr  (NOLOCK)
                INNER JOIN
                Product_CompanyProd_Repository.dbo.ExistingmetersAuxiliary ema   (NOLOCK) on ema.existingmeterid = emr.existingmeterid 
                    AND ema.ColumnName LIKE 'HTALetter%'
            ) AS "Letters" ON Letters.isvc=emr.isvc

        LEFT JOIN 
        (SELECT * FROM
        ( SELECT o.summary AS isvc,
            REPLACE(REPLACE([od].Information.query('data(OrderDetails/PremiseDetails/Name)').value('.','varchar(50)'),CHAR(10),''),',',';') AS "Name",
            REPLACE(REPLACE([od].Information.query('data(OrderDetails/Location/StreetAddress)').value('.','varchar(50)'),CHAR(10),''),',',';') AS "Address",
            [od].Information.query('data(OrderDetails/PremiseDetails/Phone)').value('.','varchar(50)') AS "Phone",
            o.Autotimestamp
            From Product_CompanyProd.dbo.ODMOrders AS o  (NOLOCK)
                INNER JOIN 
                Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa  (NOLOCK)
                    ON oa.Orderid = o.Orderid  AND oa.district = @District AND oa.UTCCode <> ''
                INNER JOIN 
                [Product_CompanyProd].[dbo].[ODMOrderdetails] od  (NOLOCK)
                    ON od.Orderid = o.Orderid  
                WHERE o.StatusID IN (9,10)
        ) AS pd 
        WHERE  
                pd.Autotimestamp=(SELECT MAX(o.autotimestamp)
                                    From Product_CompanyProd.dbo.ODMOrders AS o (NOLOCK)
                                        INNER JOIN 
                                        Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa  (NOLOCK)
                                            ON oa.Orderid = o.Orderid  AND oa.district = @District 
                                        INNER JOIN 
                                        [Product_CompanyProd].[dbo].[ODMOrderdetails] od  (NOLOCK)
                                            ON od.Orderid = o.Orderid  
                                    WHERE o.summary = pd.isvc AND
                                            o.StatusID IN (9,10)
                                    )
        ) AS pd ON pd.isvc = emr.isvc 

        Where 
             emr.Status NOT IN ('Complete','Fieldcomplete','UTC') 

END

Answer 1

这 3 个子查询可以组合在一起:

...
LEFT JOIN 
    (SELECT e.isvc, COUNT(*) AS mcount
        FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e  (NOLOCK)
        INNER JOIN
        [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
            on e.existingmeterid = p.existingmeterid
        INNER JOIN 
        [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
            on pn.premiseid = p.premiseid
                AND DATEPART(dw, pn.autotimestamp) IN (7,1)
        WHERE category = 'Call attempt'
        GROUP BY e.isvc

    ) AS WeekendCustContact ON WeekendCustContact.isvc=emr.isvc
LEFT JOIN
    (SELECT e.isvc, COUNT(*) AS mcount
        FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e  (NOLOCK)
        INNER JOIN
        [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
            on e.existingmeterid = p.existingmeterid
        INNER JOIN 
        [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
            on pn.premiseid = p.premiseid
                AND datepart(hh,pn.autotimestamp) >= 17
        WHERE category = 'Call attempt'
        GROUP BY e.isvc

    ) AS "After5PMCustContact" ON After5PMCustContact.isvc=emr.isvc
LEFT JOIN 
    (SELECT e.isvc, COUNT(*) AS mcount
        FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e (NOLOCK)
        INNER JOIN
        [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
            on e.existingmeterid = p.existingmeterid
        INNER JOIN 
        [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
            on pn.premiseid = p.premiseid
        WHERE category IN ('Call attempt','Door hanger','Letter received by customer','Call to Company who referred the caller to OurCompany')
        GROUP BY e.isvc

    ) AS "TotalCustContact" ON TotalCustContact.isvc=emr.isvc
...

这是一个可能的组合版本:

LEFT JOIN 
    (SELECT
        e.isvc,
        COUNT(*) AS TotalCount,
        COUNT(CASE WHEN DATEPART(dw, pn.autotimestamp) IN (7, 1) AND category = 'Call attempt' THEN 1 END) AS WeekendCount,
        COUNT(CASE WHEN datepart(hh, pn.autotimestamp) >= 17     AND category = 'Call attempt' THEN 1 END) AS After5PMCount
    FROM [Product_CompanyProd_Repository].[dbo].[existingmetersroutes] e (NOLOCK)
        INNER JOIN
        [Product_CompanyProd_Repository].[dbo].[existingmeterspremises] p  (NOLOCK)
            on e.existingmeterid = p.existingmeterid
        INNER JOIN 
        [Product_CompanyProd_Repository].[dbo].[premisenotes] pn  (NOLOCK)
            on pn.premiseid = p.premiseid
        WHERE category IN ('Call attempt','Door hanger','Letter received by customer','Call to Company who referred the caller to OurCompany')
        GROUP BY e.isvc

    ) AS "CustContact" ON CustContact.isvc=emr.isvc

当然，您还需要替换选择列表中的相应列。

查询性能缓慢的另一个可能原因是这个小怪物:

...
LEFT JOIN 
(SELECT * FROM
( SELECT o.summary AS isvc,
    REPLACE(REPLACE([od].Information.query('data(OrderDetails/PremiseDetails/Name)').value('.','varchar(50)'),CHAR(10),''),',',';') AS "Name",
    REPLACE(REPLACE([od].Information.query('data(OrderDetails/Location/StreetAddress)').value('.','varchar(50)'),CHAR(10),''),',',';') AS "Address",
    [od].Information.query('data(OrderDetails/PremiseDetails/Phone)').value('.','varchar(50)') AS "Phone",
    o.Autotimestamp
    From Product_CompanyProd.dbo.ODMOrders AS o  (NOLOCK)
        INNER JOIN 
        Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa  (NOLOCK)
            ON oa.Orderid = o.Orderid  AND oa.district = @District AND oa.UTCCode <> ''
        INNER JOIN 
        [Product_CompanyProd].[dbo].[ODMOrderdetails] od  (NOLOCK)
            ON od.Orderid = o.Orderid  
        WHERE o.StatusID IN (9,10)
) AS pd 
WHERE  
      pd.Autotimestamp=(SELECT MAX(o.autotimestamp)
                            From Product_CompanyProd.dbo.ODMOrders AS o (NOLOCK)
                                INNER JOIN 
                                Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa  (NOLOCK)
                                    ON oa.Orderid = o.Orderid  AND oa.district = @District 
                                INNER JOIN 
                                [Product_CompanyProd].[dbo].[ODMOrderdetails] od  (NOLOCK)
                                    ON od.Orderid = o.Orderid  
                            WHERE o.summary = pd.isvc AND
                                    o.StatusID IN (9,10)
                          )
) AS pd ON pd.isvc = emr.isvc 
...

这是我将如何重写它:

LEFT JOIN 
    (SELECT
        o.summary AS isvc,
        REPLACE(REPLACE([od].Information.query('data(OrderDetails/PremiseDetails/Name)').value('.','varchar(50)'),CHAR(10),''),',',';') AS "Name",
        REPLACE(REPLACE([od].Information.query('data(OrderDetails/Location/StreetAddress)').value('.','varchar(50)'),CHAR(10),''),',',';') AS "Address",
        [od].Information.query('data(OrderDetails/PremiseDetails/Phone)').value('.','varchar(50)') AS "Phone",
        o.Autotimestamp
    From Product_CompanyProd.dbo.ODMOrders AS o  (NOLOCK)
        INNER JOIN 
        Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa  (NOLOCK)
            ON oa.Orderid = o.Orderid  AND oa.district = @District AND oa.UTCCode <> ''
        INNER JOIN 
        [Product_CompanyProd].[dbo].[ODMOrderdetails] od  (NOLOCK)
            ON od.Orderid = o.Orderid  
    WHERE o.StatusID IN (9,10)
      AND NOT EXISTS (
        SELECT *
        FROM Product_CompanyProd.dbo.ODMOrders o2
          INNER JOIN Product_CompanyProd.dbo.ODMOrderAttributesUME AS oa2  (NOLOCK)
            ON oa2.Orderid = o2.Orderid  AND oa2.district = @District AND oa2.UTCCode <> ''
        WHERE o.summary = o2.summary AND o2.StatusID IN (9,10) AND o.Autotimestamp < o2.Autotimestamp
      )
    ) AS pd ON pd.isvc = emr.isvc