Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Some Background Info

We have a set of tables that holds all the transactions for our system, TransactionHeaders and 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
...)  

The Issue

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 TransactionHeader on TransactionDate and partition TransactionDetail on 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.

The Question(TL;DR)

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.

share|improve this question
Reports based on Store and nothing else? From a business perspective, that doesn't really make sense to me. I would expect to answer a question like "How much did Store A sell in the last 3 months?" -- in other words, it would always have a time constraint as well. Is that not the case? – Jon Seigel Aug 2 '12 at 15:56
@Jon Seigel, it is the case, I'll edit the post to reflect that. – 00000400000135 Aug 2 '12 at 16:00
What does the SQL Statement look like that you are using to join the two tables? – Gittins Aug 2 '12 at 18:08

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.