Entity Framework join optimization

Question

I have recently started using Entity Framework and code first migration. My application is now "live" and I am starting to see that things are getting slow. My database have around 30.000 rows in the table that are used the most.

This is a method that I return a lot as I have to tables, one with the message data, and one with each recipient:

IQueryable<CompleteMessageModel> completeMessageModels = 
    from msg in db.NewMessageItems
    join mr in db.MessageRecipients on msg.MessageId equals mr.MessageId
    select
    new CompleteMessageModel()
    {
          MessageId = msg.MessageId,
          RecipientMessageId = mr.MessageRecipientId,
          Title = msg.Title,
          Message = msg.Message,
          Recipients = msg.Recipients,
          AuthorUserId = msg.AuthorId,
          RecipientUserId = mr.RecipientId,
          StatusCode = mr.StatusCode,
          Timestamp = msg.Timestamp,
          IsRead = mr.ReadTimestamp > 0,
          ReadTimestamp = mr.ReadTimestamp,
          GeoTag = msg.GeoTag
    };

Then I use this IQueryable to ask for messages above a certain timestamp and similar actions.

My question is: Can this query be optimized further?

This is the execution plan of the most used query:

SELECT TOP (90) 
[Project1].[MessageId] AS [MessageId], 
[Project1].[MessageRecipientId] AS [MessageRecipientId], 
[Project1].[Title] AS [Title], 
[Project1].[Message] AS [Message], 
[Project1].[Recipients] AS [Recipients], 
[Project1].[AuthorId] AS [AuthorId], 
[Project1].[RecipientId] AS [RecipientId], 
[Project1].[StatusCode] AS [StatusCode], 
[Project1].[Timestamp] AS [Timestamp], 
[Project1].[C1] AS [C1], 
[Project1].[ReadTimestamp] AS [ReadTimestamp], 
[Project1].[GeoTag] AS [GeoTag]
FROM ( SELECT 
    [Extent1].[MessageId] AS [MessageId], 
    [Extent1].[Message] AS [Message], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[AuthorId] AS [AuthorId], 
    [Extent1].[Timestamp] AS [Timestamp], 
    [Extent1].[Recipients] AS [Recipients], 
    [Extent1].[GeoTag] AS [GeoTag], 
    [Extent2].[MessageRecipientId] AS [MessageRecipientId], 
    [Extent2].[RecipientId] AS [RecipientId], 
    [Extent2].[ReadTimestamp] AS [ReadTimestamp], 
    [Extent2].[StatusCode] AS [StatusCode], 
    CASE WHEN ([Extent2].[ReadTimestamp] > 0) THEN cast(1 as bit) WHEN ( NOT ([Extent2].[ReadTimestamp] > 0)) THEN cast(0 as bit) END AS [C1]
    FROM  [dbo].[NewMessageModels] AS [Extent1]
    INNER JOIN [dbo].[MessageRecipients] AS [Extent2] ON [Extent1].[MessageId] = [Extent2].[MessageId]
    WHERE ([Extent2].[RecipientId] = @p__linq__0) AND (1 <> [Extent2].[StatusCode]) AND (3 <> [Extent2].[StatusCode]) AND ([Extent1].[Timestamp] >= @p__linq__1)
)  AS [Project1]
ORDER BY [Project1].[Timestamp] DESC

And if it can be optimized, How would this look in c#?

http://stackoverflow.com/questions/21051612/entity-framework-join-3-tables — Erçin Dedeoğlu, Feb 11 '17 at 05:59

Moho · Answer 1 · 2013-03-27T20:44:59.237

You don't need the join in your linq query - simply access the msg.MessageRecipient nav property in the projection. It will simplify your SQL statement by reducing the number for fields returned to only those that you use in the projection, but the join is still necessary.

e.g. change

RecipientMessageId = mr.MessageRecipientId

to

RecipientMessageId = msg.MessageRecipient.MessageRecipientId

Check the execution plan for the generated script in SSMS - it should suggest an index that will increase performance.

Edit: modified your sample to remove the unnecessary linq join. You access navigation properties like any other property in the projection:

IQueryable<CompleteMessageModel> completeMessageModels = 
    from msg in db.NewMessageItems
    //join mr in db.MessageRecipients on msg.MessageId equals mr.MessageId
    select
    new CompleteMessageModel()
    {
          MessageId = msg.MessageId,
          RecipientMessageId = msg.MessageRecipient.MessageRecipientId,
          Title = msg.Title,
          Message = msg.Message,
          Recipients = msg.Recipients,
          AuthorUserId = msg.AuthorId,
          RecipientUserId = msg.MessageRecipient.RecipientId,
          StatusCode = msg.MessageRecipient.StatusCode,
          Timestamp = msg.Timestamp,
          IsRead = msg.MessageRecipient.ReadTimestamp > 0,
          ReadTimestamp = msg.MessageRecipient.ReadTimestamp,
          GeoTag = msg.GeoTag
    };

Thank you for your valuable reply. How do you access a property in the projection? Do you have a code example or link that show how to do this? — Sindre, Mar 27 '13 at 18:42
Although you could use a navigation property, the query itself cannot be optimized. You already use a projection, which narrows down the result set and the join is required. It must be an indexing issue. 30,000 rows is "nothing". Or one of the fields is a large object (`Message` maybe?). — Gert Arnold, Mar 27 '13 at 19:19
There may be many MessageRecipients for each NewMessageItems, I don't see how I can get the msg.MessageRecipient to work without a join? There is not a MessageRecipient object on msg Also; when it find an index, how do i "use it"? Thank you for your help so far! — Sindre, Apr 06 '13 at 08:50

score 1 · Accepted Answer · edited May 23 '17 at 12:24

Unrelated to your specific issues at hand (which Moho answered partly already)...

You could also try the performance tips and especially Performance Considerations for Entity Framework 5

Above all, download the EF Power Tools - and check the generate views feature.

For more info I posted that few days ago with some more info (just a little but might help).

Mapping View to Entity using EF 5 Code First

why when i want use EF Power tools for view my model i get error?

Entity Framework join optimization

2 Answers2