Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 11 down vote accepted

Read this paper: Multi-Tenant Data Architecture. It goes at length 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:

A significant drawback of the separate-schema approach is that tenant data is harder to restore in the event of a failure. If each tenant has its own database, restoring a single tenant's data means simply restoring the database from the most recent backup. With a separate-schema application, restoring the entire database would mean overwriting the data of every tenant on the same database with backup data, regardless of whether each one has experienced any loss or not. Therefore, to restore a single customer's data, the database administrator may have to restore the database to a temporary server, and then import the customer's tables into the production server—a complicated and potentially time-consuming task.

The separate schema approach is appropriate for applications that use a relatively small number of database tables, on the order of about 100 tables per tenant or fewer. This approach can typically accommodate more tenants per server than the separate-database approach can, so you can offer the application at a lower cost, as long as your customers will accept having their data co-located with that of other tenants.

Read the paper.

which would improve select performance

If you are trying to do this split for performance reasons, you and you're 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 I 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.

share|improve this answer
    
Thanks. Going through the paper now.But wouldn't a database partition on ProjectID would be more beneficial here? – f444ran Mar 21 at 16:53
1  
What do you mean by "database partition on ProjectID" ? – Remus Rusanu Mar 21 at 19:40
    
My first recommendation was doing a partition on database with projectID as reference. This would older project data goes into different partition which would be hopefully in different disks. – f444ran Mar 22 at 8:29
    
I would advise against partitioning the tables per project ID. You can, at the very best, make 15k partitions on a table, so you will end up mixing projects in a partition anyway. Partitioning has a cost, see How To Decide if You Should Use Table Partitioning. – Remus Rusanu Mar 22 at 9:03
    
As a first step I would start by making every clustered index key start with the project id. It will give you most of the advantages you were after (as every query specifies a project_id, which I'm sure your app does, I hope), and the overhead/cost is smaller. – Remus Rusanu Mar 22 at 9:06

Can't really add much to @RemusRusanu 's answer - maybe this should be made into a community wiki on this issue - it seems to crop up regularly in various shapes and forms.

There is, as Remus points out, no single answer to this question - basically, it's a series of compromises which boil down to business decisions less than technical ones.

For example, you write " This would be mean each table would contain at max 30K rows which would improve select performance" - err... no it won't. If you have 40 small tables being accessed at once, that will be as much (approx.) of a disk contention as one big table being accessed multiple times.

It's up to you to explain the issues to management having read Remus's link and other material that you can Google. I've worked with various companies which have taken differing approaches and I've written a bit about this issue myself (1, 2 & 3 and check the links within - esp. this one - the accepted answer to one thread). I'll try and write up a community wiki and submit it for approval. Best of luck with your situation.

share|improve this answer
    
Thanks for the links.Going thru them. – f444ran Mar 22 at 8:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.