Database Query Performance Case Studies: Large Volumes of Data

In an earlier blog, we discussed how WebCE uses SQL Server's Query Store feature to identify and troubleshoot query performance.

Now let's dig a little deeper into one of the queries we improved. 

The Query and Code

The query we’ll be troubleshooting in this article was our top resource consumer by total execution time per hour in peak traffic and accounted for around half of our total DB execution time:

SELECT * FROM CourseSection WHERE CourseId = @CourseId;

This query was generated from a basic ORM method call used in many places throughout our code. We often see queries generated by ORMs when troubleshooting as, while they are a consistent and straightforward way to get the data you need from code you’re working on, they can be overly generic, and it is easy to overlook the performance implications of how the methods are being called. Eventually we narrowed the likely problem down to a specific instance of the method call on one of our most heavily used LMS pages.

This page has a method that queries data about a course and the user’s progress and computes various flags used by the LMS system. The snippets below show the parts of this logic that are pertinent to this query call, and how it is used to compute a flag called isChapterMustRead:

public static void ComputeComplianceFlags(Course course)

{

…

// GetCourseSections executes the Query in shown above

var courseSections = GetCourseSections(courseId);

foreach (var chapterProgressStatus in chapterProgressStatuses)

{

…

var isChapterMustRead = IsChapterMustRead(chapterProgressStatus.ChapterId), courseSections);

…

}

…

}



public static bool IsChapterMustRead(int chapterId, IEnumerable<CourseSection> courseSections)

{

var chapterSections = courseSections.Where(cs=> cs.ChapterId == chapterId).ToList();

var initialSection = chapterSections.Single(cs=> cs.SectionId == 0);

if (initialSection.IsMustReadExempt)

{

return false;

}

return chapterSections.Any(cs=> cs.SectionId != 0 && !cs.IsMustReadExempt);

}



Analyzing the Problem

With this code identified, I started running through some of the common causes of performance issues:

• Looking at the query plans in SSMS (SQL Server Management Studio), I could see that the query was using the expected index.

• Looking at how the query method was called, I could see that we weren’t executing queries in a loop structure, so we weren’t making an excessive number of queries.

• Seeing that we logically needed all the rows returned by query, we couldn’t use a more specific filter.

However, looking carefully at the IsChapterMustRead(…) method, I could see that it was using only a couple of fields to compute a single boolean value per chapter, which meant that we were pulling more data than we needed with our SELECT * query.

In fact, the table in question has a VARCHAR(MAX) column that has a lot of data that isn’t needed at all in the IsChapterMustRead method.

Building the Solution

While the primary issue can be mostly resolved by just implementing a specialized query that only returns the required columns, due to the simplicity of the IsChapterMustRead method, we could also go one step further and compute much of this method’s logic as part of query. This allows us to minimize the result set down to a single row per chapter of (ChapterId, IsChapterMustReadExempt, AreAllSectionsMustReadExempt).

The resulting query was:

SELECT

ChapterId,

CASE

WHEN SUM(CASE WHEN SectionId = 0 AND IsMustReadExempt = 1 THEN 1 ELSE 0 END) > 0

THEN 1

ELSE 0

END AS IsChapterMustReadExempt,

CASE

WHEN

SUM(CASE WHEN SectionId <> 0 AND IsMustReadExempt = 0 THEN 1 ELSE 0 END) > 0

THEN 0

ELSE 1

END AS AreAllSectionsMustReadExempt

FROM CourseSection

WHERE CourseId = @courseId

GROUP BY ChapterId;

The Impact

After implementing this new query, we saw a net 96.5% reduction of the original query’s execution time in our Database. Given the original percentage of overall DB execution time spent on this query, that translated to reducing the overall execution time load on the entire DB by around 45%!

Implementing this improved query also significantly improved the page load time of a major LMS page, resulting in a better experience for our customers! Maintaining good data access performance is a significant part of providing a quality user experience for our customers, and Query Store continues to be a valuable tool for our maintenance processes.

WebCE is regularly reviewing our practices and pushing our industries for more secure data handling across all channels. Together we can create a secure environment for all student data throughout the licensing and education process.