This is a database where users create a 'project' and work on it. We have a table for project
and various other tables that contain different attributes for project (multiple rows for each project from 2-30K rows). All of them contains projectID
as link to project
. Currently the database is 400GB.
We are trying to create a schema for each project wherein each schema would contain all attribute tables. Whenever a project is created, it would get a new schema. This would be mean each table would contain at max 30K rows which would improve select
performance. We would be using dynamic SQL in our queries to read/insert.
I feel like this would not be proper application of database but how do I prove to my team that this a bad approach?
Best Answer
Read this paper: Multi-Tenant Data Architecture. It extensively discusses on the pros and cons of 3 possible approaches (tenant ID in each table, schema per tenant, database per tenant). This approach is called "Shared database, Separate schemas" and is a valid approach. Whether it is best for your case, I'm not in position to make that call. This is what the paper has to say about this approach:
Read the paper.
If you are trying to do this split for performance reasons, you and your team are definitely on the wrong path. Address the perf issues by identifying them and fix them appropriately. Read How to analyze SQL Server performance. Multi-tenancy ain't cheap, and you need to plan ahead and have an answer to simple questions like 'how do I deploy the next version of my app, which includes a database modification?' and 'how to restore the data for one particular project?'. The issues you see now are indicative of app tunning problems (hint: you're missing indexes).
As a personal experience note: I've seen databases with millions of objects in them (which the 'shared database, separate schema' approach is naturally going to cause), and it wasn't pretty... I hope all your DB administration is 100% scripted/automated now, expanding SSMS tree views with to select the 1 in 1M objects is not going to work.