Reducing the Number of SQL Queries on Your Website
I’ve found that quite often the same data is used over and over again on a website. I would bet that you do the same thing, retrieving the same DataSet or a subset of entries of the same DataSet on multiple pages. Without any additional thought on configuration, the system will execute as many SQL queries as there are listings on your website. Let’s have a look at how to bring down the number of executed SQL queries to a single one.
There are basically two scenarios, as mentioned at the beginning of this article:
- You are displaying the same listing on multiple places (e.g., the same Recent news)
- You are displaying a subset of the same dataset on multiple places (e.g., a subset of pages filtered out by a variable, such as category or page name)
If you have scenario one, then no custom development is required. The only thing you have to do is to set the same Cache item name (cache key) on all web parts which are using the same DataSet. That means that an SQL query is executed only the first time the data is requested. All remaining web parts will be retrieving the DataSet from the system memory based on your custom cache key. So, for scenario one we achieved what I’d promised at the beginning, to use only one SQL query across all components, which are using the same DataSet (please note that the styling and HTML may change, since we are only caching the data, not the HTML output of the component).
Now, let’s take a look at scenario two. Let’s say we want to display a subset of pages filtered out by page alias path or page name. If you do that for each individual listing, then you’ll end up with multiple different SQL queries because your filter WHERE condition will be different from listing to listing. You can, of course, cache these listings separately and specify a different cache key for each individual listing, but that means that you’ll end up with multiple duplicate data rows in your system cache, which may quickly eat up your server memory. An alternative is to take out the WHERE condition from the equation and to perform the filtering of the data in code. This approach will be a bit slower than having the different listings cached separately. However, it is a small tradeoff considering how much memory and how many SQL queries you can save (this, of course, depends on the project you are working on). Also, it’s easier to scale the website server by adding web farms than scaling the database server.
I’ve decided to implement a proof of concept of such a setup. The first step is to take the DataSource web part you want to use. I’ve chosen the Query Data Source web part due to its flexibility. I’ve cloned the web part (named it Enhanced Query Data Source) and modified the code behind in the following way:
/// <summary>
/// Initializes the control properties.
/// </summary>
protected void SetupControl()
{
if (StopProcessing)
{
// Do nothing
}
else
{
// Setup query data source
srcElem.FilterName = ValidationHelper.GetString(GetValue("WebPartControlID"), ID);
srcElem.LoadPagesIndividually = LoadPagesIndividually;
srcElem.CacheItemName = CacheItemName;
srcElem.CacheDependencies = CacheDependencies;
srcElem.CacheMinutes = CacheMinutes;
srcElem.OrderBy = OrderBy;
//srcElem.WhereCondition = WhereCondition; // removing the WHERE condition out of the equation
srcElem.SelectedColumns = Columns;
srcElem.SelectTopN = SelectTopN;
srcElem.QueryName = QueryName;
srcElem.SourceFilterName = FilterName;
}
}
…
protected override void OnPreRender(EventArgs e)
{
DataSet ds = (DataSet)srcElem.DataSource;
DataSet tempDS = ds.Clone();
tempDS.Tables[0].Merge(ds.Tables[0].Select(WhereCondition).CopyToDataTable());
srcElem.DataSource = tempDS;
base.OnPreRender(e);
}
At first, I’ve removed the WhereCondition from the equation (in the SetupControl method), so it doesn’t impact the resulting SQL query. This means that no matter what the WHERE condition looks like, the system will always be retrieving the same DataSet and, therefore, we can cache it under the same cache key. The second step was to implement the OnPreRender method, where I am filtering the DataTable using the quite useful DataTable.Select() method. Please note that this implementation isn’t suitable for advanced WHERE conditions as it’s not executed over the database. Please refer to the MSDN documentation of the mentioned method for details.
And that’s it. Easy, don’t you think? Here is my resulting setup. I’ve placed two Enhanced Query Data Source web parts onto the same page (that’s, however, not a requirement, they could be placed on different pages). I connected them to different Basic repeater web parts, however, their configuration is exactly the same, except the WHERE condition. You can find the configuration of these components in the picture below (also, note the different set of data listed by the repeaters).
When viewing the page on the live site, while the SQL debug mode is enabled, you can clearly see that only one SQL query is executed, retrieving the whole DataSet at once (it contains 820 rows), even if two web parts with different SQL WHERE conditions are used:
We can also double check the Cache items debug to verify that, indeed, only one cache item with the key my_key is created.
This simple modification can help you to reduce the amount of SQL queries across your website drastically. Just go through your pages and count how many times you end up working with the same DataSet. For example, if you have an e-commerce website, just go through all of the product lists you have on your pages. Now it won’t matter how many times you end up using the same DataSet or subset of data, you’ll always be executing only one SQL query.
This article is only a proof of concept, and the code could be improved. If you have any project specific questions, please shoot us an email to consulting@kentico.com. You can download the modified web part from here.