August 30, 2010 at 3:21 pm
Firstly the core tables:
EventTable:
* Id (INT)
* Moment (DATETIME)
LedgerTable:
* Id (INT)
* EventId (INT)
* StockId (INT)
* LocationId (INT)
* AccountId (INT)
* Quantity (INT)
Now at the moment the Event table has 600k rows and the ledger table has 2m rows; one event row will have 1 or more ledger lines associated with it. This is about 6 months worth of data and that will be a linear growth over time. Various reports look at this table to view quantities per location, per stock item, per client (a stock item belongs to a client) and then all of the above at a moment in time (i.e. want to view the quantity in the location by month).
The two tables are central to the business and numerous reports (used daily) are based on them.
Now I'm wondering the best way to deal with these reports. At the moment I have a fair few covered indexes (location+account, location+stock, eventid etc) with include columns (all have quantity, some have stock).
The problem is the nature of the ledger system (which we need, as we often need to view the state of location / stock at various moments in the past), no matter what we do we need to sum up all the data in the table. I'm not sure what to do from a performance point of view...
More Indexes: Obviously use more storage (not a massive issue, as in the grand scheme of things our 5gb database isn't massive I guess?), probably 10+ taking into account various reports. Fair bit of storage, not sure of performance implications for writes to the table?
Condense Data: Not something I really want to do, but could replace detailed data with more of a weekly version if the data is more than 6 months old.
Snap shot tables: Update a report type table off the data (updated daily by out of hours job). Would be used for all reports that don't need to be 100% up to date.
So just wondering on peoples experiences with ledger tables?
Thanks!
August 31, 2010 at 6:30 am
From the sounds of the system you describe, I think I would create a third table that is aggregated data of all the other information. You say that the reports aggregate across the entire data set, every time, but do the aggregations have to be real time? Meaning, if the data was a day or several hours old, would that work? If so, you could, depending on the amount of activity on the inserts, use a materialized view to aggregate the information. Or, if the activity is high, run an SSIS package to agregate the data into a table on a scheduled basis.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2010 at 5:34 am
Some reports need to be real time but most do not (a day late isn't a killer).
I do like the idea of a view, but do I have any control over when it's materialised? If it gets redone every insert then that would be no good as new rows are added every minute, but if I can 'recompile' a view once a night that would be perfect.
September 1, 2010 at 6:01 am
goksly (9/1/2010)
Some reports need to be real time but most do not (a day late isn't a killer).I do like the idea of a view, but do I have any control over when it's materialised? If it gets redone every insert then that would be no good as new rows are added every minute, but if I can 'recompile' a view once a night that would be perfect.
No, a materialized view is going to update constantly. It's probably not the way to go. If you can afford a day, then I'd go with an aggregate table. You can load it up during a slow time & then it'll be available for use later. It's probably doable to set it up for every 4-6 hours without seriously impacting performance too if a day is too long.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2010 at 12:51 pm
a scholar and a gent.
*tips hat.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply