What are your thoughts on breaking up a large SQL table into several, based on a certain category id?

Question

We have a large table that services many different stored procedures on lots of websites, but generally only deal with data in the table that relates to the website using it.

In other words, would it be wrong to split the table by websiteID?

Answer 1

If it means you are installing multiple databases on multiple servers, then that's one of the recommended ways to scale. If you mean multiple tables on the same server (same database or not), your gain will be little if any; and your administrative overhead will probably increase to keep them in synch. And if there are any cases at all of queries that hit multiple tables, those will be less efficient.

The effect is not likely to be substantial either for good or ill. What would you expect to be the benefit? (This is a typical rdbms antipattern for premature optimization, by the way.)

Answer 2

You mean you want to have BigTableForWebsite1, BigTableForWebsite2, BigTableForWebsite3, etc. instead of a single BigTableForAllWebsites? That makes me feel a little ill in my database-purist stomach...

I don't think it will give you a performance boost, since each access will still be working against a single database (probably stored in a single set of files).

Answer 3

Yes, it's wrong. You should keep in one table and index on websiteid. If you really wanted to make them appear separated you might want to use partitioned views but I don't think that's necessary.

In SQL terms you should never break up a table based on its excessive length (ie number of rows) but you might consider breaking up a table based on excessive width (ie number of columns).

Answer 4

If you want to move the applications to their own tables for isolation you would be better off setting up a database per instance rather than trying to do this in the application. This has the following advantages:

• The SQL for the queries doesn't change.

• It is easier to secure the data by database than by table partition. You can give the clients much more flexible access to the data without compromising other clients' data.

• You can implement independent backup/restore regimes on a per-customer basis if necessary.

• It is easier to 'scale out' by moving some customers onto another server if you have to.

• The application architecture is simpler as it doesn't have to be explicitly aware of customer filtering in each query.

Setting up multiple tables on a per-customer basis means you have to maintain a separate build for the application or generate all of the SQL statements that involve those tables. This is messy and harder to scale to a large number of customers.

As a generalisation, if you have a relatively simple application and a large number of customers, a single table with filtering is probably the more appropriate solution. For a more complex application with fewer customers your best approach is to set up multiple databases and have an application instance per customer.