Performance issue for the tables with many relations.

[borger]

We are using LinqConnect for MySQL in our ASP.NET application. Database have more then 170 tables and some tables have 10-25 relations per table. It takes 1-2 sec only to init structure of the relations and related tables when we do query to those tables (simply get some records from one table). An operation of the LINQ-statement to SQL-query compilation and IL-code of the materialization method take long time and many resources.

It happens with every query. We turn on CompiledQueryCache but all new queries take long time and many resources of the system.

Is it possible to generate f.ex. DB model one time then add it to the cache and use those data? Or is it possible to resolve this problem in another way?

[StanislavK]

Provided that you can determine some list of frequently used queries, you can cache them after the data context is created. For example, you can implement the OnCreated partial method of your data context class and compile there each of these queries like in the following sample:

Code: Select all

Func>
  MyTableById = CompiledQuery.Compile((MyDataContext db, int id) =>
  from mt in db.MyTables where mt.Id == id select mt);

After being compiled, it will be cached. For more information about compiling queries manually, please refer to our documentation.

Please tell us if this helps.