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
|
|
|
|
|
|