Multilanguage site performance

Question

I'm currently working on a site (ASP MVC) that support Multilanguage where every web content is stored in Database, for example I have english version, spanish version, etc. But I'm worried about the performance when it goes to production because everytime a user hit my page, there are at least 20 database calls to get the content.

Is there anything I can do to make this more efficient?

What I can't do is :

• Merge all database call into 1 call on every page load (this takes too much effort).

• Make a separate html page for every language (I need the end user to be able to add new language without me changing the code hence the database design).

I was thinking about caching everything in user's browser and compare it everytime they hit my page and it will only call the database if it doesn't match but I'm not sure how to approach this.

Any help will be appreciated and sorry for bad english.

Answer 1

I would suggest to go with static dictionary in this case, as @Vsevolod-Goloviznin suggested in the comments. Let me elaborate my approach

1. You probably have a localized resource in your database identified with some named key and language key as well:

   var homePageTitle = Database.GetResource("homeTitle", "es");
   

2. You should build up a static dictionary that will be used as cache and will have all the resources in the database in memory, for easy and convenient access:

   public static MultiLanguageManager {
       private static Dictionary<string, Dictionary<string, string>> ContentCache;
   
       ...
       public static GetResource(string key, string language) {
           return (ContentCache[language])[key];
       }
   }
   

3. Then in your front-end you will have something like this:

   ...
   <title>@MultiLanguageManager.GetResource("aboutTitle", "en")</title>
   ...        
   

4. Whenever user changes the localized content in the database, you should rebuild the ContentCache dictionary manually:

   // update localized content
   ...
   MultiLanguageManager.RebuildContentCache();
   ...
   

By using this approach you can reduce the number of database calls to minimum (retrieve only logic units, and not static resources), and control the consistency of your localization data. One possible drawback might be the size of the static dictionary that you will build, but with Today's abundance of resources, this should not be a problem in 99% of the cases.