1

I have a critical performance scenario that,

I have to use more than 100 schema in a database. Each schema having more than 50 tables and few views. I need to use join queries for communicating between tables in these schema.

I would like to know any performance issue there by using this much schema for intercommunicating the tables in one MSSQL database? That means issue in memory, CPU, or any other resources. Thank you.

felix
  • 121
  • 5
  • Why dislike this question??? any issues with the question??? – felix Sep 17 '14 at 09:46
  • 1
    The question is too broad. A discussion of all of the factors that could affect performance in a large MySQL installation is out of scope here. – Andrew Schulman Sep 17 '14 at 10:31
  • 3
    @AndrewSchulman: Two things. 1) The question is tagged with sql-server, not MySQL. 2) He's not asking about all the factors that could affect performance. He's very clearly asking about one factor (i.e. number of schemas) in his installation that he has a question about. – Ben Thul Sep 17 '14 at 12:10
  • @BenThul you are correct. I was wonder about his comment. – felix Sep 17 '14 at 12:44

1 Answers1

3

A schema is just a container (i.e. a place for objects to live) in SQL Server. That said, there should be little to no performance implications with having a large number of schemas. That said, 100 sounds like a large number. Rhetorically, what are you trying to accomplish by having so many?

Ben Thul
  • 3,024
  • 17
  • 24
  • Actually I have to create same structure of tables and views for different applications. And This schema creation also a dynamic process. In that scenario I have to test whether this approach is good or not. Else I can create an extra table column as application_Id for mapping the application to tables. So is this a good approach??? – felix Sep 17 '14 at 12:48
  • 1
    No. A good approach is having one database per application – MichelZ Sep 20 '14 at 12:11
  • 1
    I'm not so sure, michelz. I think the additional column approach will make his pattern of having to get the data from multiple "applications" much less kludgy. – Ben Thul Sep 20 '14 at 12:56