August 14, 2008 at 9:03 am
I have a VLDB with approx 9 Billion Rows. (Single Table) and I need some suggestions for speeding up some operations. Currently we have this table partiioned by day (approx 80 million records per day) and we are currently rolling 120 days. We havent completed backfilling yet, and will end up with about 12 Billion Rows overall. This will remain static as we move forward. We have the Transaction Log and DB on separate SCSI Raids.
Once a month we need to grab a range of dates (monthly snapshot) and compute some totals on the data.
OK the question:
Currenty grabbing one month of data and computing the totals is taking about 10 hours (Est Execution plan),
Should I create a Cube and do the computing of the Totals etc from that, instead of a script running monthly?
Should I create a Slice of Data for that month and work from that? (Build an Index etc)
I have some bigger brains here working on this, but I thought it was an interesting issue as I have never seen such a large DB...!
We don't index the table because it is basically a "Bucket" and not really meant for analysis.
August 14, 2008 at 9:51 am
you might want to create a reporting database (readonly snapshot / mirror / etc )
And put indexes on the table in that database
A cube would also be a choice.
Basically, it all depends on how often you need to refresh the cube / reporting db.
August 14, 2008 at 10:05 am
Well it is a monthly item, however we have some space concerns, my DBA says that creating slices of this table will be counter productive because he has partitioned the table.
I have created some SSAS Cubes for much smaller DB's, and I am not sure how large this cube will be when finished. My guess is processing such a large cube will take many hours a day.
thanks for the feedback.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply