Have a transactional table like this (tblTrx):
TableID, int
ProductID, int
CreatedOn, datetime
Quantity, int
Size of table is getting to 2.5M records and will grow at about 1.5M/year.
Most queries are date related wanting to know what the product quantities were at a certain date so we generally query like this:
SELECT * FROM tblTable T
INNER JOIN
(SELECT ProductID, MAX(TableID) AS TableID FROM tblTrx WHERE CreatedOn > @TIMESTAMP GROUP BY ProductID) M
ON T.ProductID = M.ProductID
AND T.TableID = M.TableID
Clustered index is on TableID
Non-clustered index on CreatedOn
So, a couple questions:
1. Is this type of query optimal for getting this data?
2. Thoughts on indexes?
What I'm finding is that a lot of queries are doing Clustered Index Scans rather than Seeks and it's getting slow.
Thoughts?
