3

I have a LINQ to SQL query that's created dynamically. Funny thing is, when I run it in SQL Management Studio it's lightning fast. When I run it from L2S it becomes awefully slow after a while.

This is probably because of the query plan/execution plan. When I restart SQL Server the L2S query is also lightning fast again.

Now with T-SQL you can have WITH RECOMPILE. But how to do this with L2S?

Alberto Solano
  • 7,972
  • 3
  • 38
  • 61
Dennis van der Stelt
  • 2,203
  • 16
  • 22
  • 1
    In 99% of cases, you should not need WITH RECOMPILE; it is usually a symptom you are treating, rather than the real cause... – Mitch Wheat Mar 11 '10 at 07:57
  • It is a symptom of parameter sniffing being imperfect and sometimes resulting in crazy decisions. If it were programmer error, all queries would be slow. – speciesUnknown May 17 '21 at 08:37

4 Answers4

6

As I found in the thread below, you can use the DataContext.GetCommand(IQueryable) to get a DbCommand for the query you wish to execute. You can add "OPTION (RECOMPILE)" to the command text, from that, open a reader, and use [DataContext.Translate<T>]1 to translate the opened reader to the entity type you wanted.

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/def80609-eaf2-4631-8d3d-ad10fc9aedfa

For example, given a DataContext dataContext:

IQueryable<string> exampleItemsQuery = dataContext.Table.Where(…).Select(…); //etc

DbCommand command = dataContext.GetCommand(exampleItemsQuery);
command.CommandText += Environment.NewLine + "OPTION (RECOMPILE)";
if (dataContext.Connection.State != ConnectionState.Open)
   dataContext.Connection.Open();

IEnumerable<string> exampleItems = dataContext.Translate<string>(command.ExecuteReader(CommandBehavior.CloseConnection));
Mark Sowul
  • 10,244
  • 1
  • 45
  • 51
  • However it doesn't seem to work with some load options. The DataContext.Translate may start executing another SQL command and fail since the current command still uses the connection. – Ihar Bury Sep 19 '12 at 13:06
  • I sort of understand what you are saying, but a clear example of how to do this would be greatly helpful – Kellen Stuart Jan 31 '19 at 17:45
2

From the behaviour you describe, your statistics are almost certainly out of date.

I suggest you rebuild them:

exec sp_MSForeachTable 'UPDATE STATISTICS ?'
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
0

Check out the CompiledQuery class. Here's a tutorial from Microsoft that goes into even more detail.

Gregory Higley
  • 15,923
  • 9
  • 67
  • 96
0

I used this EF 6 Parameter Sniffing to add at the end of SQL commands "option(recompile)" before executing. It work for me. It is very good workaround how to solve it.

Community
  • 1
  • 1
user2245049
  • 1