4
\$\begingroup\$

I've been trying to fix this performance bottleneck in my web app for a while now.

Here is what the data looks like on my web page:

Screenshot of categories with content counts

Here is how I get this data in the controller:

var allCategories = categoryRepository
                    .Categories
                    .Select(x => new
                    {
                        x.CategoryID,
                        x.FriendlyName,
                        x.RoutingName,
                        x.ParentID
                    })
                    .ToList();


var allListings = listingRepository
                  .Listings
                  .Where(x => x.Lister.Status != cancelledStatus)
                  .Select(x => new
                  {
                      x.SelectedCategoryOneID,
                      x.SelectedCategoryTwoID,
                      x.SelectedCategoryThreeID,
                  })
                  .ToList();


List<CategoryBrowseIndexViewModel> categories = 
allCategories
.Where(x => x.ParentID == null)
.Select(a => new CategoryBrowseIndexViewModel
{
    CategoryID = a.CategoryID,
    FriendlyName = a.FriendlyName,
    RoutingName = a.RoutingName,
    ListingCount = allListings
                   .Where(x => x.SelectedCategoryOneID == a.CategoryID)
                   .Count(),

    BrowseCategoriesLevelTwoViewModels =
    allCategories
    .Where(x => x.ParentID == a.CategoryID)
    .Select(b => new BrowseCategoriesLevelTwoViewModel
    {
        CategoryID = b.CategoryID,
        FriendlyName = b.FriendlyName,
        RoutingName = b.RoutingName,
        ParentRoutingName = a.RoutingName,
        ListingCount = allListings
                       .Where(x => x.SelectedCategoryTwoID == b.CategoryID)
                       .Count(),

        BrowseCategoriesLevelThreeViewModels =
        allCategories
        .Where(x => x.ParentID == b.CategoryID)
        .Select(c => new BrowseCategoriesLevelThreeViewModel
        {
            CategoryID = c.CategoryID,
            FriendlyName = c.FriendlyName,
            RoutingName = c.RoutingName,
            ParentRoutingName = b.RoutingName,
            ParentParentID = a.CategoryID,
            ParentParentRoutingName = a.RoutingName,
            ListingCount = allListings
                           .Where(x => x.SelectedCategoryThreeID == c.CategoryID)
                           .Count()
        })
        .OrderBy(x => x.FriendlyName)
        .ToList()
    })
    .OrderBy(x => x.FriendlyName)
    .ToList()
})
.OrderBy(x => x.FriendlyName == jobVacanciesFriendlyName)
.ThenBy(x => x.FriendlyName == servicesLabourHireFriendlyName)
.ThenBy(x => x.FriendlyName == goodsEquipmentFriendlyName)
.ToList();

I make two database calls to get what I need into memory, and construct the data model in memory. I use nested foreach loops in the view to display the categories and the listing counts.

What I want to do is make one database call, have the database do all the work and send back the entire object model, in one go. Can this be done?

My repositories use IQueryable, like this:

public class EFListingRepository : IListingRepository
{
    private EFDbContext context = new EFDbContext(); 

    IQueryable<Listing> Listings { get { return context.Listings; } }
}

To do one database call I guess I would have to use one dbContext, like this:

EFDbContext browseControllerDbContext = new EFDbContext(); 

var allCategories = browseControllerDbContext 
                    .Categories
                    .Select(x => new
                    {
                        x.CategoryID,
                        x.FriendlyName,
                        x.RoutingName,
                        x.ParentID
                    });


var allListings = browseControllerDbContext 
                  .Listings
                  .Where(x => x.Lister.Status != cancelledStatus)
                  .Select(x => new
                  {
                      x.SelectedCategoryOneID,
                      x.SelectedCategoryTwoID,
                      x.SelectedCategoryThreeID,
                  });

But I get stuck at this point. How do I make a single call to the database to populate the List<CategoryBrowseIndexViewModel> categories variable, and have everything inside it loaded into memory in one go, ready to be looped through?

The Category entity looks like this:

public class Category
{
    public long CategoryID { get; set; }

    public string FriendlyKey { get; set; }

    public string FriendlyName { get; set; }

    public string RoutingKey { get; set; }

    public string RoutingName { get; set; }  

    public long? ParentID { get; set; } 
}

The Listing entity is similar in that it's just a (large) class with simple properties, as above. Category and Listing are not related by navigation properties or a foreign key.

