TSQL optimization of sproc with multiple similar subqueries

So, I have inherited someones product, and while I am looking at a lot of the code, I feel much of it can be improved. My fist task, that I hope someone can put me on the right tracks with is optimizing the following stored proc. Although I am quite green, I can't help feel that there must be a better way to do this... it takes 4+ minutes to run.

In the sproc, there are multiple times the same joins are made. I really am not asking for someone to do my job, but please could someone give me a start as to how to better structure the following?:

Should I create a temp tables instead of doing so many nested joins?

Thanks,

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
  开发者_JS百科      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

These 3 subqueries could be combined:

...
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
...

Here's a possible combined version:

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

Of course, you'll also need to replace the corresponding columns in the select list.

Another possible cause of the query's slow performance is this little monster:

...
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 
...

And here's how I would rewrite it:

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 

yes, looking at that, it can be optimized. Right off the bat, you can change the line

SET @today = RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + '/' +
             RIGHT('00'+CAST(DAY(getdate()) as varchar), 2) + '/' +
             CAST(YEAR(getdate()) as varchar) 

to

SET @today = convert(varchar,GETDATE(),101)

which leads me to believe there are other things you can do to help out the performance. Looking at it makes me think they were trying to build a pivot table or matrix report, try rebuilding the query in test using the PIVOT command or a CTE(Common Table Expression). I would be inclined to try the CTE first.
--chris

These link to the SQL 2005 references on MSDN

Pivot Info

CTE