0

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.

2
  • What indexes are on the table? Jan 28 '15 at 13:50
  • @JohnM Great question I just thought it might be very relevant. Let me add them. Jan 28 '15 at 13:51
3

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.

12
  • Hmm it is a lot faster (6 times, as expected by your logic!), but it does not show the employees which have 0 for every work type. Do you see a way to get these as well? Jan 28 '15 at 16:53
  • 3
    The DATEDIFF(day, mt.ActionDate, GETDATE()) = 0 can be improved to mt.ActionDate >= CAST(GETDATE() AS date) AND mt.ActionDate < DATEADD(day, 1, CAST(GETDATE() AS date)) Jan 28 '15 at 17:43
  • 2
    And the SUM(CASE WHEN mt.owner IS NULL THEN 0 ELSE 1 END) can be simplified to COUNT(mt.owner) Jan 28 '15 at 17:45
  • 1
    @user2609980 - edited one more time to put the criteria in the join logic. Jan 29 '15 at 12:30
  • 1
    Wonderful to hear :) Jan 29 '15 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.