ADO.NET RPC Slower than Management Studio

[Dereck]

I have an ADO.NET Entity Framework generated query which seemed a bit
slow, so I ran SQL Profiler and confirmed that the query was taking
12-13 seconds for each execution. In the profiler the event is an
RPC. If I take the command text from the profiler and run it in
Management Studio, the execution takes 1 second (about 500
milliseconds in profiler). The query never changes, and I have run it
repeatedly hundreds of times with the same disturbing results. When I
run it from Management Studio I noticed it does not come across as an
RPC, but a normal TSQL batch. Any ideas why the RPC takes so much
longer?

Here is an outline of what the query looks like:


exec sp_executesql N'SELECT TOP (1)
[Project1].[C1] AS [C1],
.....
FROM ( SELECT
[Filter1].[AcctID] AS [AcctID],
....
1 AS [C1]
FROM (SELECT [Extent1].[AcctID] AS [AcctID], ....
FROM [dbo].[Account] AS [Extent1]
LEFT OUTER JOIN [dbo].[AccountStatus] AS [Extent2] ON [Extent1].
[AcctStatusID] = [Extent2].[AcctStatusID]
WHERE .... ) AS [Filter1]
INNER JOIN [dbo].[Client] AS [Extent3] ON [Filter1].[ClientID] =
[Extent3].[ClientID]
WHERE (.... = @AccountStatus) AND (.... = @ClientId)
) AS [Project1]
ORDER BY [Project1]..... DESC',N'@AccountStatus int,@ClientId
int',@AccountStatus=1,@ClientId=1

 

[sloan]

Google "Sql Server" "Parameter Sniffing". <<To at the least familiarize
yourself with the issue......

I know how to fix it with traditional usp calls.

I don't know what the ADO.NET Entity Framework fixes might be.

If you find this is the issue and a workaround........then post it here !

 

[Travis75]

You may want to look at the ARITHABORT settings in SQL management studio.
ADO.NET and SQL MS have different default settings for this (cannot remember
which is which), but the differences result in different query plans. Try
running the query in SQL MS with the setting ON and then OFF and see if the
timings change. I've seen this introduce performance issues between the
different executions.