Similar Topics...
 |
Support Option Recompile in LINQ
LINQ 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 thereafer 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, imgine 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.
LINQ does not easily give you a way to do either of these, without a lot of customization. I have found the following solution for this: an override (Extension Method) of ToList that takes in a boolean flag. False will execute the normal ToList code. True will use the SQL OPTION (RECOMPILE) flag in the generated result set.
[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 it is:
/// <summary>
/// Overload ToList to force option recompile in the SQL call (if you pass true to optionRecompile).
/// </summary>
/// <typeparam name="T">output type</typeparam>
/// <param name="query">the query (LINQ to SQL)</param>
/// <param name="dataContext">the datacontext that was used to generate the query</param>
/// <param name="optionRecompile">true: forces SQL to recompile execution plan</param>
/// <returns></returns>
public static List<T> ToList<T>(this IQueryable<T> query, DataContext dataContext, bool optionRecompile)
{
if (!optionRecompile)
return query.ToList(); // just let LINQ handle this.
var command = dataContext.GetCommand(query);
command.CommandText = string.Concat(command.CommandText, " OPTION (RECOMPILE)");
object[] values = new object[command.Parameters.Count];
for (int i = 0; i < command.Parameters.Count; i++)
{
values[i] = command.Parameters[i].Value;
}
return dataContext.ExecuteQuery<T>(command.CommandText, values).ToList();
} // ToList
This must be put in a static class unless you remove the 'this' so that it is not an extension method.
Usage:
return myQuery.ToList(myDataContext, true);
Created By: amos 4/4/2013 4:35:04 PM
Updated: 11/6/2014 6:06:23 PM
|
|
|
|
|
|