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;
        }
    }

Answer 1

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.)

Answer 2

Some tips:

1. 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)

2. 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

3. 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.

4. as the last resort, write your own SQL for the most exposed queries