Can you force Linq2SQL to NOT use sp_executesql?

Question

So I write a Linq query and it takes 16 seconds to run. Decide to see what the query plan is, so I get that out of Linq to SQL Profiler and the query only takes 2 seconds to run. sigh

After spending most of the day poking at things and finally getting around to using SQL Server Profiler I see that Linq2SQL is using sp_executesql to run the query. I understand that it's supposed to improve performance because it's more likely to re-use the execution plan... but it seems to have chosen a horrible execution plan to use.

The weirder part is that it only gets slow if I join a specific table, and I have no idea why that specific table is causing a problem.

EDIT Just to clarify the actual issue here:

It's actually getting to different queries. One is, essentially,

SELECT col1, col2, ... FROM table1, table2 WHERE table1.val IN (1234, 2343, 2435)

The other is

EXEC sp_executesql 'SELECT col1, col2, ... FROM table1, table2 WHERE table1.val IN (@p1, @p2, @p3)', 
N'@p0 int,@p1 int,@p2 int,@p3 int',
@p0=1234, @p1=2343, @p3=2435

Then why are you using LINQ? LINQ is great for basics. If you need to tune the query then query SQL. .NET can talk directly to SQL. — paparazzo, Dec 31 '11 at 03:16
But I shouldn't need to tune the query... the query itself works fine. — CodeRedick, Dec 31 '11 at 03:19
Make sure that the table you join has proper index and mainly on column on which you join. — dotnetstep, Dec 31 '11 at 03:35
See [Slow in the Application, Fast in SSMS? Understanding Performance Mysteries](http://www.sommarskog.se/query-plan-mysteries.html) — Martin Smith, Dec 31 '11 at 12:53
If a query has different performance if you just substitute values for the parameters, the usual cause is parameter sniffing. Martin Smiths' link provides an excellent read for that. If you still have questions after reading that (or because of it!), probably best to ask a new question. — Andomar, Jan 01 '12 at 12:36
I answered a similar question [here](http://stackoverflow.com/a/15477956/11808). — Christoffer Lette, Mar 18 '13 at 13:29

score 5 · Accepted Answer · answered Jan 01 '12 at 17:37

Your problem doesn't stem from the use of sp_executesql, and so circumventing it (which you can't) will not solve your problems. I suggest you read Erland Sommarskog's excellent article:

Slow in the Application, Fast in SSMS?
Understanding Performance Mysteries

This will give you a deep understanding of why you're getting a performance difference, how to diagnose and consistently reproduce it, and finally, how to solve it.

score 1 · Answer 2 · answered Dec 31 '11 at 11:20

1

If the exact same query is fast from one application or server, but slow from another, it's usually all about execution plans. An execution plan is the blueprint the server uses to run the query. The plan is supposed to be created once, and then reused for all queries which differ only in parameter values.

Different execution plans can lead to wildly difference performance, a factor of 100 is not at all unusual. As a first step, examine if the execution plans are different. The profiler event performance -> showplan xml logs the plan.

If the plan is different, one possible cause can be the session options, like ansi nulls:

SET ANSI_NULLS

Another possibility is a different login (the blueprint contains security information, so each security context has its own set of cached execution plans.)

The easiest way to clear the plan cache is to restart the SQL Server service. There's also an advanced command to clear the entire query plan cache:

DBCC FREEPROCCACHE

P.S. If you have a stored procedure that performs differently based on the value of parameters, it's worth to check out parameter sniffing. But since you're copying the exact same procedure from the profiler, I assume the parameters are identical for both the slow and the fast invocations.

answered Dec 31 '11 at 11:20

Andomar

232,371
49
380
404

I would have thought parameter sniffing. It is irrelevant that the OP is invoking with identical parameters for both the slow and the fast invocations. The whole issue with parameter sniffing is that it compiles the plan for the **first** lot of parameter values it sees then re-uses it. When pasted into SSMS as that has a different default option for `ARITH_ABORT` it won't reuse the cached plan used by the application but will generate a new one that is tailored for the parameter values that the OP is currently investigating. Hence better performance. – Martin Smith Dec 31 '11 at 12:52
@MartinSmith: The question says there's a performance difference with identical parameter values. How could sniffing explain that? – Andomar Dec 31 '11 at 13:02
Because when ran from the application it is presumably re-using a plan that was compiled for a different set of parameter values. The OP can verify this by retrieving the execution plans and looking at the "parameter compiled values" in the XML – Martin Smith Dec 31 '11 at 13:11
@MartinSmith: If the plan was compiled for a different set of parameters, that can explain why a call is slow. But it cannot explain why two calls with the same parameters have different performance. – Andomar Dec 31 '11 at 13:14
Because they aren't using the same plan. SSMS has a different default setting for the `ARITH_ABORT` option. This is one of the settings used as a plan cache key meaning that connections with different `on/off` settings can't reuse each others' plans. Chances are there will be no pre-existing plan in the cache suitable for the SSMS connection until the OP pastes the problem query in and runs it and so it generates a new one good for those values. – Martin Smith Dec 31 '11 at 13:18
Right, I see, what you're saying is that the different execution plan wouldn't be a problem except for the sniffing. I agree with that. Thanks for the link, interesting article! – Andomar Dec 31 '11 at 13:25

score 0 · Answer 3 · answered Jan 01 '12 at 17:13

0

To answer your question....

NO, you can't...

answered Jan 01 '12 at 17:13

Pleun

8,856
2
30
50

Can you force Linq2SQL to NOT use sp_executesql?

3 Answers3