Sql-server – How to increase the performance of this 3 minute reporting query

sql-server-2008t-sql

We have a table MyTable with about 10.000.000 records which contains a record of all transactions processed in our application. This table has the user Owner who did the transaction, the date ActionDate on when it took place, the status Status and the order type OrderType of what type of transaction has been performed.

To generate a report where for every user the amount of transactions it did for every order type is displayed we have the following query as part of a stored procedure:

SELECT mt.Owner AS User,

(SELECT COUNT(ID) 
FROM `MyTable` mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner = ca.Owner
AND mt.OrderType = 1
AND mt.Status <> 3
AND mt.Status <> 2  ) AS FIRST,

(SELECT COUNT(*) 
FROM MyTable mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner = mt.Owner
AND mt.OrderType = 2
AND mt.Status <> 3
AND mt.Status <> 2 ) AS SECOND,

(... Do this for six order types where mt.OrderType and the AS name changes ...)


(SELECT COUNT(*) 
FROM MyTable mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner =  mt.Owner
AND mt.Status<> 3
AND mt.Status<> 2 )
AS TOTAL

FROM MyTable mt
WHERE mt.Owner IS NOT NULL
GROUP BY mt.Owner

Some relevant information on indexes:

There are three indexes on MyTable. One on ActionDate, Status, OrderType, one on Id, and one on Owner.

These are created as follows:

CREATE NONCLUSTERED INDEX AnExampleOfAnIndex ON dbo.MyTable (ActionDate ASC, 
                                                             Status ASC, OrderType ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) 
ON [PRIMARY]

The query takes three minutes. Does someone see how it can be improved? Thanks.

Best Answer

Instead of 7 Subqueries, you can just use one query. This should be a large performance gain:

SELECT owner [User],
       SUM(CASE WHEN OrderType = 1 THEN 1 ELSE 0 END) [First],
       SUM(CASE WHEN OrderType = 2 THEN 1 ELSE 0 END) [Second],
...
       COUNT('x') [Total]
FROM MyTable mt
WHERE status NOT IN (2,3)
  AND ActionDate >= CAST(GETDATE() AS DATE) 
  AND ActionDate < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
GROUP BY owner

You'll also want to make sure that DATEDIFF(day, ActionDate, GETDATE()) = 0 is doing what you want. What you're actually getting is anything that happened on the day that the date is run- the period between the start of the current day and the current time. If you're looking for anything else, say, transactions that took place YESTERDAY, i.e. before midnight this morning and after midnight the previous morning, you'd want to use different logic.

In order to get all users and the related records for them, whether or not there are any related records in the timeframe, you'd have to use a subquery failing another source for the users:

   SELECT  user_lst.owner [User],
           SUM(CASE WHEN mt.OrderType = 1 THEN 1 ELSE 0 END) [First],
           SUM(CASE WHEN mt.OrderType = 2 THEN 1 ELSE 0 END) [Second],
    ...
           COUNT(mt.owner) [Total]
    FROM (SELECT DISTINCT owner FROM MyTable) user_lst
    LEFT JOIN MyTable mt
      ON mt.owner = user_lst.owner
      AND mt.status NOT IN (2,3)
      AND mt.ActionDate >= CAST(GETDATE() AS DATE) 
      AND mt.ActionDate < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
    GROUP BY user_lst.owner

Also note that we had to change the aggregate logic for [Total], as otherwise COUNT('x') would return 1 as [Total] for users with no activity in the timeframe.

Related Question