Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2003
    Posts
    21

    Unanswered: Large Number of Tables and Performance

    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...

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it all really depends on what the nature of the system is. is this primarily a reporting DB, an order entry DB, or are we using it for both?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think that you are pushing\ exceding the limit for SQL Server there. I don't know what it is off hand since I have never got even close to the limit.

    TBH any design where you are counting tables in the thousands sounds very suspect indeed. It certainly doesn't sound scalable....

  4. #4
    Join Date
    Apr 2003
    Posts
    21
    Actually it's a website. In my application, I'll have forums used by different groups of people (each group will have their own forums). I will start from 10000 groups with the potential to grow until 20000 groups (it's guaranteed that I won't exceed that number of groups, 20000, 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.

  5. #5
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by waleedfi
    Actually it's a website. In my application, I'll have forums used by different groups of people (each group will have their own forums). I will start from 10000 groups with the potential to grow until 20000 groups (it's guaranteed that I won't exceed that number of groups, 20000, 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.
    Don't underestimate the power of SQL Server. Also, don't underestimete the bad performance caused by inexperient professionals in all fields of TI.

    With that data, and system, architecture you are doomed to failure.

  6. #6
    Join Date
    Apr 2003
    Posts
    21
    Thanks for the very valuable help, you're simply telling me it's bad without mentioning any reasons or ideas on how the design should be!

  7. #7
    Join Date
    Jul 2007
    Posts
    96
    Implyed in my reply is the suggestion that SQL Server can handle your estimated load, and much, much, much, more. Further than that I can only speculate on a possible solution. I don't have enough data to propose anything.

    For instance, you mention "(esp. that some groups are expected to have a large number of posts per day)". What's your notion of large number of posts? 10? 100? 1000? 1000000? 1000000000?

    What kind of isolation / access do you intend to provide to the group owners? Total isolation? Access to the database for customization?

    What kind of data will actually be stored in the database for you to consider such radical approach? Probably it would be useful to us to have some DDLs to consider and suggest further works / enhancements.

    What kind of experience do you have in software development? Database administration? Data architecture? If you are expecting to store 1000000000 posts per year maybe you should consider hiring some professional help.

    Don't take my replies as rude. The intent of my previous post was to see if you could come back with a more elaborate plan and ask for some specific guidance, as I'm sure you will do now

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think you will find moving parts of your data to remote servers will be a great deal of trouble. Also, the assertion that a smaller index is a better index is at least partially mistaken. While it is good to try to limit the width of the index key, there is no limitation on how many keys you can have. SQL Server's indexes (as well as oracle's MySQL's, Sybase's, etc.) build a tree structure for an index. The depth of this tree structure will only grow logarithmically compared to the number of rows in the table. If you are currently having problems with "large indexes", I would suggest you make sure the query is even using that index.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One table will both process individual queries faster and scale up better than 10K or more tables will. It will also require roughly 10K time less maintenance. The only place having one table will cause you more pain than 10K tables is if you are forced (by political or security issues) to scale out.

    You don't need to take my word for it because you can prove this very quickly and easily by using a web farm with a load testing tool such as Load Runner or even by simulating the load tester by spinning up all of the threads you can using multiple instances of SQLCMD.EXE.

    -PatP

  10. #10
    Join Date
    Apr 2003
    Posts
    21
    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.

    One last thing to mention here, the whole idea of separating things came from 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...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •