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
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.