Some Background Info
We have a set of tables that holds all the transactions for our system,
TransactionDetails. There are roughly 80k Transactions a day which translates to 80k rows in
TransactionHeaders and 900k rows in
TransactionDetails being burned daily. At current, the
TransactionHeaders has about 10 million rows and
TransactionDetails has about 110 million rows. We use the data for general reporting.
dbo.TransactionHeaders ( TransactionHeaderId int identity, TransactionHeaderTypeId int not null, StoreId int not null, TransactionDate date not null, TransactionTime time not null, ...) dbo.TransactionDetails ( TransactionDetailId int identity, TransactionHeaderId int not null, ItemUPC NCHAR(14) not null, Price NUMERIC(16,2) not null, ReplicationDate datetime not null ...)
Querying has become cumbersome. It takes a very long time to access the sales of a given store or a given item for any period.
What I've Tried
I have tried to bring
TransactionDate down to the
TransactionDetails table in order to partition it on the date with one partition per day. This worked great for finding the sales of an item. The problem is that many of the reports require the StoreID in addition to being over a specific date range.
Given that adding more information from the
TransactionHeader table to the
TransactionDetail table breaks the pattern, I'm hesitant to denormalize the tables into one table due to storage concerns.
I've had the idea to partition
TransactionDate and partition
TransactionHeaderID. In theory this makes the queried data significantly smaller and reinforces the pattern by making the details only reasonably accessed via the header information.
Is there a preferred, correct, standard, etc. method for dealing with tables in the Master/Detail pattern in order to increase performance? Partitioning one or both tables? I'd like to avoid denormalizing if at all possible.