Deciding if I should split a table

Question

I am building a multi language site

I have a descriptions table that hold the description of each product and a column that indicate the language. So it contain a row for each language description

Now my concern is that I have various different types of products in the system and reading the description of any product will have to go through this table. This mean a lot of traffic to this table

Is there gain from creating multiple description tables that will be dedicated to specific product groups and distribute the amount of hits to that single table to multiple tables?

Would this bring me any performance gain on Microsoft SQL?

Answer 1

How many products are you talking about? Because if it's less than 10 million, don't even bother optimizing it this way.

What you're talking about is basically a partitioning scheme. This is used for really large datasets. Less than 10 million doesn't come close though unless each item is massive.

If what you're doing is many reads, few writes there's no gain to be had from this kind of partitioning.

Don't optimize a problem until you have a problem.

Answer 2

For practical purposes, no. If your table is properly indexed then you should not see any difference.

Don't prematurely optimize your database structure.

Answer 3

I recommend checking these two things:

• Is your table indexed correctly? If not, index it.
• Are you currently seeing a performance issue here (provided its indexed properly)?

If you're not specifically seeing a performance issue, I wouldn't bother changing your structure. This should be perfectly fine for nearly any sized database - perhaps with a huge set of items, partitioning your description table may help, but if it's properly indexed, I doubt it will matter much at all.

Answer 4

There is always a slight performance hit when you normalise tables. But you probably will not notice it, if you index your tables correctly. If, in time it does become a problem, it is easier to denormalise than it is to normalise a table.

So, I would recommend you use separate tables.

Answer 5

Agreed - I prefer to solve problems that are actual problems.

If your application is sufficiently modular, and your data access layer correctly structured, it shouldn't be a problem to split things up later on. If you're using stored procedures, views, etc to abstract the underlying table structure from the application, performing this sort of optimization at a later date should have minimal impact on your source.