At 05:56 PM 9/10/2009, you wrote:

>Hi all,
>
>I've got a Firebird database design that we've used for a few years. It essentially holds large e-mail archives on a per-user basis.
>
>The problem is that queries quickly become very slow as the data volume increases. It seems to me that it would make more sense to have a single table per user because there are no queries that span multiple users.
>
>Does it make sense to do this if it results in several thousand tables or are there disadvantages in having many tables? I know that it is bad database design, but is there a practical reason in this case to uses a single table?

In principle, there's nothing to gain by isolating each user's set in its own table. The thing to focus on with regard to your problem is *why* you should be seeing slowdown as data increases. Usually, that's a sign of a database that is accumulating too much garbage, or has indexes that are becoming inefficient from a long life without enough housekeeping.

Those issues really matter to performance.

>Currently we have a single table that stores each message in a row, and a field identifies the user. Most of the fields are indexed too.

In saying "most of the fields are indexed" should we infer that your records have more than just the UserID and a message? If you are using the structural model that makes a relational database "relational", then you will have structured the data into multiple tables that link related bunches of data horizontally and eliminate redundancy. A relational database really does not lend itself to bunching data vertically as a general rule, although it is not unusual for OLTP-style systems to archive outdated transactions periodically into "history" tables that are visited infrequently.

I'm sure your degrading performance issues can be solved. It would help if you could make the question a bit more specific to your data: its structure; how it is queried; the size of the datasets that are needed in the user applications, and so on. And mention the housekeeping measures you take regularly to keep the database in shape.

./heLen