Optimze SQL Queries for UserControls in Sitecore


Many times we have to deal with the process of getting content from a database, then sorting it and then displaying it to the user. Recently I was given a task of optimizing a user control on the website that pulls upcoming and past events for a particular category. The way this thing was programmed was something like this:

  • Pull all the event data¬† irrespective of the category. More than 1000 records.
  • Put all events starting from current datetime in upcoming list
  • Put events dated prior to current date in past list
  • Grab the page category name and filter both upcoming and past lists
  • Display events in respective placeholder/view

This logic was spun across the site as the upcoming/past event controls were almost everywhere. It was taking up to 5 seconds to run this whole process for each user, thereby making it really slow when there are 1000s of users. The first thing I did to fix this is analyzed the queries using Sitecore’s built in X-Path query builder. I could clearly see that although grabbing all the events from the parent directory was taking less time than filtering out events per category. But I was certain that when we fit this filtered data into respective user controls things will be faster. Here’s what I did:

  • Modified sitecore query to grab events per field name.
    Using something like (C#): /sitecore/content/calendar events/calendars/meeting events//*[@@templatename = ‘Meeting’ and @Short Title = ‘” + Sitecore.Context.Item.Name + “‘ or @Short Title = ‘” + Sitecore.Context.Item.Name.ToUpper() + “‘]
  • Now that I have events specific to just one category. Only 10 to 20 records. I sorted them based on currentdate to see if its an upcoming or past event.
  • Display events in respective placeholder/view

This logic took less than a second to process the whole thing. Not sure why the previous designers thought about it before. So, the moral of the story is, If data is huge collectively (as in all categories here), pull only what you need. Others may argue.

Leave a Reply

Your email address will not be published.