Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-24 : 08:17:35
|
| ryan writes "Question simple: (question in detail below)When is it more efficient to break a multi-million row table into seperate tablesi.e instead of:tableit would be table1table2table3And what is the best way to query these tables(ie variable named tables using stored procedures:ie:Exec sp_executesql orschema_table_name=@procparamtablename(if this is the case would appreciate a example using joins)Questions Details:HelloI am in the middle of building a real estate website. In the backend there is a realtor section and each relator can have a contact list via pkid->fkid. Each realtor belongs to a companyThe contact list could potentially get over 5 million records. I know ms-sql 2005 can handle this but I would like to make it as efficient as possibleRight now contacts are stored in a table called contacts with a foreign key to the company the realtor belong to they belong to:My thinking was to split the contact table into seperate tables based on the company id:contact(table)1contact(table)2And what is the best way to query these tables(ie variable named tables using stored procedures:ie:Exec sp_executesql orschema_table_name=@procparamtablename(if this is the case would appreciate a example using joins)" |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-01-24 : 10:38:19
|
| You imply it's a 2005 database. In that case, lookup "Partitioned Tables and Index" in Books Online. However, 5m rows is not really a huge number, and I wouldn't necessarily advise doing it for the sake of it. First consider the queries that will be run against this table, and how sensible indexing will help. Definitely don't go down the proposed route of having separate physical tables referenced via dynamic sql!Mark |
 |
|
|
|
|
|
when to clone database tables