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

Question

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?

Answer 1

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