Similar Topics...
 |
Support Option Recompile in Entity Framework
Entity Framework does not contain a straightforward way to add the OPTION (RECOMPILE) text to a dynamically generated LINQ SQL statement.
This is necessary in certain conditions where Parameter Sniffing (caching a bad execution plan based on your first call; every call thereafter runs very slow) has been identified as an issue. It should not be used in most other scenarios as caching execution plans is usually a good thing.
For example, imagine a LINQ SQL query: orders.Where(o => o.CategoryId = myvalue).
And an Orders table, where 99.9% is category "1" and .01% is category "2".
If you call this searching for category 2, it will cache an execution plan that is absolutely terrible if you then call it for category 1. This is a perfect parameter of parameter sniffing/cached execution plans causing huge performance issues.
In ADO.NET, you would either rename the query parameter to get a new execution plan each time, or add OPTION (RECOMPILE) to the SQL.
EF does not easily give you a way to do either of these, without customization. I have found the following solution for this: Implement DbCommandInterceptor, which requires EF version 6.0 or higher.
[Note: a simpler way to solve this is to disable parameter sniffing at the level of the entire database using this command: DBCC TRACEON (4136,-1); but this will affect every query.]
[Note: if you are calling a Stored Procedure with LINQ, the solution is to use local param vars in the procedure, OR add "OPTION (OPTIMIZE FOR UNKNOWN)" after the order by clause.]
Here is the solution:
Create this class:
using System;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
public class RecompileDbCommandInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
if (!command.CommandText.EndsWith(" option(recompile)"))
{
command.CommandText += " option(recompile)";
}
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
if (!command.CommandText.EndsWith(" option(recompile)"))
{
command.CommandText += " option(recompile)";
}
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
if (!command.CommandText.EndsWith(" option(recompile)"))
{
command.CommandText += " option(recompile)";
}
}
} // class
And then before your query, add the interceptor to the global instance.
You should remove it after your query is done.
RecompileDbCommandInterceptor recompile = new RecompileDbCommandInterceptor();
DbInterception.Add(recompile);
CALL EF QUERY HERE AND GET RESULT
DbInterception.Remove(recompile);
There is obviously a concurrancy issue where two users in parallel could have the 2nd query unregister the intercept before the 1st query has started.
This is low risk (worst case you are running a query without recompile).
Options are:
a) Ignore
b) Just never 'Remove' and recompile all queries (may not be ideal)
c) Implement on a per-query basis using technique found here: http://stackoverflow.com/questions/26761827/adding-a-query-hint-when-calling-tvf
Created By: amos 9/12/2016 5:06:34 PM
Updated: 9/12/2016 5:06:47 PM
|
|
|
|
|
|