Redis vs SQL Server performance

Question

Application performance is one of the main reason of using cache over relational database. Because it stores data in memory in the form of key value pair, we can store frequently accessed data in cache which are not changes very frequently. Reading from cache is much faster than database. Redis is one of the best solution in distributed cache market.

I was doing a performance test between Azure Redis cache and Azure SQL Server. I have created a simple ASP.NET Core application and inside that I have read data from SQL Server database as well as Redis multiple times and compare the read time duration between them. For database reading I have used Entity Framework Core and for Redis reading I have used 'Microsoft.Extensions.Caching.StackExchangeRedis'.

Model

using System;

namespace WebApplication2.Models
{
    [Serializable]
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Subject { get; set; }

        public Student()
        {
            Name = string.Empty;
            Subject = string.Empty;
        }
    }
}

Entity Framework Core data context.

using Microsoft.EntityFrameworkCore;
using WebApplication2.Models;

namespace WebApplication2.Data
{
    public class StudentContext : DbContext
    {
        public StudentContext(DbContextOptions<StudentContext> options)
            : base(options)
        {
        }

        public DbSet<Student>? Students { get; set; }
    }
}

Startup class

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
        
    string studentDbConnectionString = Configuration.GetConnectionString("StudentDbConnectionString");
    services.AddDbContext<StudentContext>(option => option.UseSqlServer(studentDbConnectionString));

    string redisConnectionString = Configuration.GetConnectionString("RedisConnectionString");
    services.AddStackExchangeRedisCache(options =>
    {
        options.Configuration = redisConnectionString;
    });
}

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
     }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "StudentDbConnectionString": "[Azure SQL Server connection string]",
    "RedisConnectionString": "[Azure Redis cache connection string]"
  }
}

Home controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Distributed;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using WebApplication2.Data;
using WebApplication2.Models;

namespace WebApplication2.Controllers
{
    public class HomeController : Controller
    {
        private readonly StudentContext _studentContext;
        private readonly IDistributedCache _cache;

        public HomeController(StudentContext studentContext, IDistributedCache cache)
        {
            _studentContext = studentContext;
            _cache = cache;
        }

        public IActionResult Index()
        {
            List<Student>? students = null;
            var counter = 10000;

            var sw = Stopwatch.StartNew();
            for (var i = 0; i < counter; i++)
            {
                students = _studentContext.Students.OrderBy(student => student.Id).ToList();
            }
            sw.Stop();
            ViewData["DatabaseDuraion"] = $"Database: {sw.ElapsedMilliseconds}";

            if (students != null && students.Count > 0)
            {
                List<Student> studentsFromCache;
                var key = "Students";
                _cache.Set(key, ObjectToByteArray(students));

                sw.Restart();
                for (var i = 0; i < counter; i++)
                {
                    studentsFromCache = (List<Student>)ByteArrayToObject(_cache.Get(key));
                }
                sw.Stop();
                ViewData["RedisDuraion"] = $"Redis: {sw.ElapsedMilliseconds}";
            }

            return View();
        }

        private byte[] ObjectToByteArray(object obj)
        {
            var bf = new BinaryFormatter();
            using var ms = new MemoryStream();
            bf.Serialize(ms, obj);
            return ms.ToArray();
        }

        private object ByteArrayToObject(byte[] arrBytes)
        {
            using var memStream = new MemoryStream();
            var binForm = new BinaryFormatter();
            memStream.Write(arrBytes, 0, arrBytes.Length);
            memStream.Seek(0, SeekOrigin.Begin);
            object obj = binForm.Deserialize(memStream);
            return obj;
        }
    }
}

Home\Index.cshtml view

@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">        
    <p>@ViewData["DatabaseDuraion"]</p>
    <p>@ViewData["RedisDuraion"]</p>
</div>

I have found SQL Server is faster than Redis.

The ASP.NET Core application is hosted in Azure App Service with the same location with Azure SQL Server and Azure Redis.

Please let me know why Redis is slower than SQL Server?

Answer 1

I have used github.com/dotnet/BenchmarkDotNet to benchmark the Azure SQL Server database and Azure cache for Redis for 10000 reads. SQL Server database mean: 16.48 sec and Redis mean: 29.53 sec.

I have used JMeter and connects 100 users each reading SQL Server database/Redis 1000 times. There is not much difference between total time it took to finish reading SQL Server database vs Redis (both are near about 3 mins and 30 sec), but I saw load on Azure SQL Server database DTU. The DTU goes near 100% during the test.

As a conclusion, I think speed is not the only reason to use Redis cache over SQL Server database but another reason is Redis cache reduces good amount of load from the database.