Take the 2-minute tour ×
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.

We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from available data. we've some concerns and require technical input and advices.

  • What impact we may see in future for very large table? Is there any limitation on number of records in one table or size of table?
  • What is the best way to index large data tables like this?

Any suggestion for optimization and performance of database is also appreciated.

share|improve this question

3 Answers 3

SQL Server DENALI will have column store indexes but this won't come until the end of this year or early next year but from what I have seen so far (very little), it will definitely help.

In SQL Server partitioning is your best friend here. And to your question abt the limitation of # of rows in a table, it is limited only by storage space.

http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx

As tables grow, adding and updating the data will take some time and you may want to look at using minimal logging if you are using SQL Server 2008.

http://sqlcat.com/whitepapers/archive/2009/02/15/the-data-loading-performance-guide-2008.aspx

And for large tables, updating indexes frequently can take significant times and have seen cases where updating the statistics will give you a quick boost instead of frequent index rebuilds.

share|improve this answer
    
thanks for your links. both links give some good technical inside. We can run statistics as schedule job but it would be helpful if you've any article for comparison about performance or stats. –  kodvavi Jun 1 '11 at 21:51

According to the description of your needs I'd recommend you to use a Column-store database.

They are recommended for Analysis use. I can see the sql-server tag in your question, I know there is a way to design your tables in such a way SQL-Server will act as a Column-store database, but it won't be as efficient as using a real Column-Store db.

Column store db are optimized for indexation and very large table. I would recomment you LucidDB so you can have a quick view of what those databases are capable of.

share|improve this answer
    
Column-store table is the new thing for me. Let me do some research on this. thnx anyway. btw we're using MS SQL server 2008 –  kodvavi Jun 1 '11 at 21:31

Check out the posts for VLDB Week by Robert Davis over at SQL University. He covers a lot of these questions: http://sqlchicken.com/sql-university/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.