What do you think of splitting a large SQL table into several, based on a specific category identifier?

Question

We have a large table that serves many different stored procedures on many websites, but usually deals only with the data in the table that relates to the website used.

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

Answer 1

If this means that you are installing multiple databases on multiple servers, then this is one of the recommended scaling methods. If you mean several tables on the same server (the same database or not), your gain will be small; and your administrative overhead is likely to increase to keep them in sync. And if there are any cases with all queries that fall into several tables, they will be less efficient.

The effect is unlikely to be significant for either good or bad. What do you expect from this? (This is typical rdbms antispam for premature optimization, by the way.)

Answer 2

Yes, that’s wrong. You must store in one table and index the site. If you really wanted them to be separated from each other, you can use partitioned views, but I don't think this is necessary.

In terms of SQL, you should never split a table based on its excessive length (i.e. the number of rows), but you can split a table based on its excess width (i.e. the number of columns).

Answer 3

If you want to move applications to your own tables for isolation, you'd better configure the database on one instance, rather than trying to do it in the application. This has the following advantages:

• SQL for queries is not a change.

• It is easier to protect database data than in a table section. You can provide customers with much more flexible data access without compromising other customers' data.

• You can implement independent backup / restore modes for each client.

• It's easier to "scale" by moving some clients to another server if you must.

• The application architecture is simpler because it does not have to be explicitly aware of the client filtering in each request.

Setting up multiple tables for each client means that you need to maintain a separate assembly for the application or create all the SQL statements that include these tables. This is a dirty and complicated scaling for a large number of customers.

In general, if you have a relatively simple application and a large number of clients, a single table with filtering is probably the more appropriate solution. For a more complex application with fewer clients, the best approach is to create multiple databases and have an application instance for each client.

Answer 4

You want to have BigTableForWebsite1 , BigTableForWebsite2 , BigTableForWebsite3 , etc. instead of one BigTableForAllWebsites ? It makes me feel a little sick in my base-purist stomach ...

I don’t think that this will give you an increase in productivity, since each access will work against one database (it may be stored in one set of files).