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.

currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:

  • Size: HDD has > 4tb to store filegroup if using partition tables.

  • Performance: Using partition tables. In diagram, we will not create relationship among tables. I heard that it will help us query data faster.

But, in reality, I don't know it is good solution or not, please advice me with some concerns:

  1. if I have 100 milion records in each tables, and using parttion table, each quarter is 1 table/part. So, when I need to report in 1 year, how is its performance to query/filter data?

  2. With 100 milion records, it is posible to query data faster with no relationship?

  3. How about insert data with checking exist data first? it will take a long time to check duplicate before insert?

  4. How about replicate data by using partition table?

I know that, some ways, this is the job of DBA, but I readly want to understand them, please advise me.

I'm very appriciate for you helps.

Thanks.

share|improve this question

migrated from stackoverflow.com Aug 3 '12 at 15:53

This question came from our site for professional and enthusiast programmers.

2  
I don't think this question as asked, cannot be answered. There is insufficient detail to provide a reasonable response. Some questions involved in determining performance, or capability to provide performance. Some relevant factors are: How many tables; how many key references; proportion of queries to updates; rate of amendment; .... and the list goes on. So the answer to your question is "It depends". –  Chris Walton Aug 3 '12 at 4:35
    
but if you've already built one db by using partition tables, I think you must know its performance in query data or insert/update/delete data (as my concerns 1. and 3.), right? –  Thang Lang Aug 3 '12 at 7:10
add comment

1 Answer 1

I think you're having a hard time with this because I'm not sure you're looking at it from the right perspective. Database performance is a big topic and generally depends on an enumerable number of factors. But table partitioning is only addresses parallelism across cores vs. parallelism across threads. Generally speaking, the bottle-neck is not going to be there.

Let me give you some advice. Don't worry about partitioning - leave it in whatever state it's currently in - but do focus on the following.

  1. What's more important, reads or writes? If it's reads then ensure the queries you are performing against the database are optimized by building covering indexes where you can - reading the data page is expensive. If it's writes then be careful how many indexes you build because of the shear size of your data, so only build what you have to because rebuilding the index is expensive if there are a lot of transactions.
  2. Key constraints. Do not go down the road of not building proper foreign key constraints. When you build a foreign key it will build an index, you do not want to build in the referential integrity yourself, there is no performance bottle-neck with foreign keys. If you are using a database engine where foreign keys are a bottle-neck you should probably consider a different engine.
  3. Last but not least, tune your database to your usage. A substantial amount of usage statistics are needed to understand exactly how to tune your database and because of the size of your database it will need tuned regularly.

I'm sorry if this feels nebulous, but you have a huge task before you and database optimization is a regular process on a database of that size. So, in short, the aforementioned items will start you down the right path but there is no answer to your whoas.

share|improve this answer
1  
"When you build a foreign key it will build an index" -- this is not true for all DBMSs, but I agree an index should exist. Maybe state this as "When you build a foreign key, also make sure to build an index". –  Jon Seigel Aug 3 '12 at 16:09
add comment

Your Answer

 
discard

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