7

I know that in SQL Server, the maximum number of "objects" in a database is a little over 2 billion. Objects contains tables, views, stored procedures, indexes, among other things . I'm not at all worried about going beyond 2 billion objects. However, what I would like to know, is, does SQL Server suffer a performance hit from having a large number of tables. Does each table you add have a performance hit, or is there basically no difference (assuming constant amount of data). Does anybody have any experience working with databases with thousands of tables? I'm also wondering the same about MySQL.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Kibbee
  • 65,369
  • 27
  • 142
  • 182

5 Answers5

8

No difference, assuming constant amount of data.

Probably a gain in practical terms because of things like reduced maintenance windows (smaller index rebuilds), ability to have read-only file groups etc.

Performance is determined by queries and indexes (at the most basic level): not number of objects

gbn
  • 422,506
  • 82
  • 585
  • 676
4

In terms of the max number of tables I have had a database with 2 million tables. No performance hit at all. my tables where small around 15MB each.

Andy Ansryan
  • 41
  • 1
  • 1
1

I doubt SQL Server will have a performance problem working with thousands of tables, but I sure would.

I've worked on databases with hundreds of tables in SQL Server with no problems, though.

Paul Lefebvre
  • 6,253
  • 3
  • 28
  • 36
  • Had to read your first sentence a couple times. I understand where you're coming from, but these would basically be a lot of tables all with the same structure, and not something that would be managed individually. – Kibbee Mar 04 '09 at 15:21
  • 1
    If the tables are generated programmatically then you as a developer can deal with them programmatically too. No performance problems (at least not vocationally) – Davos Oct 22 '14 at 06:12
0

In my experience I dont think that the number of tables will hit the performance. But then you should be able to justify why you are having so many tables in the database. That is because having so many tables at the database side will also effect the work of developer at the server side.

IMO if you divide the tables on the basis of functionality then you can not make the life of developer easy but also have performance gain in your application because you have fixed tables from where you suppose to get the required data.

Say like you need to store sales,purchase,receipt and payment details. All of the them have the same table structure then instead of storing them in single table you could store them all separately in separate tables. with these you can get all the details for the sales in single table, for purchase in its single table and likewise. thus it can help in improving the response time of database tier of the application which is one of the most slowest component in all web tiers...!!! ofcourse we imporve upon the performance of database by SQL quires but then such structuring can also indirectly help you improve upon the database performance.

svg
  • 499
  • 5
  • 7
0

SQl Server can suffer a larger performance hit by using tables with many, many columns instead of breaking out a related table (even one with a one-to_one relationship). Plus a wide table likely can have problems when the data you want to input exceeds the number of bytes that you can store for a column. You can create a table that has the potential to store, for example, 10000 bytes but you will still only be able to store 8060 bytes.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • This is useful information, but doesn't at all answer the question being asked. – Kibbee Mar 04 '09 at 15:34
  • BUt it does as the reason you ask if too many tables will create a performance problem is that you are considering consolidating them rather than create so many which in fact creates a much larger performance problme and the person should be aware of that. – HLGEM Mar 04 '09 at 16:41