Optimize EntityFramework Query

Question

I have got 2 tables - ReferrerSources with size of 50 rows and Referrer with size of 1.5 million rows.

Each Refferer entity has a link to corresponding ReferrerSource. I just need to count number of Referrer's for each ReferrerSource:

public void CreateConvertionReportTest(DateTime from, DateTime to)
{
    //.GetAll() returns IQueryable<T>. It's equal to Context.Set<T>().
    var src = ContextManager.ReferrerSourceContext.GetAll();
    var reff = ContextManager.ReferrerContext
                             .GetAll()
                             .Where(r => r.Created >= from && r.Created < to);

    var info = src.GroupJoin(
                   reff, 
                   (s) => s.Id, 
                   (r) => r.ReferrerSourceId, 
                   (s, or) => new
                   {
                       sorce = s.Name,
                       refferrersCount = or.Count()
                   } 
               );

    foreach(var g in info)
        Console.WriteLine("src: " + g + " : " + g.refferrersCount);
}

But this code causes a TimeoutException after an 30 seconds of execution.

I know, that simple SQL script can do that in a moment. What am i doing wrong?

What does GetAll() do? Have you profiled your SQL instance to see what queries is actually running? If GetAll() returns something in memory, then you might be creating an n+1 query instead of executing in a single go. — Ocelot20
– Ocelot20, Commented Aug 3, 2015 at 14:03
My fault. .GetALL() returns IQueryable<T>. It's equal to Context.Set<T>() — tmt
– tmt, Commented Aug 3, 2015 at 14:07

Philip Stuyck · Accepted Answer · 2015-08-03 14:28:21Z

You simply need projection to solve this problem, I don't have your code so this is an example of my own using my own repositories but the EF part survives pretty much because i use iquerable :

var companyList = _companyRepo
            .Query()
            .Select(c => new
            {
                label = c.Name,
                count = c.Projects.Count
            }).ToList();

I think/hope you should be able to translate that to your needs. So this returns a list of companies and the number of projects each company has as per my understanding of your problem and in my datamodel a company can have many projects so there is a navigation property between company and projects. There is no need for a join if you use the navigation properties to begin with.

Check the database for indexes on the Referer table as well. There should be an index on the foreign key to referersource.

You can use sql server profiler to check what queries are executed as well. Execute those queries in the profiler manually an let them be optimized. It will suggest missing indexes as well. But I have a feeling that the index that i mentioned above is missing.

MMZurita · Accepted Answer · 2015-08-03 15:10:42Z

0

If you have the navigation properties properly set, and if I understand your model you should be able to do this:

var src = ContextManager.ReferrerSourceContext.GetAll()
    Select(x => new 
    {
        Source = x.Name,
        RefferersCount = x.Refferers
            .Where(r => r.Created >= from && r.Created < to)
            .Count()
    });

foreach(var source in src)
{
    Console.WriteLine("src: " + source.Name + " : " + RefferersCount);
}

edited Aug 3, 2015 at 15:10

answered Aug 3, 2015 at 15:04

MMZurita

1258 bronze badges

Collectives™ on Stack Overflow

Optimize EntityFramework Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related