Here are the viewmodels, which are nested. They're looped over with nested foreach's in the view:

The CategoryBrowseIndexViewModel looks like this:

public class CategoryBrowseIndexViewModel
{
    public List<BrowseCategoriesLevelTwoViewModel> BrowseCategoriesLevelTwoViewModels { get; set; }

    public long CategoryID { get; set; }

    public string FriendlyName { get; set; }

    public string RoutingName { get; set; }

    public string ParentRoutingName { get; set; }

    public long? ParentID { get; set; }

    public long ListingCount { get; set; }
}

And BrowseCategoriesLevelTwoViewModel

public class BrowseCategoriesLevelTwoViewModel
{
    public List<BrowseCategoriesLevelThreeViewModel> BrowseCategoriesLevelThreeViewModels { get; set; }

    public long CategoryID { get; set; }

    public string FriendlyName { get; set; }

    public string RoutingName { get; set; }

    public string ParentRoutingName { get; set; }

    public long? ParentID { get; set; }

    public long ListingCount { get; set; }
}

BrowseCategoriesLevelThreeViewModel

public class BrowseCategoriesLevelThreeViewModel
{
    public long CategoryID { get; set; }

    public string FriendlyName { get; set; }

    public string RoutingName { get; set; }

    public string ParentRoutingName { get; set; }

    public long? ParentID { get; set; }

    public long? ParentParentID { get; set; }

    public string ParentParentRoutingName { get; set; }

    public long ListingCount { get; set; }
}

EDIT: After testing how long everything was taking using Stopwatch, from the time the controller was hit to the end of the view, the main source of slowness, unbelievably, was Html.ActionLink() in a foreach loop. Hardcoding the <a> tag sped up this page by about 3-4 seconds. I couldn't believe how slow the Html helpers are! So I guess the lesson is don't assume the database is the bottleneck! I've checked all the other loops in my codebase. In the controller loops I've been careful not to make repeated database calls, for example I was initially using IEnumerable instead of IQueryable without understanding how it worked. I understand immediate execution in linq code (Count, Average etc. ) and how it works in IQueryable. Navigation properties can make repeated calls if you're not careful, even if a list of the main entity has been loaded into memory. Thank you everyone, I've learned alot from your suggestions.

\$\endgroup\$
  • \$\begingroup\$ Welcome to CR! "Snippetized" code works well for Stack Overflow, but much less so for Code Review. I'd suggest you include the actual repository class(es) and, at least, the actual method (that's a controller method, right?) that's doing this.. query. I suppose your database has all the necessary indexes and that you've profiled the EF-generated SQL? \$\endgroup\$ – Mathieu Guindon Nov 8 '16 at 0:27
  • \$\begingroup\$ @Mat'sMug, thanks, I've added more detail. The database is in Azure. The main thing I'm trying to find out is can I do this query in the database entirely? I don't know how to do that. (I'm assuming it will be faster.) \$\endgroup\$ – nmit026 Nov 8 '16 at 0:55
  • 1
    \$\begingroup\$ @nmit026 not necessarily terrifying - it provides us more context and this is a good thing ;-) without the data models the queries are often extremely hard to understand. \$\endgroup\$ – t3chb0t Nov 8 '16 at 5:07
  • 1
    \$\begingroup\$ Did you try what happens if you use the IQueryable versions of allCategories and allListings in the categories query? That should be enough to get it all in one query (if you use one context instance!). Not that that will necessarily be faster. It's easy enough to generate queries that are too large to run efficiently. \$\endgroup\$ – Gert Arnold Nov 8 '16 at 19:33
  • 1
    \$\begingroup\$ You can simply log the SQL statements by the DbContext.Database.Log property. \$\endgroup\$ – Gert Arnold Nov 8 '16 at 22:01
1
\$\begingroup\$

Categories

I suggest changing this design before it's to late. Having a table (?) Listing with separate columns for each category (SelectedCategoryOneID, SelectedCategoryTwoID, SelectedCategoryThreeID) is a very bad idea. What if you introduce a new category? You need to change the database model.

The categories build a tree. This means you don't have to store each category id with a listing. You know the parents so they are automatically selected if any child category was chosen.


I find you can keep it really simple. For the sake of the example I removed and renamed some properties. It should just demonstrate the sample solution - not your final one so I used simplified objects.

public class Category
{
    public long Id { get; set; }

    public long? ParentId { get; set; }

    public string Name { get; set; }
}

public class Listing
{
    public long CategoryId { get; set; }
}

