How to reduce the number of database calls in linq to sql

Question

I have a script and I want to know bestpractices to reduce database hits. Scenario: I have a dictionary table in my application where I put all the words / keywords for the purpose of translation, because my system is multilingual. Keywords are placed throughout the page, they can be from 10 to 20 on one page, and for each word it extracts a translation from the database if the user does not view the English version of the website.

My application is in Asp.Net MVC 2 with C # and LINQ2SQL.

Answer 1

Caching is a good way to reduce database queries. You can use 2 cache levels:

• Cache objects (e.g. database query results)
• Cache HTML output of all actions or partial actions of the controller.

Answer 2

Translation usually does not change very often and the amount of data is limited. Read all translated lines when starting a web application and put them in a public dictionary. Whenever you need translated strings, find them in the dictionary.

Answer 3

linq will be lazy loading, which means that queries will not get into the database unless you get access to the properties returned by the query, so make sure you avoid accessing the property before you really need them.

you can also try combining linq queries into one and have a look at your loops to make sure there is no better way to loop through your queries.

you can also completely remove access to the database and use the translation files in xml and not in the database.

Answer 4

Before you can do things like caching and lazy loading, etc., it's best to understand what is going wrong.

Enter LinqToSql Profiler . Yes, it's a commercial product .. but it's worth it. In addition, he has a DEMO period.

This may show you the shit running queries .. and what queries do N + 1, etc.