September 9, 2009 at 8:25 am
Hello all,
I need to create a huge table (more than 500 million rows and 50 columns) in SQL Server. One of my coworkers told me that I should split this huge table into several small tables which have exact same structure with the big table. By doing this, the performance of database will be enhanced greatly.
Is that so and why? (My coworker said he just heard somebody said that and dont know why)
Thanks a lot in advance.
September 9, 2009 at 8:31 am
Wayne (9/9/2009)
Hello all,I need to create a huge table (more than 500 million rows and 50 columns) in SQL Server. One of my coworkers told me that I should split this huge table into several small tables which have exact same structure with the big table. By doing this, the performance of database will be enhanced greatly.
Is that so and why? (My coworker said he just heard somebody said that and dont know why)
Thanks a lot in advance.
Not exactly sure what your co-worker is going on about, what is the purpose of the table. if the data can be split by a range like a date range for example. then you could keep certain ranges in different tables, to make querying easier. but there are better ways of doing this. by using partioning for example.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 8:35 am
Thanks a lot fox for your reply.
September 9, 2009 at 8:36 am
They may also mean splitting data out into other tables and using a Primary/Foreign key to join them - eg taking someone's address and putting it into an address table.
Can you upload a copy of the table Schema and give us an idea of what sort of data is going in there - eg loads of historical static data or a constantly changing set of active data
September 9, 2009 at 8:46 am
was he talking about partitioning? if so then yes that can greatly improve the performance of your database
September 9, 2009 at 8:51 am
Wayne (9/9/2009)I need to create a huge table (more than 500 million rows and 50 columns) in SQL Server. One of my coworkers told me that I should split this huge table into several small tables which have exact same structure with the big table.
As your co-worked already pointed out he is just saying what he heard ... the old "some guy told a friend of mine".
Look at your design, if your design calls for a single large entity a.k.a table then implement as a single table.
You may consider partitioning IF an archive-and-purge process would benefit from it BUT take into consideration partitioning strategy may end up hurting your performance rather than improving it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 8:55 am
Animal Magic (9/9/2009)
was he talking about partitioning? if so then yes that can greatly improve the performance of your database
he didnt mention it, but if you have a large table, then partitioning can help greatly. splitting a table into several smaller ones doesnt really help that much, bearing in mind that a single table query in theory will be quicker than a multi table query.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 8:59 am
We are guessing what he may have meant but he may have meant...
Creating a filegroup and adding files to that file group which exist on separate drives and then putting the table on that filegroup.
September 9, 2009 at 9:15 am
Seth Lynch (9/9/2009)
We are guessing what he may have meant but he may have meant...Creating a filegroup and adding files to that file group which exist on separate drives and then putting the table on that filegroup.
Nope, just re-read what he posted and I am sticking to my guns here, he explicitly said TABLE 😛
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 9:19 am
Ok - I was guessing at what his colleague might have actually heard rather than what he said he heard:hehe:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply