Entity Framework performance problems

Question

I'm having a problem where the below query is taking around 700ms to execute. It's in a loop and gets called 100+ times so it's taking forever.

Model:

public class ReleaseDates
{
    public int Id { get; set; }
    public string MovieName { get; set; }
    public string Country { get; set; }
    public DateTime ReleaseDate { get; set; }
    public string AlternateSource { get; set; }
}

Query:

public async Task<List<ReleaseDates>> GetReleaseDatesAsync(string movieName)
{
    return await Db.ReleaseDates.Where(x => x.MovieName == movieName && string.IsNullOrEmpty(x.AlternateSource)).ToListAsync();
}

Any suggestions how to speed this up?

Take it out of a loop and change it so there is only one query to get everything? You can keep appending where conditions without enumerating it — Brad C
– Brad C, Commented Jun 25, 2015 at 13:20
The table is very large 10,000's of data. So grabbing everything I would have thought to be a bad idea? — Jamie Rees
– Jamie Rees, Commented Jun 25, 2015 at 13:22
Maybe you should use a stored procedure to do the join and get the data you need the return instead of looping and passing in movieName in 1 by 1 — dansasu11
– dansasu11, Commented Jun 25, 2015 at 13:25
Use results caching (also known as "second-level caching"), you keep the results of queries in a local cache. When issuing a query, you first see if the results are available locally before you query against the store. — Anant Dabhi
– Anant Dabhi, Commented Jun 25, 2015 at 13:30

Razvan Dumitru · Accepted Answer · 2015-06-25 13:32:08Z

3

Get rid of the loop. That's the problem. You're sending a lot of queries to database.

Store all the movie names that you're searching for in a list and do a contains there.

public async Task<List<ReleaseDates>> GetReleaseDatesAsync(List<string> movieNames)
{
    //movie names that you're searching for - movieNames

    return await Db.ReleaseDates.Where(x => movieNames.Contains(x.MovieName) && string.IsNullOrEmpty(x.AlternateSource)).ToListAsync();

}

If you want to get the release dates for "Ant Moon", "GodZippa", "SuperWan", your list will contain these strings and this is it.

Some further reading: http://blogs.msdn.com/b/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx

edited Jun 25, 2015 at 13:32

answered Jun 25, 2015 at 13:26

Razvan Dumitru

12.5k5 gold badges38 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamie Rees Over a year ago

Yeah stupid of me, when trying to work something out I always end up with tunnel vision. Thanks.

Thiago Custodio · Accepted Answer · 2015-06-25 13:21:21Z

2

Create a index on your movie name field. Also, catch the query through profiler and exec it in Sql Management Studio with Show Plan enabled. It will give you some insights to optimize the query performance.

answered Jun 25, 2015 at 13:21

Thiago Custodio

18.4k9 gold badges50 silver badges99 bronze badges

4 Comments

Jamie Rees Over a year ago

sorry, new to EF, how would i create an index?

Thiago Custodio Over a year ago

Do it directly on Sql Server Management Studio.

Jamie Rees Over a year ago

I'm using a code first approach, Can it not be done via the model or any EF configuration?

Drew Kennedy Over a year ago

I believe you can create indices if you're using EF 6.1. If you're using a version lower than 6.1, you'll have to do it manually. msdn.microsoft.com/en-us/data/jj591583.aspx#Index

Collectives™ on Stack Overflow

Entity Framework performance problems

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related