具有子查询优化的SQL Server存储过程

Question

我有四个表：

1. 销售人员（personId int ,PersonName nvarchar(20)..）
2. 人员和地区（autoId int,PersonId int,districtId int）：在特定地区工作的每个销售人员
3. 销售点（SalesId int,pointOfSalesId int,districtId int,pointOfSalesName nvarchar(20)，…）每个地区都有许多销售点
4. 每日销售额（SalesPersonId int,SoldAmount float,PointOfSalesId int, salesDate date，..）：每日销售额数据

为了保存每个销售人员的每日销售额，我需要首先显示他的所有销售点的列表，其中每个销售点的销售额总和（如果有的话）。

因此，我们需要的是显示特定销售人员在特定日期的所有销售点，以及积分的总销售额。

我有存储过程：

ALTER PROCEDURE [dbo].[SP_PoinOfSales_GetDataForEntry]
      @SalesPersonId   int=null, @SalesDate  date=null
AS
   SELECT DISTINCT 
       par.SalesPersonId, 
       pos.pointOfsalesID, 
       pos.pointOfSalesName, 
       ISNULL((SELECT SUM(SoldAmount) AS Expr1 
               FROM   
                  dbo.DailySales AS ds 
               WHERE  
                  (SalesDate = CONVERT(VARCHAR, @salesDate, 102)) 
                  AND (SalesPersonId = par.SalesPersonId)  
                  AND (PointOfsalesID = ps.PointOfsalesID)), 0) AS SoldAmount, 
   FROM   
       dbo.PointOfSales AS pos 
   INNER JOIN 
       dbo.PersonAndDistrict AS par ON pos.districtId = par.districtId 
   WHERE  
       (@SalesPersonId IS NULL 
        OR par.SalesPersonId = @SalesPersonId ) 
   GROUP BY 
       pos.districtId, 
       pos.pointOfSalesName, 
       pos.PointOfsalesID, 
       par.SalesPersonId 
   ORDER BY 
       pos.PointOfsalesID, 
       pos.districtId 

只返回800条记录（销售点）需要6秒以上！

那么，如何优化这个存储过程以在最短的时间内执行呢？

Answer 1

一般来说，我发现JOIN中的内联视图比SELECT子句中的表现更好。所以我会这样重写。

SELECT DISTINCT par.salespersonid, 
            pos.pointofsalesid, 
            pos.pointofsalesname, 
            Isnull(t.expr1, 0), 
            as soldamount 
FROM   dbo.pointofsales AS pos 
       INNER JOIN dbo.personanddistrict AS par 
         ON pos.districtid = par.districtid 
       LEFT JOIN (SELECT SUM(soldamount) AS expr1, 
                         pointofsalesid, 
                         salespersonid 
                  FROM   dbo.dailysales AS ds 
                  WHERE  ( salesdate = CONVERT(VARCHAR, @salesDate, 102) ) 
                  GROUP  BY pointofsalesid, 
                            salespersonid) t 
         ON t.salespersonid = par.salespersonid 
            AND t.pointofsalesid = pos.pointofsalesid 
WHERE  ( @SalesPersonId IS NULL 
          OR par.salespersonid = @SalesPersonId ) 
GROUP  BY pos.districtid, 
          pos.pointofsalesname, 
          pos.pointofsalesid, 
          par.salespersonid 
ORDER  BY pos.pointofsalesid, 
          pos.districtid 

另一种选择是移到下面的CTE，但这主要是一种风格，除非在SQL代码中重复内联视图。

WITH t 
     AS (SELECT SUM(soldamount) AS expr1, 
                pointofsalesid, 
                salespersonid 
         FROM   dbo.dailysales AS ds 
         WHERE  ( salesdate = CONVERT(VARCHAR, @salesDate, 102) ) 
         GROUP  BY pointofsalesid, 
                   salespersonid) 
SELECT DISTINCT par.salespersonid, 
                pos.pointofsalesid, 
                pos.pointofsalesname, 
                Isnull(t.expr1, 0) AS soldamount, 
FROM   dbo.pointofsales AS pos 
       INNER JOIN dbo.personanddistrict AS par 
         ON pos.districtid = par.districtid 
       LEFT JOIN t 
         ON t.salespersonid = par.salespersonid 
            AND t.pointofsalesid = pos.pointofsalesid 
WHERE  ( @SalesPersonId IS NULL 
          OR par.salespersonid = @SalesPersonId ) 
GROUP  BY pos.districtid, 
          pos.pointofsalesname, 
          pos.pointofsalesid, 
          par.salespersonid 
ORDER  BY pos.pointofsalesid, 
          pos.districtid