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.
up vote 1 down vote favorite
1

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance improvement is really fantastic. However I'm getting each day around 30-50 Mio new rows that I have to load as fast as possible and make available for the users. I have seen recommendations to make for each day on partition and do partition swapping. This would result for the data I have at the moment in 4380 partitions. Even thinking of the scripts to create this makes me wonder if this is really a smart approach.

What would be best practice in such a situation ? Does disabling and rebuilding the columnstore index only rebuilt for the added 30 - 50 mio rows, or over the whole 22 Bn rows ?

The time frame I have to add these daily new rows is around 30-45 min max.

share|improve this question
Will your time frame be 30-45 minutes when you need to add 75 million, 100 million, 200 million rows per day? At some point this requirement might get a little ridiculous. Also do you have the space to hold two copies of the data? It's possible you could create a view that spans one copy of the columnstore data and today's raw, non-columnstore data, while you simultaneously rebuild the other copy, then when that's done, you swap – Aaron Bertrand Jan 10 at 12:47
the 50 mio rows is the max I had per day over the last twelve years, so I'm pretty confident it won't explode beyond 100 mio anytime soon. At the moment the loading takes around 10 min, so 30-45 is still plenty. Only the columnstore index operation is now time critical issue.Space I have plenty as well (40 TB still free) – nojetlag Jan 10 at 15:36

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.