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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply