LINQ Query very slow compared to SSMS

Question

MAIN QUESTION Is there some known limitation, snafu, configuration issue, anything, which could account for the fact that all things being equal, a query run from C# linq could take an order of magnitude longer to complete than when run in any other fashion?

here is the abbreviated query in linq. it is a very straight forward join between a view and a table.

var query = (
    from content in context.ApprovedContentView
                where content.BucketId == 13098 && content.ContentTypeId == 5220 
    join item in context.ActiveContent
                on content.ContentId equals item.ItemId
        where
                item.IsSuchAndSuch == true && item.SomeOtherProperty == 5000 
        select new 
        {
            ItemId = item.ItemId,
            Title = item.Title,
            SubTitle = item.SubTitle,
            DescriptionText = item.DescriptionText,
            /* about 10 other scalar fields */

        });

int count = query.Count();
var data = query.OrderByDescending(item => item.ItemId).Skip(5).Take(3);

And here is the (abbreviated/formmatted) SQL it generates

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM       [SchemaX].[ApprovedContentView] AS [Extent1]
    INNER JOIN [SchemaX].[ActiveContent] AS [Extent2] ON [Extent1].[ContentId] = [Extent2].[ItemId]
    WHERE (13098 = [Extent1].[BucketId]) AND (5220 = [Extent1].[ContentTypeId ]) AND 
    (1 = [Extent2].[IsSuchAndSuch]) AND (5000 = [Extent2].[SomeOtherProperty ])
)  AS [GroupBy1]
GO

SELECT TOP (3) 
[Filter1].[BucketId] AS [BucketId], 
[Filter1].[ItemId] AS [ItemId], 
[Filter1].[Title] AS [Title], 
[Filter1].[SubTitle] AS [SubTitle], 
[Filter1].[DescriptionText] AS [DescriptionText], 
/* other fields */
FROM ( SELECT 
            [Extent1].[BucketId] AS [BucketId], 
            [Extent2].[ItemId] AS [ItemId], 
            [Extent2].[Title] AS [Title], 
            [Extent2].[SubTitle] AS [SubTitle], 
            [Extent2].[DescriptionText] AS [DescriptionText], 
            /* other fields */
            row_number() OVER (ORDER BY [Extent2].[DealId] DESC) AS [row_number]
    FROM  [SchemaX].[ApprovedContentView] AS [Extent1]
    INNER JOIN [SchemaX].[ActiveContent] AS [Extent2] ON [Extent1].[ContentId] = [Extent2].[ItemId]
    WHERE (13098 = [Extent1].[BucketId]) AND (5220 = [Extent1].[ContentTypeId ]) AND 
            (1 = [Extent2].[IsSuchAndSuch]) AND (5000 = [Extent2].[SomeOtherProperty ])
)  AS [Filter1]
WHERE [Filter1].[row_number] > 5
ORDER BY [Filter1].[DealId] DESC

DIFFERENT SCENARIOS i am basing my speed tests on observing the queries run using sql profiler

IN SITU when this linq query executes as part of its normal operation in my c# application, i observe that in sql profiler, the select count takes a full 3 seconds to complete, and oddly, the query which produces the projection takes only 200 ms, and the times are repeatable, which would seem to rule out a query execution plan cache issue. (running with entity framework 5, sql server 2008 r2)

IN LINQPAD when i execute the linq statements through LinqPad, using the C# application's dll's data context, the count and the projection each complete in under a quarter second (~224ms, per for a total run time of ~450ms).

IN SSMS regardless of the source of the sql, when i copy the actual code that sql profile reports that it execute and paste it into a management studio window and execute, it takes about 224ms.

Database tuning In SSMS, when i evaluate the actual execute plan for the sql that i copy from profiler (either from code or from linqpad), i find that sql is using all the correct indexes, and is reporting only index seeks -- no table scans, no rid lookups.

So, what gives? Anybody ever seen anything like this?

Answer 1

I would make sure that you don't have a bad execution plan that has been cached for your application. This happens to me often when doing schema work on a database that has already been in use. Its possible that you have an execution plan that has been cached for your applications execution context that is inefficient due to schema changes, while the execution plan that is generated for your SSMS queries is up to date and does not see these performance issues.

I would try using DBCC FREEPROCCACHE to force an update of your execution plans and see if that resolves the issue.

Answer 2

ARITHABORT is ON by default in SSMS and OFF by default for a SqlClient connection.

If the problem shows up again add:

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