I have a query called by a DataContext object creating an extremely inefficient execution plan. I would like to add the query hint "OPTION (RECOMPILE)" to the query, but I do not know how to add this query to the DataContext query object.
I ran SQL trace to capture the query. I ran it manually as it is, and it took almost four minutes to add “OPTION (RECOMPILE)” to the query, which reduced the execution time to a second. The query contains many variables, two table value functions and a view with a built-in table value function. All input variables are numbers. Request plans between the two executions were very different.
I don't need help optimizing the code to avoid bad execution plans; I can do it myself if I need to go this route. All I need to know is a way to add an OPTION request hint (RECOMPILE) to my Linq request. I will not publish the code, this is not relevant to my question.
If you can add a request to recompile the request, let me know how and if this is not possible, if you could provide a link to some documentation that indicates that it is, I would appreciate it.
I am using SQL Server 2012 as my rdbms.
source share