How to force recompile of execution path of a Linq to SQL query?

Question

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 path. 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?

Answer 1

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

Answer 2

;) 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?

Answer 3

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

I suggest you rebuild them:

exec sp_MSForeachTable 'UPDATE STATISTICS ?'