Entity Framework 6 recipes 2nd edition (13-6) Translation-> automatically compiled LINQ Query

Problem

You want to improve the performance of multiple queries, and you do not want to add additional encoding or configuration.

 

Solution

Suppose you have the model shown in Figure 13-8.

 

Figure 13-8.A model with an associate and Its Related paycheck

 

In this model, each associate (colleague) has 0 to multiple paychecks (salaries), and you have a LINQ query, Which is used repeatedly in your entire application, you want to compile this version only once, and then reuse the compiled version to improve the query performance.

When executing a SQL statement for a database, EF must convert your strong-type LINQ query to the corresponding SQL query (based on your database engine, sqlserver, Oracle, and so on). In EF5, by default, each query conversion will be cached. This process is related to "automatic cache". Each subsequent LINQ query will be retrieved directly from the "query plan cache, in this way, the conversion step is bypassed. for queries containing parameters, if the parameter value is changed, the same query will be retrieved again. interestingly, this "query plan cache" is shared among context objects in the same application domain, that is, once cached, any context object in the same application domain accesses it.

In listing 13-10, we compared the performance of enabling and disabling cache. to illustrate the performance, we print out the 10 iterations of the compiled and non-compiled versions of the LINQ query. in this query, we can see a performance improvement of roughly twice. in most cases, compilation requires a relatively high cost, while query execution requires only a low cost.

 

Listing 13-20.Comparing the performance of a simple compiled LINQ Query

Private Static void rununcompiledquery ()

{

Using (VAR context = new efrecipesentities ())

{

// Explicitly disable query plan caching

VaR objectcontext = (iobjectcontextadapter) Context). objectcontext;

VaR associatenocache = objectcontext. createobjectset <associate> ();

Associatenocache. enableplancaching = false;

VaR watch = new stopwatch ();

Long totalticks = 0;

// Warm things up

Associatenocache. Include (x => X. paychecks). Where (A => A. Name. startswith ("Karen"). tolist ();

// Query gets compiled each time

For (VAR I = 0; I <10; I ++)

{

Watch. Restart ();

Associatenocache. Include (x => X. paychecks). Where (A => A. Name. startswith ("Karen"). tolist ();

Watch. Stop ();

Totalticks + = watch. elapsedticks;

Console. writeline ("not compiled #{0 }:{ 1}", I, watch. elapsedticks );

}

Console. writeline ("average ticks without compiling: {0}", (totalticks/10 ));

Console. writeline ("");

}

}

Private Static void runcompiledquery ()

{

Using (VAR context = new efrecipesentities ())

{

VaR watch = new stopwatch ();

Long totalticks = 0;

// Warm things up

Context. associates. Include (x => X. paychecks). Where (A => A. Name. startswith ("Karen"). tolist ();

Totalticks = 0;

For (VAR I = 0; I <10; I ++)

{

Watch. Restart ();

Context. associates. Include (x => X. paychecks). Where (A => A. Name. startswith ("Karen"). tolist ();

Watch. Stop ();

Totalticks + = watch. elapsedticks;

Console. writeline ("compiled #{0 }:{ 1}", I, watch. elapsedticks );

}

Console. writeline ("average ticks with compiling: {0}", (totalticks/10 ));

}

}

The output result is as follows:

Not compiled #0: 10014

Not compiled #1: 5004

Not compiled #2: 5178

Not compiled #3: 7624

Not compiled #4: 4839

Not compiled #5: 5017

Not compiled #6: 4864

Not compiled #7: 5090

Not compiled #8: 4499

Not compiled #9: 6942

Average ticks with compiling: 5907

Compiled #0: 3458

Compiled #1: 1524

Compiled #2: 1320

Compiled #3: 1283

Compiled #4: 1202

Compiled #5: 1145

Compiled #6: 1075

Compiled #7: 1104

Compiled #8: 1081

Compiled #9: 1084

Average ticks with compiling: 1427

 

How it works

When you run a LINQ query, EF creates an Expression Tree object for the query and converts or compiles the object into an internal command tree. the internal command tree is passed to the database provider and converted to the corresponding database commands (usually SQL ). the cost of converting an expression tree may be quite high, mainly depending on the query complexity and the underlying model. if the model has a deep inheritance or many horizontal introductions, the conversion process will be quite complex, in this way, compilation takes much more time than query execution. then, the automatic query cache technology is introduced for the LINQ query in EF5. you can view the execution result of listing 13-20 to see its improved performance.

In addition, as shown in listing 13-20, you can also disable the "automatic compilation" feature. You can use the underlying object objectcontext of the dbcontext object to get a reference to an object, set its enableplancaching attribute to false.

To track each compiled query, EF traverses the query expression Tree node, creates a hash table, and uses it as the index of the compiled query for each subsequent call, EF first tries to find the primary key of the hash table from the cache to save the cost of query conversion. note that the "query cache plan" does not depend on the context object. It is the application domain bound to the application, which means that, cache queries are available for all context instances.

When the underlying query cache contains 800 or more cache plans, each minute, a cleanup process is performed based on lfru (least frequently/recently used is used least times, not used recently) the algorithm (based on the number of hits in the query and its time limit) to remove a cache.

Compiled queries are particularly useful for Asp.net paging queries. The parameters of paging queries may be changed, but the queries are consistent and can be reused to display each page, this is because a compiled query is "parameterized", that is, it can accept different parameter values.

Entity Framework 6 recipes 2nd edition (13-6) Translation-> automatically compiled LINQ Query