Caching database queries

Topics: Customizing Orchard, Writing modules
Jun 9, 2015 at 12:19 PM
Hey everyone,

I'm about to finish a custom site: http://devosbeta.azurewebsites.net/Assortiment

As you can see this filters wines (it's completely ajax) but i was wondering how i can speed up the database query or cache it somehow.

For instance click on "rood" and some other stuff, it always takes a few seconds to load up.

Can someone point me in the right direction? I'm using the ce flat file.

Borrie
Jun 17, 2015 at 6:40 PM
Edited Jun 17, 2015 at 7:05 PM
Ok, i've found something which i would like to implement but don't know how...
http://stackoverflow.com/questions/19638508/how-to-implement-output-cache-for-a-content-part-such-as-a-widget
I'm posting my whole controller code, maybe this is also a good chance for someone to look over it and see where i can get better speed results. (please be nice this took me a long time and i'm still learning :) )
using System;
using System.Collections.Generic;
using System.Web;
using Orchard.Themes;
using System.Web.Mvc;
using Orchard;
using Orchard.Mvc;
using Orchard.ContentManagement;
using Orchard.Data;
using Orchard.Taxonomies.Models;
using Assortiment.ViewModels;
using Assortiment.Models;
using Orchard.Taxonomies.Services;
using System.Collections;
using Orchard.Core.Common.Models;
using Orchard.ContentManagement.Records;
using System.Xml.Linq;
using System.Text;
using System.Linq;
using System.Linq.Dynamic;
using Orchard.UI.Navigation;
using Orchard.Settings;
using Orchard.DisplayManagement;
using System.Web.Routing;
using System.Web.Mvc.Html;

namespace Assortiment.Controllers
{
    
    public class AssortimentController : Controller
    {
        const int pageSize = 12;
        private readonly ITaxonomyService _taxonomyService;
        private readonly IOrchardServices _orchardServices;
        private readonly IContentManager _contentManager;
        private readonly IRepository<ContentItemVersionRecord> _contentItemVersions;
        private readonly List<ContentItem> gefilterd = new List<ContentItem>();
        private StringBuilder finalquery = new StringBuilder();
        private readonly ISiteService _siteService;
        readonly IEnumerable<String> taxonomyNames = new List<String> { "Dranken", "Land", "Prijs", "Kelderrestjes" };
        private dynamic Shape { get; set; }
        public AssortimentController(
                                ITaxonomyService taxonomyService,
                                IOrchardServices orchardServices,
                                IContentManager contentManager,
                                IRepository<ContentItemVersionRecord> contentItemVersions,
                                ISiteService siteService,
                                IShapeFactory shapeFactory
                              )
        {
            _orchardServices = orchardServices;
            _contentManager = contentManager;
            _taxonomyService = taxonomyService;
            _contentItemVersions = contentItemVersions;
            _siteService = siteService;
            Shape = shapeFactory;
        }

       [Themed]
        public ActionResult Index(List<taxTermViewModel> postedTerms, int sortBy = 1, bool isAsc = true, int page = 1)
        {      
            
           if (postedTerms == null )
            {
               List<AlleWijnenIDPrijsKleurLandViewModel> Allewijnen =  ZoekAlleWijnen();
               finalquery.AppendFormat("prijs >= {0}", 0.00);
               List<int> filteredIds = SorteerIds(sortBy, isAsc, Allewijnen, finalquery);
               BouwWijnShapesMetPager(filteredIds, page);
               if (Request.IsAjaxRequest())
               {
                   return PartialView("_sortandpost", GetTermsInitialModel());
               }
               else
                return View(GetTermsInitialModel());
            }
            if (Request.IsAjaxRequest())
            {
                return PartialView("_sortandpost", GetTermsModel(postedTerms, page, sortBy, isAsc));
            }
            else
            {
                return View(GetTermsModel(postedTerms, page, sortBy, isAsc));
            }
        }
Jun 17, 2015 at 7:06 PM
rest of the code:
 
