LINQ to Entities Query Optimization - Poor Performance

I've got some very poor performing queries all over the place in my EF6 implemented app. Here is one query that is taking nearly 3000 MS to be performed. (localhost to external sql server)

dash.UserActivities = db.Activities.Include(a => a.Customer).Include(a => a.ActivityType).Where(a => a.AssignedUserId == userId)
    .Where(a => a.IsComplete == false).OrderBy(a => a.DueDateTime).Take(10).Select(
        a => new ActivityViewModel()
        {
            Id = a.Id,
            CustomerFirstName = a.Customer.FirstName,
            CustomerLastName = a.Customer.LastName,
            ActivityType = a.ActivityType.Name,
            DueDateTime = a.DueDateTime,
        }
    ).ToList();

Clearly something doesn't feel right about this, it is probably something obvious. But I have no clue what it is!

UPDATE

The SQL being generated from this is:

SELECT TOP (10) 
    [Project1].[C1] AS [C1], 
    [Project1].[Id] AS [Id], 
    [Project1].[FirstName] AS [FirstName], 
    [Project1].[LastName] AS [LastName], 
    [Project1].[Name] AS [Name], 
    [Project1].[DueDateTime] AS [DueDateTime]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[DueDateTime] AS [DueDateTime], 
        [Extent2].[FirstName] AS [FirstName], 
        [Extent2].[LastName] AS [LastName], 
        [Extent3].[Name] AS [Name], 
        1 AS [C1]
        FROM  [dbo].[Activities] AS [Extent1]
        INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[Id]
        INNER JOIN [dbo].[ActivityTypes] AS [Extent3] ON [Extent1].[ActivityTypeId] = [Extent3].[Id]
        WHERE (0 = [Extent1].[IsComplete]) AND ([Extent1].[AssignedUserId] = 037da3f4-99cc-4338-8b36-491ca0fcfcb1 /* @p__linq__0 */)
    )  AS [Project1]
    ORDER BY [Project1].[DueDateTime] ASC

Answers


As @MarcinJuraszek suggested in the comments, we needed to perform these queries locally and audit the performance here. Using tuning adviser, we found many opportunities for improvement.


Need Your Help

How does SSMS get the transaction log usage?

sql-server sql-server-2005 ssms transaction-log

When you right-click on database, Reports...Disk Usage, I get a report. Because I have limited permissions, I only get the top portion, which shows "Transaction Log Space Usage" - that number alone...

why does my 'if (isset) does not interact with mysql'

php mysql json isset

why does my 'if if (isset($_POST['submit'])) not interact with mysql'

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.