LINQ to SQL execution time is 50x longer than SSMS

问题:

I have an asp.net app that uses Linq to SQL.

One query from the app was timing out (over 30 seconds to execute). I increased the CommandTimeout of the DataContext, and it did complete in 45 seconds.

I copied the Linq to SQL generated SQL from SQL Profiler to SSMS and executed it using the same user credentials as I am using in my app, and I got a sub-second execution time.

I am using a new DataContext to execute the query, so the query is running in its own transaction.

My connection string looks like this:

Data Source=.\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=False; user id=MyUser; password=@#$%^&*

Anyone have any ideas why I could be getting such different results?

Here is the SQL:

exec sp_executesql N'SELECT [t1].[MatchCount], [t1].[RequestId], [t1].[Site], [t1].[Client]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[RequestId]) AS [ROW_NUMBER], [t0].[MatchCount], [t0].[RequestId], [t0].[Site], [t0].[Client]
    FROM [dbo].[ReportingRequestsSubsetWithMatches] AS [t0]
    WHERE ([t0].[Finish] < @p0) AND ([t0].[Finish] > @p1)
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
ORDER BY [t1].[ROW_NUMBER]',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int',@p0='2013-02-02 00:00:00',@p1='2013-02-01 00:00:00',@p2=10,@p3=10

回答1:

Compare the SET options for SSMS and your LINQ sessions:

SELECT * FROM sys.dm_exec_sessions
WHERE session_id in (@@SPID, @LINQSESS)

(replace @LINQSESS with your Linq session id)

---

ARITHABORT is often the culpit, see if this solves your issue:

new SqlCommand("SET ARITHABORT ON", connection).ExecuteNonQuery();