February 17, 2009 at 9:52 am
I have a scenario where I have 3 databases with data that I'd like to combine for reporting.
The stored procedures that calculate the data for the reports currently run on the fly and do a mix of joining and aggregating the data from the original 3 sources. Sometimes the queries take a few minutes to run. The data updates in 1 of the 3 databases very often.
I'm trying to devise a plan that would create report tables on a scheduled time (ex. every 4 hours). These report tables would contain the joined/aggregated data that takes a long time to compute on the fly.
My problem is, the 3 databases that I'd like to pull data from contain huge amounts of data, so I don't want to delete and repopulate the entire report tables every time the calculations are run.
Instead, I am looking for a way to only recalculate the parts that have been changed since the last update.
Could someone help me figure out
1) How should I move the data around? A scheduled job to combine the data I need from 3 seperate databases into one? Replication?
2) How can I go about only recalculating data that has changed since the last calculation? It's not feasible for me to modify the original data collection points to add any kind of trigger or flag to show that the data has changed.
Thanks
February 17, 2009 at 2:07 pm
Regarding 1, yes it could be a schedule stored proc that brings the data together into the 1 reporting table.
On 2, the big issue is if data is updated or only added? If data is updated randomly you have probably no other way than running this on the whole table. You might be able to do some row checksum's to see which changed, but it still would be querying basically the whole table.....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply