Distributed Cache with SQL Server in ASP.Net Core application
Performance is a key factor for any application, especially for web & mobile applications. Based on various research studies, it has been identified that high performing sites retain users than low performing sites. This means a poor performing site will have impact on the company’s business goals. So, Developers, be aware to make sure your applications are designed for faster response times.
One of the aspects that affect the performance of an application is Caching. With caching, you store the frequently accessed data and store in some temporary storage, As a developer, you should carefully consider the various caching mechanisms and implement them in your applications.
Caching in ASP.Net Application
ASP.Net supports both In-Memory Cache and Distributed Cache. Let me illustrate the basic differences.
In-Memory Cache
In-Memory Cache, your application uses Server memory to store the cached data. This works perfectly if you have only one server. In today’s world, where you use cloud services and automatically scale your applications, all your servers need to access the cached data from a single source, and In-Memory cache will not help you here,
Distributed Cache
In distributed cache, the cached data is stored and shared across all servers. ASP.Net supports several types of distributed cache implementations such as SQL Server, Redis, NCache etc.
In this article, I am going to demonstrate how to implement distributed cache with SQL Server. For the purpose of this demo, I created a .Net application using the template “ASP.Net Core Web App”. The screenshot of the app in solution explorer is given below.
Since we are going to use SQL Server distributed cache, install the following Nuget package.
Once the Nuget package is installed, the next step is to install the tooling support for sql-cache in .net CLI, so that we can generate the required tables. From Visual Studio, open the package manager console and enter the following command.
dotnet tool install -g dotnet-sql-cache
With this command the dotnet-sql-cache will be installed globally (-g switch) in your computer. If for any reason, if you want to uninstall you may use the following command.
dotnet tool uninstall -g dotnet-sql-cache
Now you can use the tool to create the database table where you will be storing the cache entries.
dotnet sql-cache create "Your Connection String" dbo CacheTable
To the sql-cache create command, you need to pass your connection string, the schema and the table name. A table with the given table name and schema will be created in the target database.
The following is the schema of the table that is created by the dotnet tool.
If you don’t want to use the tool to create your database, you can use the following SQL script to create the cache table.
CREATE TABLE [dbo].[CacheTable]( [Id] [nvarchar](449) NOT NULL, [Value] [varbinary](max) NOT NULL, [ExpiresAtTime] [datetimeoffset](7) NOT NULL, [SlidingExpirationInSeconds] [bigint] NULL, [AbsoluteExpiration] [datetimeoffset](7) NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Let us configure the app to use distributed cache. First we need to add the distributed cache to the Services using the Method AddDistributedSqlServerCache, so that it will be available for dependency injection.
builder.Services.AddDistributedSqlServerCache(options => { options.ConnectionString = builder.Configuration.GetConnectionString("CacheConnectionString"); options.SchemaName = "dbo"; options.TableName = "CacheTable"; });
CacheConnectionstring is the connection string I defined in the appsettings.json file. The schema name and the table name are the ones we passed to the dotnet tool.
Now Let us see how the caching working in razor pages. In the PageModel class, in the OnGet handler, the application will check for a cache entry, and if not found, it will get the data from the GetData method. For demonstration purpose, I added a 5 seconds sleep to the method, in real time, this will be a method that fetches data from database or some other data sources. Once the data is retrieved for the first time, it will be added to the cache entry with a sliding expiration. For the subsequent requests, the data will be fetched from the cache. See the code for my PageModel below.
using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.Extensions.Caching.Distributed; using System.Text; namespace DistributedCacheSample.Pages { public class IndexModel : PageModel { private readonly ILogger_logger; private readonly IDistributedCache Cache; public string? Data { get; set; } public DateTime StartTime { get; set; } public DateTime CompletedTime { get; set; } public IndexModel(ILogger logger, IDistributedCache cache) { _logger = logger; Cache = cache; } public async Task OnGetAsync() { StartTime = DateTime.Now; byte[] encodedData = await Cache.GetAsync("MyCacheKey"); if (encodedData != null) { Data = Encoding.UTF8.GetString(encodedData); } else { // Data is not there in cache. So build it. Data = await GetData();; encodedData = Encoding.UTF8.GetBytes(Data); var options = new DistributedCacheEntryOptions().SetSlidingExpiration(TimeSpan.FromSeconds(50)); await Cache.SetAsync("MyCacheKey", encodedData, options); } CompletedTime = DateTime.Now; } private Task GetData() { Thread.Sleep(5000); return Task.FromResult("BIG DATA"); } } }
The code is self explanatory. The GetData method returns the string “Big Data” after 5 seconds and OnGet handler will add it to the cache. Now let us see the razor markup in the cshtml page. The start time and completed time variables are updated with the current time at the start and end respectively.
<div class="text-center"> <h1 class="display-4">@Model.Data</h1> <p>Start Time: @Model.StartTime.ToString("dd-MM-yyyy HH:mm:ss:ffff")</p> <p>Completed Time: @Model.CompletedTime.ToString("dd-MM-yyyy HH:mm:ss:ffff")</p> </div>
Now let us run the application, for the first time. You may notice the 5 seconds difference between start time and completed time.
Now let us run the application again, and you will see there is no more waiting. The data is fetched from the cache.
Now the database contains the cache value with the Id of the key you defined.
Implementing Distributed Cache in ASP.Net web application is relatively easy and developers can make use of the caching implementation to ensure the applications run faster.
References:
https://docs.microsoft.com/en-us/aspnet/core/performance/caching/overview?view=aspnetcore-6.0
https://docs.microsoft.com/en-us/aspnet/core/performance/caching/distributed?view=aspnetcore-6.0