        public List<taxTermViewModel> GetTermsModel(List<taxTermViewModel> postedTerms, int page, int sortBy, bool isAsc)
        {
            List<AlleWijnenIDPrijsKleurLandViewModel> Allewijnen = ZoekAlleWijnen();
            int checkifallempty = 0;
            List<taxTermViewModel> models = new List<taxTermViewModel>();
            //IEnumerable<int> Ids = GetTaxonomyIdsbyName();
            int andteller = 0;
            for (var i = 0; i < postedTerms.Count; i++)
            {
                var selectedTerms = new List<TermPart>();
                var postedTermIds = new string[0];
                var AllTerms = new List<TermPart>();
                if (postedTerms[i].PostedTerms == null) postedTerms[i].PostedTerms = new PostedTerms();

                if (postedTerms[i].PostedTerms.TermIds != null && postedTerms[i].PostedTerms.TermIds.Any())
                {
                    if (postedTerms[i].PostedTerms.TermIds[0].Contains(","))
                    {
                        string texttosplit = postedTerms[i].PostedTerms.TermIds[0].ToString();

                        postedTermIds = texttosplit.Split(',');
                    }
                    else
                        postedTermIds = postedTerms[i].PostedTerms.TermIds;
                }

                if (postedTermIds != null && postedTermIds.Any())
                {
                    if (andteller > 0) finalquery.Append(" AND ");
                    finalquery.Append("(");
                    checkifallempty = 1;
                    int kleur = 0;
                    int land = 0;
                    int prijs = 0;

                    for (var j = 0; j < postedTermIds.Count(); j++)
                    {
                        if (postedTerms[i].TaxonomyName == "Dranken")
                        {
                            if (kleur > 0) finalquery.Append(" OR ");
                            finalquery.AppendFormat("KleurID == {0}", postedTermIds[j]);
                            kleur++;
                        }

                        if (postedTerms[i].TaxonomyName == "Land")
                        {
                            if (land > 0) finalquery.Append(" OR ");
                            finalquery.AppendFormat("LandID == {0}", postedTermIds[j]);
                            land++;
                        }

                        if (postedTerms[i].TaxonomyName == "Prijs")
                        {
                            if (prijs > 0) finalquery.Append(" OR ");
                            if (postedTermIds[j] == "24")
                            {
                                finalquery.AppendFormat("prijs < {0}", 6);
                            }

                           if (postedTermIds[j] == "25") finalquery.Append("(prijs >= 6 AND prijs < 10)");

                            if (postedTermIds[j] == "26") finalquery.Append("(prijs >= 10 AND prijs < 20)");


                            if (postedTermIds[j] == "47")
                            {
                                finalquery.AppendFormat("prijs >= {0}", 20);
                            }

                            prijs++;
                        }

                        if (postedTerms[i].TaxonomyName == "Kelderrestjes")
                        {
                           finalquery.AppendFormat("KelderID == {0}", postedTermIds[j]);
                          
                        }

                    }

                    finalquery.Append(") ");
                    andteller++;
                }

                if (postedTermIds.Any())
                {
                    selectedTerms = _taxonomyService.GetTerms(GetTaxonomyIdsbyName().ElementAt(i))
                     .Where(x => postedTermIds.Any(s => x.Id.ToString().Equals(s)))
                     .ToList();
                }

                models.Add(new taxTermViewModel() { AvailableTerms = _taxonomyService.GetTerms(GetTaxonomyIdsbyName().ElementAt(i)), SelectedTerms = selectedTerms, PostedTerms = postedTerms[i].PostedTerms, TaxonomyName = postedTerms[i].TaxonomyName });
            }


            if (checkifallempty == 1)
            {

                List<int> filteredIds = SorteerIds(sortBy, isAsc, Allewijnen, finalquery);
                BouwWijnShapesMetPager(filteredIds, page);

            }
            else
            {
                finalquery.AppendFormat("prijs >= {0}", 0);
                List<int> filteredIds = SorteerIds(sortBy, isAsc, Allewijnen, finalquery);
                BouwWijnShapesMetPager(filteredIds, page);
            }

            return models;
        }


        private List<AlleWijnenIDPrijsKleurLandViewModel> ZoekAlleWijnen()
        {
            int[] allewijnenIds = _contentManager.Query().ForVersion(VersionOptions.Published).List().Where(r => r.ContentType == "Wijn").Select(x => x.Id).ToArray();
            List<AlleWijnenIDPrijsKleurLandViewModel> allewijnenmetprijslandenkleurlijst = new List<AlleWijnenIDPrijsKleurLandViewModel>();
            for (int tel = 0; tel < allewijnenIds.Count(); tel++)
            {

                dynamic contentItem = _contentManager.Get(allewijnenIds[tel]);
                double prijs = Convert.ToDouble(contentItem.Wijn.Prijs.Value);
                int jaar = Convert.ToInt32(contentItem.Wijn.Jaar.Value);
                int kelder;
                List<TermPart> termsvoorcontentitem = _taxonomyService.GetTermsForContentItem(allewijnenIds[tel]).ToList();
                // Opgepast, onderstaande is bijgevoegd voor de kelderrestjes, indien er een taxonomy bijgeplaatst wordt met de check veranderen.
                if (termsvoorcontentitem.Count() == 3) {
                    kelder = termsvoorcontentitem[2].Id;
                }
                else { kelder = 0; }
                allewijnenmetprijslandenkleurlijst.Add(new AlleWijnenIDPrijsKleurLandViewModel
                {
                    WijnID = allewijnenIds[tel],
                    prijs = prijs,
                    Jaar = jaar,
                    KleurID = termsvoorcontentitem[0].Id,
                    LandID = termsvoorcontentitem[1].Id,
                    KelderID = kelder
                });
            }

            return allewijnenmetprijslandenkleurlijst;
        }

