Misused database view (SQL Server) - how to optimize

Question

I need some advice on how to optimize a SQL query, since I am not an expert in SQL.

A customer of mine has some SQL code that runs on SQL Server, and they want it to run faster. It used to run fast, but now it does not.

The query is using the same huge view 6 times to extract data for each day and it is the primary cause for it to be very slow, running for 25+ minutes.

The main issue seems to be that the view is called so many times with so little difference between the runs.

Is it possible to re-write the query so it only calls the view once and then extract the data for the week from that and can it be done without using temp tables?

-- Imput: @justWeekEnding - the date for the end of week report
-- Input: @customerid - the unique customer ID for whom the report is generated
-- Expected output: Sales report, showing profit margin for the selected customer per shop per day-of-week
--
-- dbo.charting_salesdata: View producing a sales report across all stores per date per customer
-- dbo.stores: Table containing store info
-- dbo.receipts: Table containing all receipts
-- dbo.storecategories: Table containing all store categories

 DECLARE @justWeekEnding AS date;
 SET @justWeekEnding = CONVERT(date, @weekEnding, 101);

 SELECT 
     Query.StoreName AS [Store Name], Query.POS as [POS Name], 
     Query.saturday, Query.sunday, Query.monday, Query.tuesday, 
     Query.wednesday, Query.thursday, Query.friday
 FROM
     (SELECT     
          dbo.stores.NAME AS [StoreName],
          receipts.POS AS [POS],
          (SELECT COUNT(percentage) 
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = Dateadd(d, -6, @justWeekEnding)
           GROUP BY receiptid, dateofsale) AS saturday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = Dateadd(d, -5, @justWeekEnding)
           GROUP BY receiptid, dateofsale) AS sunday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = Dateadd(d, -4, @justWeekEnding)
           GROUP BY receiptid, dateofsale) AS monday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = Dateadd(d, -3, @justWeekEnding)
           GROUP BY receiptid, dateofsale) AS tuesday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = Dateadd(d, -2, @justWeekEnding)
           GROUP BY receiptid, dateofsale) AS wednesday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = Dateadd(d, -1, @justWeekEnding)
           GROUP BY receiptid, dateofsale) AS thursday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale = @justWeekEnding
           GROUP BY receiptid, dateofsale) AS friday,
          (SELECT COUNT(percentage)
           FROM dbo.charting_salesdata WITH (nolock)
           WHERE dbo.charting_salesdata.receiptid = dbo.receipts.receiptid
             AND dateofsale BETWEEN Dateadd(d, -6, @justWeekEnding) AND @justWeekEnding) AS TOTAL
       FROM       
           receipts WITH (nolock)
       INNER JOIN 
           dbo.stores WITH (nolock) ON dbo.receipts.storeid = dbo.stores.storeid
       INNER JOIN 
           dbo.storecategories WITH (nolock) ON dbo.stores.storecategoryid = dbo.storecategories.storecategoryid
       WHERE      
           1=1
           AND customerid = @customerid
           AND dbo.receipts.isdeleted = 0) AS Query
WHERE 
    Query.TOTAL > 0
ORDER BY   
    Query.StoreName, Query.TargetName

Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this everywhere - quite the contrary! — marc_s, Jan 11 '18 at 05:49
Thank you for the advice. The SQL I inherited have them everywhere... I'll start to get rid of them. What could be the reason for using them everywhere? — T.H.P., Jan 12 '18 at 19:08

score 1 · Accepted Answer · answered Jan 11 '18 at 01:21

1

Yes, it's possible.

Group everything by dateofsale, receiptid, dateofsale with applied filter for dates and then PIVOT results (rotating rows (7 days of week) to columns)

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

answered Jan 11 '18 at 01:21

Anton

2,846
1
10
15

That sounds like a great solution and just what I needed. Thank you very much. – T.H.P. Jan 11 '18 at 02:17

score 1 · Answer 2 · edited Feb 15 '18 at 05:48

1

Create a CTE (Common type expression) for the view on group by columns dateofsale, receipted, dateofsale with a filter of @justweekEnding date.

Then you can create a query which joins other tables with CTE and do Pivot to get all the columns like Saturday, Sunday, Monday

edited Feb 15 '18 at 05:48

marc_s

732,580
175
1,330
1,459

answered Jan 11 '18 at 03:59

niks

125
1
9

Thank you for your input. I have not used a CTE before, I will look into it. – T.H.P. Jan 12 '18 at 19:10

Misused database view (SQL Server) - how to optimize

2 Answers2