How to Cache SQL Server Data for a Web Application

By: Pablo Echeverria | Updated: 2023-04-21 | Comments | Related: > Application Development

Problem

One of the slowest parts of any application is data retrieval. So, caching data for a period of time improves performance and response time for end users while also reducing the load on the database server, which is more expensive than an application server in terms of CPU and memory. How is this accomplished in an ASP.Net core web application when retrieving SQL Server data?

Solution

In a previous article, connecting from a Visual Studio 2019 ASP.NET Core Web Application to SQL Server, we covered how to build a simple web application to receive SQL Server data. We will start with this previous article as the basis for this article, where we will introduce code to cache the data on the web server without having to retrieve the data from the database server each time.

The code below is from the article mentioned above and is our starting point.

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Configuration;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
namespace WebApplication2.Pages 
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        public object Message { get; private set; } = "PageModel in C#<br/>";
        private IConfiguration _config;
        public IndexModel(ILogger<IndexModel> logger, IConfiguration config)
        {
            _logger = logger;
            _config = config;
        }
        public void OnGet()
        {
            Message += $"Server time is{ DateTime.Now }<br/>";
            DbConnection connection = new SqlConnection(_config["ConnectionStrings:SqlServerConnection"]);
            connection.Open();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = "select count(1) from T1";
            Message += $"{ cmd.ExecuteScalar() } records found<br/>";
        }
    }
}

We will make the following changes to the above code that will allow us to use a cache data set on the web server.

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Configuration;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.Extensions.Caching.Memory;
namespace WebApplication2.Pages
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        public object Message { get; private set; } = "PageModel in C#<br/>"; 
        private IConfiguration _config;
        private readonly IMemoryCache _memoryCache;
        public IndexModel(ILogger<IndexModel> logger, IConfiguration config, IMemoryCache memoryCache)
        {
            _logger = logger;
            _config = config;
            _memoryCache = memoryCache;
        }
        public void OnGet()
        {
            Message += $"Server time is { System.DateTime.Now }<br/>";
            System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
            timer.Start();
            if (!_memoryCache.TryGetValue("Data", out DataSet ds))
            {
                ds = new DataSet();
                using (DbConnection connection = new SqlConnection(_config["ConnectionStrings:SqlServerConnection"]))
                {
                    DbDataAdapter da = new SqlDataAdapter("select * from T1", (SqlConnection)connection);
                    da.Fill(ds);
                }
                _memoryCache.Set("Data", ds, new MemoryCacheEntryOptions());
            }
            timer.Stop();
            Message += $"{ ds.Tables[0].Rows.Count } records found in { timer.ElapsedMilliseconds } ms<br/>";
        }
    }
}

The code below is the same as above, but with line numbers so I can explain the changes that were made.

We included two libraries: System.Data (line 7), which allows using a DataSet to store the information, and Microsoft.Extensions.Caching.Memory (line 8), which allows using the interface IMemoryCache for persistence, as well as the MemoryCacheEntryOptions class (to be discussed later).

In line 16, we declare a private read-only property of type IMemoryCache to store the memory cache, which is passed to the constructor in line 17 and stored internally in line 21.

Lines 26, 27, and 38 measure the elapsed time through a System.Diagnostics.Stopwatch object.

Starting from line 28, we check if there is a cached object named "Data"; if there is, the value is referenced in the DataSet named "ds," and the timer stops. But if there isn't, lines 30 to 35 declare the DataSet, connect to the database using the connection string, create a SqlDataAdapter with a query to the database connection, and fill the dataset. Finally, line 36 stores the "ds" DataSet in the cache with the name "Data." The MemoryCacheEntryOptions class has some default values but can also be customized to store the data for different periods of time (see the Microsoft documentation: Cache in-memory in ASP.NET Core).

Finally, in line 39, we've modified the message to show the number of records found inside the dataset and the elapsed time while retrieving the information.

Testing the Data Caching

Below is the result when the page is loaded for the first time. Note: It took 13102 milliseconds to retrieve the information from the database.

And if you refresh the page using the upper left corner button, the elapsed time is reduced to 2 milliseconds.

As you can see:

With the use of caching, you will consume less CPU and memory from the database server
You won't put as much load on the network
The only thing remaining is when to expire the cached data so it's retrieved again (stayed tuned for the next article)

Next Steps

Here are additional articles to review:

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-04-21

Cache SQL Server Data in a .NET Web Application

Problem

Solution

Testing the Data Caching

Next Steps

About the author

Comments For This Article