CODEWITHSUNDEEP
c#asp.net-mvclinqentity-frameworkentity-framework-4
DonQi
DonQi

Reputation: 409

Why is Entity Framework .ToList() with .Include very slow and how can I speed it up?

I am querying an oracle database with EF4.

I have 2 to tables POINTS (with around 100 000 rows) and COUNTRIES, each point having a countryCode as foreign key

I have the 2 below methods in a repository

public List<PointsTable> GetAll()
{
    using (Entities context = new Entities())
    {
        List<PointsTable> theList = context.POINTS_TABLE.ToList();
        return theList;
    }
}

public List<PointsTable> GetAllComplete()
{
    using (Entities context = new Entities())
    {
        List<PointsTable> theList = context.POINTS_TABLE.Include("Countries").ToList();
        return theList;
    }
}

The GetAll takes 5 seconds but the GetAllComplete takes 2 minutes!

I have using AsParallel() but the gain is ridiculous.

Can i speed this up, or whats causing it to be slow?

Upvotes: 4

Views: 14470

Answers (2)

NinjaNye
NinjaNye

Reputation: 7126

The reason is that for each record you are retrieving it's countries which for 200k records multiplies into alot of records.

Are you going to querying this data later to reduce it to your specific needs? If so don't .ToList() them just yet.

Change your repository methods to return IQueryable, that way you can restrict the query to the particular data you require later down the line reducing the ammount of data you put into memeory

private Entities _context;

public PointsRepository(Entities context)
{
    _context = context
}

public IQueryable<PointsTable> GetAll()
{
    return context.POINTS_TABLE;
}

public IQueryable<PointsTable> GetAllComplete()
{
    return context.POINTS_TABLE.Include("Countries");
}

You can then add your specific filters and ToList the smaller result. e.g.

using (Entities context = new Entities())
{
    var rep = new PointsRepository(context);

    // This will change the query you send to sql to only 
    // retrieve the specific data you want and should result 
    // in much quicker execution
    var result = rep.GetAllComplete()                    // get all with includes
                    .Where(p => p.Property = "Specific") // refine the query 
                    .ToList()                            // retrieve the data and add to memory
}

Hope this helps

Upvotes: 7

PakKkO
PakKkO

Reputation: 154

AS Parallel(), not working with Linq to Entities, working only with Linq to Object.

Load 200K with EF not is good plain.

You can improve performance with the readonly load:

context.POINTS_TABLE.MergeOption = MergeOption.NoTracking;

Upvotes: 0

Related Questions