duplicate data or better performance?

We need to display our product name in different languages, but only some of the them have name in different language than English. When we query products with the certain language, we want to show the default name in English if the certain language name is missing.

To get the better query performance, we have to fill the default English name to the language dependent product name table (languageid + productid is the primary key) when the name for the certain language is missing. It made lots of duplicate name in this language dependent table and it's a bit difficult to update this table when the default English name changed.

Currently, we have about 300,000 products with about 30 languages and more than 8,000,000 rows in this table, at least more than 90% data is duplicate and fill with default English name. But if we use left join and isnull check in the query, the query performance will be much slower.

Who can recommend me a better database design that I can avoid to fill the duplicate data and have a better query performance?

The current tables schema like below

Table1 (about 300,000 rows)
ProductId   | Country        | Currency  | others fields
------------|----------------|-----------|---------------
Product A   | US             | USD       | ...
Product B   | GB             | GBP       | ...

Table2 (about 9,000,000 rows)
LanguageId  | ProductId      | Product Name
------------|----------------|--------------------------
English     | Product A      | Product A Name
English     | Product B      | Product B Name
German      | Product A      | Produkt A Name
German      | Product B      | Product B Name (it's filled by English name)

I have tried below query to avoid duplicate data, but the performance was a bit worst.

SELECT
    A.ProductId,
    A.Country,
    ISNULL(B1.ProductName, B2.ProductName) as ProductName
FROM
    Table1 A (NOLOCK)
    LEFT JOIN Table2开发者_高级运维 B1 (NOLOCK) on A.ProductId = B1.ProductId
    LEFT JOIN Table2 B2 (NOLOCK) on A.ProductId = B2.ProductId and B2.LanguageId = 'ENGLISH'
WHERE
    B1.LanguageId = 'German'
ORDER BY
    ISNULL(B1.ProductName, B2.ProductName)

Have you tested the LEFT JOIN and ISNULL? or is this just a guess? Given you are shifting a lot of data around, I'd say the optional language row would be far quicker unless you have some really bad indexing

SELECT
   ...,
   ISNULL(L.languageproductName, P.productname)
FROM
   Product P
   LEFT JOIN
   LangaugeStuff L ON P.productID = L.productID AND L.languageID = @Mylanguage

I'm not sure if this is feasible for your particular situation, but why not let the UI interface layer or Application layer handle the translation via a common localization pattern?

If there is no name for your product in that language record, put a NULL in that field. When you do your query, use COALESCE to replace the NULL with your English product name.

SELECT COALESCE(l.ProductName, 'Product Name')
FROM Language l

My proposed design would look something like this:

Language | ProductName | TitleMenu
----------------------------------
English  | Widgetizer  | Title
French   | La Widgette | La Title
Spanish  |             | El Title

Since Spanish has a NULL entry, the COALESCE skips the null and puts in the default product name.