Is it reasonable to divide data into different tables, based on a column value?

Question

If I have a large table with a column which has a rather limited range of values (e.g. < 100), is it reasonable to divide this table into several tables with names tied to that column value?

E.g. a table like with columns:

table "TimeStamps": [Id] [DeviceId] [MessageCounter] [SomeData]

where [DeviceId] is the "limited range" column would be separated into several different tables:

table "TimeStamps1": [Id] [MessageCounter] [SomeData]
table "TimeStamps2": [Id] [MessageCounter] [SomeData]
...
table "TimeStampsN": [Id] [MessageCounter] [SomeData]

The problem I am having with my original table is that finding a largest MessageCounter value for some DeviceId values takes really long time to execute (see this post).

If tables would be separated, finding a maximum column number should be an O(1) operation.

Answer 1

While you could do it as a last-ditch performance optimization, I would advise against it. Mainly because it makes it very difficult to accomodate new DeviceIDs.

At any rate, doing this should not be necessary. If there's an index for DeviceID, the DBMS should be able to filter on it very quickly. That's what a DBMS is for, after all...

Answer 2

This is what a distributed database is for. The servers share a table in the same database based on some column. You tell the servers how to distribute the table based on ranges of column values. Once this is set up you just query the table and aren't concerned on which server the data actually resides.

Answer 3

I fear this approach would add a great deal to the complexity of any application which needed to access this data. An alternate approach, which gains you whatever benefits you might get from putting each device in a separate table while still keeping all devices in the same table, would be to partition the table on DeviceID. I suggest that you investigate table partitioning to see if it fits your needs.

Share and enjoy.

Answer 4

Have you considered Database partitioning? This is the baked in solution for the type of problem you've described. See: Partitioned Tables and Indexes in SQL Server 2005