SQL Server stored procedure with subquery Optimization

Question

I have four tables:

1. SalesPerson (personId int ,PersonName nvarchar(20)..)
2. personAndDistrict (autoId int,PersonId int,districtId int):every sales man working in a specific district(s)
3. PointOfSales (SalesId int,pointOfSalesId int,districtId int,pointOfSalesName nvarchar(20),...) every district has many pointOfSales'.
4. DailySales (SalesPersonId int,SoldAmount float,PointOfSalesId int, salesDate date,..):Daily Sales data.

To save the daily sales for every sales person I need first to show a list for his all points of sales with sum (if any) of sold amount for each.

So, what we need is to show all Point Of Sales for a specific salesperson for a specific date with the total sold Amount for points.

I have the stored procedure :

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 

to return only 800 records (point's of sales) it take more than 6 seconds!

So, how to optimize this stored procedure to be executed within minimum time?

Answer 1

In a general way I find that inline views in a JOIN perform better than in the SELECT clause. So I would rewrite this way.

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 

Another choice is to move to a CTE below but that's mostly a style thing unless the Inline view is repeated in the SQL code.

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