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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Large Number of Tables and Performance

Author  Topic 

waleedfi
Starting Member

3 Posts

Posted - 2008-01-25 : 08:04:33
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.

I would really appreciate if you can give me some advice and if you have any good links that would be great...

Waleed Eissa
http://www.waleedeissa.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-25 : 08:34:51
10000 to 20000 tables???
what kind of app is this?
i'd say this is definitly not good design...

can you tell what your requirements are?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

waleedfi
Starting Member

3 Posts

Posted - 2008-01-25 : 08:44:19
In my application, I'll have forums used by different groups of people (each group will have their own forums). I will start from 1000 or 2000 groups with the potential to grow until 5000 groups (it's guaranteed that I won't exceed that number of groups, 5000, based on the nature of my application). I was thinking that having all the forum posts in one table will cause many problems like having a very large index and slow search among other problems (as I want to have many indexes on the table like the PostID, ForumID, GroupID and PostDate), so, I was thinking that it might be better to have a separate table for each group's forum posts in order to have small indexes so that I have faster searches and inserts take less time (esp. that some groups are expected to have a large number of posts per day) and also to be easier to move the tables to other database servers in case the application grows and so the web farm.

Waleed Eissa
http://www.waleedeissa.com
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-25 : 09:05:33
well number of tables don't have any significant imapct on performance.
just be sure to have plenty of RAM.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-25 : 11:46:32
10k tables.....in 1 database.
might be far simpler (and more scalable) to have 1 table in each of 10k databases (1 database per forum)

You're looking at "coding hell" going with the former.
Go to Top of Page

newsqlguy
Starting Member

13 Posts

Posted - 2008-01-25 : 12:23:30
IMO, using one table with the appropriate indices will give you almost same performance. before you jump into hell of coding with thousands of tables and databases please analyze the situation with the following questions:

1. How many groups? (20,000)
2. How many users?
3. How many records on average per group?
4. How many inserts and updates you will get per hour/day? (affects locking and index update)

There are many things to do like horizental partitioning, putting indices on separate file groups and hardware.

In any case, going with thousands of tables sounds very sour to me.

Good luck
Go to Top of Page

waleedfi
Starting Member

3 Posts

Posted - 2008-01-27 : 09:37:31
Thanks a lot for all your replies guys. Well, I could probably be wrong about having a separate table for every group but I don't exactly understand what you mean by having a problem with the maintenance, I assume you're talking about the database side here not the coding part. As to coding there won't be any problems as everything will be planned and automated, once a group is created, its own table will be created and when trying to retrieve data for a specific group the table of that group will always be looked up so I don't really see a problem with the coding part, but probably there will be a very bad problem as to query plans caching (which is on the database side) as query plans will be cached for every table, a total mess! This reason alone is enough to make me drop the whole thing and go with one table.

As to the questions posted by newsqlguy:
1. How many groups? (20,000)
Yes, but that's the maximum and I won't start with this number (probably only 5000 or less)
2. How many users?
will differ from one group to another, some groups could have up to a million users (it's guaranteed though that this number won't be exceeded by many) while some other groups would have a few thousands or hundreds. About only 500 or less could have many users up to half a million or a million.
3. How many records on average per group?
The more users per group the more records
4. How many inserts and updates you will get per hour/day? (affects locking and index update)
Depends on how many users are online (group members will be from all over the world so this will be constant during the day)

One last thing to mention here, the whole idea of separating things came reading about scalability scenarios on http://highscalability.com (a great site by the way if you're interested in scalability), more specifically read about sharding.

Thanks again for all your help...

Waleed Eissa
http://www.waleedeissa.com
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-28 : 07:42:41
you want to avoid
a) dynamic sql
b) "auto generated code"

for security + performance + maintainability reasons.

and
select * from table_a0001
select * from table_a0002
select * from table_a0003
select * from table_a0004
etc...
will be a pain when it comes to maintain-ability of code.

For the number of users and transaction levels you are looking at servicing, caching of data (and SP execution plans) and ultra-efficient StoredProcedures should be king.

Go to Top of Page

rprice
Starting Member

3 Posts

Posted - 2008-01-29 : 05:17:19
Go with one table. Provided the structure is reasonably smart and the right indexes are in place then SQL will not do much more work as the number of rows increase. This is down to the way SQL handles it's indexes. The index structures are stored in 8k pages and the number of pages SQL needs to read to find a row or series or row does not increase a lot as the number of rows increase. What you might want to consider it partitioning the database with different file groups for older data.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 05:47:40
Why one table per group? Add a "GROUP" column to the existing one.
Please read this page carefully http://www.datamodel.org/NormalizationRules.html



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -