Performance tuning Entity Framework queries

Question

So I've developed a dashboard which queries a database. The database has data stored in it from google analytics for a website we have.

I'm using ASP.NET MVC 5, EF, Linq with Telerik controls/widgets. The controller instantiates a service layer where I have my db context and business logic. Each svc.method() pertains to a specific result set I'm after that I package up in the VM for unpackaging into a widget within the view.

Currently, the response time in the network tab of Google Chrome is 5.6 seconds. I've illustrated one of the 8 methods to show you my approach.

My question is; how can I improve performance so that the page loads faster? Would making each method async improve it?

Thanks in advance for any advice you can provide.

Controller:

    public ActionResult WebStats()
    {
        //other code removed for brevity

        //Service layer where the db is queried and the business logic is performend
        WebStatsService svc = new WebStatsService();

        //view model 
    WebStatsViewModel vm = new WebStatsViewModel();

        vm.PageViews = svc.GetPageViews(vm);
        vm.UniquePageViews = svc.GetUniquePageViews(vm);
        vm.UserRatioByCountry = svc.GetUserRatioByCountry(vm);
        vm.PageViewsByCountry = svc.GetPageViewsByCountry(vm);
        vm.TopTenHealthCenters = svc.GetTopTenHealthCenters(vm);
        vm.UserTypeRatio = svc.GetUserTypeRatio(vm);
        vm.TopTenHealthCentersByDateRange = svc.GetTopTenHealthCentersByDateRange(vm);
        vm.ReferralSources = svc.GetTopTenReferralSources(vm);//Get top 10 referral paths


        return View(vm);
    }

Service:

    public List<PageViews> GetPageViews(WebStatsViewModel vm)
    {
        using (ApplicationDbContext db = new ApplicationDbContext())
        {
            List<PageViews> pageViewStats = new List<PageViews>();

            var results = db.PageStats.Where(x => (vm.CMS.Equals("All") || x.Source.Equals(vm.CMS))
                                               && (vm.HealthCenter.Equals("All") || x.HealthSectionName.Equals(vm.HealthCenter))
                                               && (vm.Country.Equals("All") || x.Country.Equals(vm.Country))
                                               && (vm.City.Equals("All") || x.City.Equals(vm.City))
                                               && (x.Date >= vm.StartDate)
                                               && (x.Date <= vm.EndDate)
                                            ).Select(x => new
                                            {
                                                Date = x.Date,
                                                Total = x.PageViews
                                            }).ToList();

            var distinctDate = results.OrderBy(x => x.Date).Select(x => x.Date).Distinct();

            foreach (var date in distinctDate)
            {
                PageViews pageViewStat = new PageViews();

                pageViewStat.Date = date.Value.ToShortDateString();
                pageViewStat.Total = results.Where(x => x.Date == date).Sum(x => x.Total);

                pageViewStats.Add(pageViewStat);
            }

            return pageViewStats;
        }
    }

This is really too broad a question for this site. You may get some benefit from making it async, but your mileage may vary on that. We may be able to help you performance tune one of your queries here, but not the general layout/performance. — DavidG
– DavidG, Commented Aug 29, 2016 at 21:50
Thanks @DavidG. I'm looking at improving the query response time, the view performance is a different story. That said, I'm looking for advise on different approaches e.g. would making each widget a partial view improve response performance? I'm looking for suggestions on different strategies. — JReam
– JReam, Commented Aug 29, 2016 at 21:53
Advice would just be opinion, another reason why this isn't a good fit for SO. Having sad that, I'd be tempted to load the data on your view via AJAX calls, that way the page will display as each part is retrieved. — DavidG
– DavidG, Commented Aug 29, 2016 at 22:00

Ivan Stoev · Accepted Answer · 2016-08-30 15:00:15Z

3

Here are some tips for EF queries:

(1) Avoid mixing constant and actual predicate in dynamic filters like this:

(vm.CMS.Equals("All") || x.Source.Equals(vm.CMS))

It might look concise, but generates awful and inefficient SQL. Instead, use if statements and chained Where:

// Base query including static filters
var query = db.PageStats.AsQueryable();
// Apply dynamic filters
if (!vm.CMS.Equals("All"))
    query = query.Where(x => x.Source.Equals(vm.CMS));
// ...
// The rest of the query
query = query.Select(...

(2) Try returning as less data as possible from the SQL query.

For instance, your query is populating a list with (Date, Total) pairs, which you then manually (and not very efficiently) group by Date and take Sum(Total). Instead, you can make the EF query directly return that grouped/aggregated data.

Applying all that to your example would lead to something like this:

using (ApplicationDbContext db = new ApplicationDbContext())
{
    var query = db.PageStats
        .Where(x => x.Date >= vm.StartDate && x.Date <= vm.EndDate);

    if (!vm.CMS.Equals("All"))
        query = query.Where(x => x.Source.Equals(vm.CMS));
    if (!vm.HealthCenter.Equals("All"))
        query = query.Where(x => x.HealthSectionName.Equals(vm.HealthCenter));
    if (!vm.Country.Equals("All"))
        query = query.Where(x => x.Country.Equals(vm.Country));
    if (!vm.City.Equals("All"))
        query = query.Where(x => x.City.Equals(vm.City));

    query = query
        .GroupBy(x => x.Date)
        .Select(g => new
        {
            Date = g.Key,
            Total = g.Sum(x => x.PageViews)
        })
        .OrderBy(x => x.Date);

    var pageViewStats = query
        .AsEnumerable() // SQL query ends here
        .Select(x => new PageViews
        {
            Date = x.Date.Value.ToShortDateString(),     
            Total = x.Total
        })
        .ToList();

    return pageViewStats;
}

You can try and compare the performance with the original.

(Note: for this specific query we need to use two projections - one temporary in SQL query and one final in the in memory query. This is because of the need of ToShortDateString() method which is not supported for the SQL query. In most of the cases a single final projection in the SQL query would be sufficient.)

edited Aug 30, 2016 at 15:00

answered Aug 30, 2016 at 9:59

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

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

2 Comments

JReam Over a year ago

Thanks, Ivan. Goes to show how I still need to improve my linq abilities... So does the query not execute until .AsEnumerable()? Curious how your suggestion for the SQL execution is deferred and when it executes.

Ivan Stoev Over a year ago

Actually it's get executed by the ToList call. AsEnumerable just switches the context from IQueryable (hence under control of the query provider) to IEnumerable (i.e. LINQ to Objects - based on extension methods contained in the Enumerable class). As long as you keep chaining methods on IQueryable, they are translated to SQL. IEnumerable methods are executed in memory.

Martin Staufcik · Accepted Answer · 2016-08-29 22:23:18Z

0

Some tips:

Indexes - index columns that appear in the where clause of select operations, use SQL profiler to detect 'table scan' operations and add indexes to avoid them (replace them with index search or clustered index search)
Caching - store the trace from SQL profiler above to a table in DB (SQL Profiler can do it) and group SQL commands by sql text, this may show some repeating selects that can be avoided by caching
Glimpse - can count SQL commands per web request, the number can be suprising if the web application has not been optimized yet. Glimpse can tell much more, for example how much time of the total time of a web request is spent on the server and how much time in the web browser rendering the page.
as the last resort, write your own SQL for the most exposed queries

edited Aug 29, 2016 at 22:23

answered Aug 29, 2016 at 22:11

Martin Staufcik

9,6728 gold badges57 silver badges77 bronze badges

Collectives™ on Stack Overflow

Performance tuning Entity Framework queries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related