        private List<taxTermViewModel> GetTermsInitialModel()
        {
            var selectedTerms = new List<TermPart>();
            List<taxTermViewModel> models = new List<taxTermViewModel>();


            for (int i = 0; i < GetTaxonomyIdsbyName().Count(); i++)
            {

                models.Add(new taxTermViewModel() { AvailableTerms = _taxonomyService.GetTerms(GetTaxonomyIdsbyName().ElementAt(i)), SelectedTerms = selectedTerms, TaxonomyName = taxonomyNames.ElementAt(i) });
            }

            return models;


        }


        private void BouwWijnShapesMetPager(List<int> filteredIDs, int page)
        {
            for (int teller = 0; teller < filteredIDs.Count(); teller++)
            {
                gefilterd.AddRange(_contentManager.Query().List().Where(r => r.Id == filteredIDs[teller]).ToList());
            }

            var displayType = "Summary";
            var contentItemShapes = gefilterd.Select(x => _contentManager.BuildDisplay(x, displayType)).Distinct();
            var wijnenmetPager = contentItemShapes.Skip((page - 1) * pageSize).Take(pageSize);
            ViewBag.CurrentPage = page;
            ViewBag.PageSize = pageSize;
            ViewBag.TotalPages = Math.Ceiling((double)contentItemShapes.Count() / pageSize);
            ViewBag.Allewijnen = wijnenmetPager;
        }

        private List<int> SorteerIds(int sortBy, bool isAsc, List<AlleWijnenIDPrijsKleurLandViewModel> Allewijnen, StringBuilder finalquery)
        {
            List<int> filteredIDs = new List<int>();
            ViewBag.SortBy = sortBy;
            ViewBag.IsAsc = isAsc;

            switch (sortBy)
            {

                case 1:

                    if (isAsc == true)
                    {
                        return filteredIDs = Allewijnen.AsQueryable().Where(finalquery.ToString()).OrderBy(s => s.prijs).Select(c => c.WijnID).ToList();
                    }
                    else

                        return filteredIDs = Allewijnen.AsQueryable().Where(finalquery.ToString()).OrderByDescending(s => s.prijs).Select(c => c.WijnID).ToList();
                case 2:
                    if (isAsc == true)
                    {
                        return filteredIDs = Allewijnen.AsQueryable().Where(finalquery.ToString()).OrderBy(s => s.Jaar).Select(c => c.WijnID).ToList();
                    }
                    else
                        return filteredIDs = Allewijnen.AsQueryable().Where(finalquery.ToString()).OrderByDescending(s => s.Jaar).Select(c => c.WijnID).ToList();

                default:
                    return filteredIDs = Allewijnen.AsQueryable().Where(finalquery.ToString()).OrderBy(s => s.prijs).Select(c => c.WijnID).ToList();
            }
        }


        IEnumerable<int> GetTaxonomyIdsbyName()
        {

            return taxonomyNames.Select(name => _taxonomyService.GetTaxonomyByName(name).Id);
        }



    }
}
Developer
Jun 21, 2015 at 7:11 PM
I didn't analyze everything, but the following block jumped out at me:
for (int teller = 0; teller < filteredIDs.Count(); teller++)
{
      gefilterd.AddRange(_contentManager.Query().List().Where(r => r.Id == filteredIDs[teller]).ToList());
}
You'e doing a content query for each item in filteredIDs. Since these seem to be content IDs, you could optimize this by loading the content items using a single query, like this:
gefiltered = _contentManager.GetMany(filteredIDs);
Further more there seems to be some things that don't vary per request, such as the wines you're loading and the taxonomy terms. Perhaps you could cache these items (warning: don't cache content items such as terms themselves, but rather some DTO representing them to prevent caching database bound objects) and optimize the performance.
Jun 26, 2015 at 3:26 PM
Skywalker,

Thanks for your answer, I don't think .GetMany works this way (I tried :) )

What i'm looking for is caching this method:
        private void BouwWijnShapesMetPager(List<int> filteredIDs, int page)
        {
            for (int teller = 0; teller < filteredIDs.Count(); teller++)
            {
                gefilterd.AddRange(_contentManager.Query().List().Where(r => r.Id == filteredIDs[teller]).ToList());
            }


            var displayType = "Summary";
            var contentItemShapes = gefilterd.Select(x => _contentManager.BuildDisplay(x, displayType)).Distinct();
            var wijnenmetPager = contentItemShapes.Skip((page - 1) * pageSize).Take(pageSize);
            ViewBag.CurrentPage = page;
            ViewBag.PageSize = pageSize;
            ViewBag.TotalPages = Math.Ceiling((double)contentItemShapes.Count() / pageSize);
            ViewBag.Allewijnen = wijnenmetPager;
        }
In this example they cache shapes in a driver but i don't know how to implement this in my controller or how to link it to my code:

http://stackoverflow.com/questions/19638508/how-to-implement-output-cache-for-a-content-part-such-as-a-widget

Kr,

Borrie