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,752
  • 3
  • 35
  • 59
Dennis van der Stelt
  • 2,058
  • 15
  • 21
  • 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

5 Answers5

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
  • 9,920
  • 1
  • 43
  • 49
  • 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
  • 288,400
  • 42
  • 452
  • 532
0

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

Gregory Higley
  • 15,216
  • 9
  • 64
  • 89
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
-1

;) you dont. Simlpe. Not exposed.

But dynamic queries should not need a "WITH RECOMPILE". Check the query in management studio when it is slow.... all users share execution paths.

Could it be it is not the SQL Server that is slow? But LINQ (i.e. client side handling)?

What is the query you run?

TomTom
  • 1
  • 10
  • 85
  • 146
  • 2
    The query plans will still be reused. You can run into the same parameter sniffing problems as with stored procedures. Checking with SSMS will probably result in slightly different text and a different plan than what you get out of L2S. – Mark Sowul Jun 05 '12 at 18:42