You now want to list all categories together with their listing-counts. If you keep it simple you can achieve this with a join.

I assume you have these two tables:

var categories = new[]
{
    new Category { Id = 1, ParentId = null, Name = "Foo" },
    new Category { Id = 2, ParentId = 1, Name = "Bar" },
    new Category { Id = 3, ParentId = 1, Name = "Baz" },
    new Category { Id = 4, ParentId = 3, Name = "Qux" },
    new Category { Id = 5, ParentId = 4, Name = "Waldo" },
    new Category { Id = 6, ParentId = 4, Name = "Corge" },
};

var listings = new[] 
{
    new Listing { CategoryId = 1 },
    new Listing { CategoryId = 1 },
    new Listing { CategoryId = 2 },
    new Listing { CategoryId = 2 },
    new Listing { CategoryId = 2 },
    new Listing { CategoryId = 3 },
    new Listing { CategoryId = 4 },
    new Listing { CategoryId = 4 },
    new Listing { CategoryId = 4 },
    new Listing { CategoryId = 4 },
    new Listing { CategoryId = 5 },
};

You can join them on the category-id:

var cats = 
    from c in categories
    join l in listings on c.Id equals l.CategoryId into catListings
    select new 
    {
        c,
        ListingCount = catListings.Count()
    };

This will give you a list of categories with the respecitve listing-counts:

Cats

ViewModels

What about the views? You don't need three of them. I guess you have three categories. You add one category and you need to create a new view for it and adjust all your queries - a lot of work for a small change.

I find you should have only one view-model with a single view that can recursively (or with a stack) display all categories. You start with the ones that have no parents and and get then the ones that have this one as their parent and so one until you have displayed them all.

A new type for the results:

class CategoryListing
{ 
    public Category Category { get; set; }
    public int ListingCount { get; set; }
}

Adjusted query:

var cats =
    from c in categories
    join l in listings on c.Id equals l.CategoryId into catListings
    select new CategoryListing
    {
        Category = c,
        ListingCount = catListings.Count()
    };

Print results:

static void PrintCategories(IEnumerable<CategoryListing> categories)
{
    var rootCategories = categories.Where(c => !c.Category.ParentId.HasValue);

    var cats = new Stack<CategoryListing>(rootCategories);
    var depths = new Stack<int>();
    depths.Push(0);

    while (cats.Any())
    {
        var cat = cats.Pop();
        var catDepth = depths.Any() ? depths.Pop() : 0;

        Console.WriteLine($"{new string('-', catDepth)}{cat.Category.Name} ({cat.ListingCount})");       

        var childCats = categories.Where(c => c.Category.ParentId == cat.Category.Id);
        foreach (var childCat in childCats)
        {
            if (childCat.Category.ParentId.HasValue)
            {
                cats.Push(childCat);
                depths.Push(catDepth + 1);
            }
        }
    }
}

And these are the results:

Foo (2)
-Baz (1)
--Qux (4)
---Corge (0)
---Waldo (1)
-Bar (3)
|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Very good code, thanks! Love the stack. Naming things is hard: I've changed SelectedCategoryOneID to LevelOneSelectedCategoryID, because there are three levels in the category hierarchy. This is unlikely to change. If I add a fourth level, then yes a database migration will be required. Each listing is required to select categories to the lowest level in the hierarchy. If I add another top-level category (three so far), no changes are necessary. I can do things like: IQueryable<Listing> listings = listingRepository.Listings.Where(x => x.LevelTwoSelectedCategoryID == categoryTwo.CategoryID); \$\endgroup\$ – nmit026 Dec 2 '16 at 0:17
  • \$\begingroup\$ I didn't want to store a category ID for each of the three levels required in the listing entity. But I thought it would be faster and just generally easier to work with than storing the category ID of the lowest level and then looking up the parent ID of that for the second level and then the parent of the parent, just to pull back all the listings that are in a level 1 or 2 category (as I do elsewhere). I thought the code snippet in the comment above was short and easy, fast, and the design unlikely to change. Storing two extra fields seemed trivial. What would be faster? Thank you! \$\endgroup\$ – nmit026 Dec 2 '16 at 0:32
  • \$\begingroup\$ Actually, should be SelectedLevelOneCategoryID, SelectedLevelTwoCategoryID etc. or maybe just LevelOneCategoryID \$\endgroup\$ – nmit026 Dec 2 '16 at 1:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.