Edit

Query Plan Caching (Entity SQL)

  • Article

Whenever an attempt to execute a query is made, the query pipeline looks up its query plan cache to see whether the exact query is already compiled and available. If so, it reuses the cached plan rather than building a new one. If a match is not found in the query plan cache, the query is compiled and cached. A query is identified by its Entity SQL text and parameter collection (names and types). All text comparisons are case-sensitive.

Configuration

Query plan caching is configurable through the EntityCommand.

To enable or disable query plan caching through EntityCommand.EnablePlanCaching, set this property to true or false. Disabling plan caching for individual dynamic queries that are unlikely to be used more then once improves performance.

You can enable query plan caching through EnablePlanCaching.

Dynamic queries should be avoided, in general. The following dynamic query example is vulnerable to SQL injection attacks, because it takes user input directly without any validation.

var query = "SELECT sp.SalesYTD FROM AdventureWorksEntities.SalesPerson as sp WHERE sp.EmployeeID = " + employeeTextBox.Text;

If you do use dynamically generated queries, consider disabling query plan caching to avoid unnecessary memory consumption for cache entries that are unlikely to be reused.

Query plan caching on static queries and parameterized queries can provide performance benefits. The following is an example of a static query:

var query = "SELECT sp.SalesYTD FROM AdventureWorksEntities.SalesPerson as sp";

For queries to be matched properly by the query plan cache, they should comply with the following requirements:

  • Query text should be a constant pattern, preferably a constant string or a resource.

  • EntityParameter or ObjectParameter should be used wherever a user-supplied value must be passed.

You should avoid the following query patterns, which unnecessarily consume slots in the query plan cache:

  • Changes to letter case in the text.

  • Changes to white space.

  • Changes to literal values.

  • Changes to text inside comments.

See also