Increasing C# data access performance by 90% using RepoDb 2nd-Level Cache

In this article, we will show you on how to implement and maximize the caching functionality when using the RepoDb ORM library.

Benefits

You will be learning the techniques on how to properly incorporate the caching layers into your application that could help you prevent frequent queries towards your database. It will help you improve the application performance by more than 90% in some scenarios.

In this tutorial, we brought the 8 seconds down to less than 500 milliseconds when querying the 20K records with child entity.

Scenarios Covered

Below is the very common scenario we will be covering on this tutorial.

• You have a look-up entity (a child entity). These records are not frequently changing (or not changing at all).
• You are retrieving the parent records with the child entity mentioned in #1.
• You are frequently iterating and re-doing #2.

There are other different scenarios that could lead us to a different solution and approach. In this tutorial, we would like to clear that the 90% performance improvement is feasible but it is a very targeted scenario. It is also important to take note that you can fetch the data via JOINS or whatever tuning you can do, but this tutorial is focusing on the 2nd-level caching.

In here, we will do the following.

• Student - the parent entity.
• Teacher - the look-up entity (or child entity).
• Create a REST API with database, table, C# project, interfaces, repositories and models.
• Display the query statistics (cache vs no-cache).

Before we begin

The cache in RepoDb is by default implemented as memory-based cache object. It can be overridden by your own customized cache object. The cache setting is by default set to 180 minutes (it can be overriden), and is only a single string argument. You can learn more about this one from here.

In addition, we expect that you have the following software install in your machine.

• Microsoft SQL Server (at least 2016).
• Microsoft SQL Server Management Studio 2019 (at least 2017)
• Microsoft Visual Studio 2019 (or with .NET Core 3.1).

Create a Database

In your SQL Server, execute the script below.

CREATE DATABASE [SchoolDB];
GO

Create a Table

In your SQL Server, execute the script below.

USE [SchoolDB];
GO

CREATE TABLE [dbo].[Student]
(
    [Id] INT IDENTITY(1,1)
    , [TeacherId] INT 
    , [Name] NVARCHAR(128) NOT NULL
    , CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

CREATE TABLE [dbo].[Teacher]
(
    [Id] INT IDENTITY(1,1)
    , [Name] NVARCHAR(128) NOT NULL
    , CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED ([Id] ASC )
)
ON [PRIMARY];
GO

Create a Child Entities

In your SQL Server, execute the script below.

USE [SchoolDB];
GO

INSERT INTO [dbo].[Teacher]
(
    [Name]
)
VALUES
('Lurlene Laury')
, ('Wynell Kort')
, ('Alexa Dempsey')
, ('Loan Goggins')
, ('Lien Lange')
, ('Veronika Hershey')
, ('Erasmo Milo')
, ('Columbus Hadden')
, ('Lena Cendejas')
, ('Shawana Bono')
, ('Morton Jourdan')
, ('Myesha Griffin')
, ('Cassi Pelayo')
, ('Shelly Chouinard')
, ('Gabrielle Cloninger')
, ('Brandee Rominger')
, ('Kimberly Blackmore')
, ('Efren Wey')
, ('Fabiola Douse')
, ('Heath Sessums');

The names mentioned above was randomly generated from listofrandomnames.com.

Create a C# Project

The C# project must be with the following information:

• Name: InventoryAPI
• Type: ASP.NET Core API
• Test: No
• Location: Any Location

No need to add a test framework. Leave this project an empty one.

Create the Class Models

In your C# project. Do the following.

• Add a folder named Models.
• Inside the Models folder, add the model classes named Student.cs and Teacher.cs.

Replace the content of the Student.cs with the code snippets below.

public class Student
{
    public int Id { get; set; }
    public int TeacherId { get; set; }
    public string Name { get; set; }
}

And replace the content of the Teacher.cs with the code snippets below.

public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Install the Library

To install the library, simply execute the command below in your Package Manager Console.

Install-Package RepoDb

Create the Repository Interfaces

In your C# project. Do the following.

• Add a folder named Interfaces.
• Inside the Interfaces folder, add the model classes named IStudentRepository.cs and ITeachRepository.cs.

Replace the content of the IStudentRepository.cs with the code snippets below.

public interface IStudentRepository
{
    IEnumerable<Student> GetAllStudents();
    int Save(Student student);
    int SaveAll(IList<Student> students);
}

And replace the content of the ITeachRepository.cs with the code snippets below.

public interface ITeacherRepository
{
    IEnumerable<Teacher> GetAllTeachers();
    Teacher GetTeacher(int teacherId);
    Teacher GetTeacherCache(int teacherId);
}

The interface contains the necessary methods for us to be able to retrieve the Student and Teacher entities from the database. Inside the ITeacherRepository, we introduced a GetTeacherCache method that will be used for caching. You will learn more as we go further.

Create the Repository Classes

In your C# project. Do the following.

• Add a folder named Repositories.
• Inside the Repositories folder, add the repository classes named SudentRepository.cs and TeacherRepository.cs.

Replace the content of your StudentRepository.cs with the one below.

public class StudentRepository : BaseRepository<Student, SqlConnection>,
    IStudentRepository
{
    public StudentRepository()
        : base(@"Server=.;Database=SchoolDB;Integrated Security=SSPI;")
    { }

    public IEnumerable<Student> GetAllStudents()
    {
        return QueryAll();
    }

    public int Save(Student student)
    {
        return Insert<int>(student);
    }

    public int SaveAll(IList<Student> students)
    {
        return this.BulkInsert(students);
    }
}

And replace the content of your TeacherRepository.cs with the code snippets below.

public class TeacherRepository : BaseRepository<Teacher, SqlConnection>,
    ITeacherRepository
{
    public TeacherRepository()
        : base(@"Server=.;Database=SchoolDB;Integrated Security=SSPI;")
    { }

    public IEnumerable<Teacher> GetAllTeachers()
    {
        return QueryAll();
    }

    public Teacher GetTeacher(int teacherId)
    {
        return Query(teacherId).FirstOrDefault();
    }

    public Teacher GetTeacherCache(int teacherId)
    {
        return Query(teacherId, cacheKey: $"Teacher{teacherId}").FirstOrDefault();
    }
}

Important Note: A method named GetTeacherCache has been introduced. This method is passing a cacheKey value of Teacher{teacherId} to cache the result into the Cache object.

Register the Repositories as Service Components

To register as a service component, follow the steps below.

• In your Solution Explorer, double-click the Startup.cs file.
• Navigate inside ConfigureServices method and paste the code below before the method end.

services.AddTransient<IStudentRepository, StudentRepository>();
services.AddTransient<ITeacherRepository, TeacherRepository>();

• Resolve the missing namespaces by placing the mouse inside the StudentRepository (and IStudentRepository) and press Ctrl+Space > Enter.
• Press Ctrl+S keys to save the changes.

The engine will register both the StudentRepository and TeacherRepository objects (as implemented by IStudentRepository and ITeacherRepository interface) into the services collection. Once the registration is complete, it signifies that these repository classes are now ready to be used for injection.

We can as well add it as Singleton via AddSingleton() method of IServiceCollection if we wish to have our Repository in a singleton mood.

Calling the Repository operations from the Controller

In this section, the ultimate goal is to call the StudentRepository operations inside a Controller method.

To attain this, we need to do the following steps below.

Create a Controller

• Inside the Controllers folder, add a new class named StudentController.cs.
• The new file named StudentController.cs will be created. Replace the class implementation with the script below.

public class StudentController : Controller
{
    private IStudentRepository m_studentRepository;
    private ITeacherRepository m_teacherRepository;

    public StudentController(IStudentRepository studentRepository,
        ITeacherRepository teacherRepository)
    {
        m_studentRepository = studentRepository;
        m_teacherRepository = teacherRepository;
    }

    [HttpGet()]
    public ActionResult<IEnumerable<Student>> Get()
    {
        return m_studentRepository.GetAllStudents().AsList();
    }
}

• Press Ctrl+S keys to save the changes.

There you see a method named Get. That method is the default method being routed that will display all the Student records from the database.

Create a Student Generator Method

This method will be used as a generator of the Student entity.

In your StudentController.cs, add the code snippets below just right after the Get method.

[HttpGet("generate/{count}")]
public ActionResult<dynamic> Generate(int count = 10000)
{
    var teachers = m_teacherRepository
        .GetAllTeachers()
        .AsList();
    var random = new Random();
    var students = new List<Student>();
    var dateTime = DateTime.UtcNow;

    for (var i = 0; i < count; i++)
    {
        var teacher = teachers.ElementAt(random.Next(0, teachers.Count - 1));
        var student = new Student
        {
            Name = $"Student-{Guid.NewGuid().ToString()}",
            TeacherId = teacher.Id
        };
        students.Add(student);
    }

    var insertedCount = m_studentRepository.SaveAll(students);
    var elapsed = (DateTime.UtcNow - dateTime).TotalMilliseconds;

    return new
    {
        Inserted = insertedCount,
        ElapsedInMilliseconds = elapsed
    };
}

What does the Generate method do?

It does the following:

• Retrieve all Teachers from the database.
• Create a Student entity based on the number passed in the count argument. The default is 10000.
• Each Student, a random Name and TeacherId is assigned.
• Bulk-inserting the Student entities into the database.

Create a QueryStat Method

This method is being used to display the query statistics without caching.

In your StudentController.cs, add the code snippets below just right after the Generate method.

[HttpGet("querystat")]
public ActionResult<dynamic> GetQueryStat()
{
    var students = m_studentRepository.GetAllStudents();
    var iteration = 0;
    var dateTime = DateTime.UtcNow;
    foreach (var student in students)
    {
        var teacher = m_teacherRepository.GetTeacher(student.TeacherId);
        if (teacher != null)
        {
            iteration++;
        }
    }
    var elapsed = (DateTime.UtcNow - dateTime).TotalMilliseconds;
    return new
    {
        Iteration = iteration,
        ElapsedInMilliseconds = elapsed
    };
}

This method is calling the GetTeacher method from the controller.

What does the QueryStat method do?

It does the following:

• Retrieve all Students from the database.
• Iterate each Student and retrieve the related Teacher from the database.
• Records the iteration account and total number of milliseconds elapsed.

Create a QueryStatCache Method

This method is being used to display the query statistics with caching.

In your StudentController.cs, add the code snippets below just right after the GetQueryStat method.

[HttpGet("querystatcache")]
public ActionResult<dynamic> GetQueryStatCache()
{
    var students = m_studentRepository.GetAllStudents();
    var iteration = 0;
    var dateTime = DateTime.UtcNow;
    foreach (var student in students)
    {
        var teacher = m_teacherRepository.GetTeacherCache(student.TeacherId);
        if (teacher != null)
        {
            iteration++;
        }
    }
    var elapsed = (DateTime.UtcNow - dateTime).TotalMilliseconds;
    return new
    {
        Iteration = iteration,
        ElapsedInMilliseconds = elapsed
    };
}

By this time, this method is calling the GetTeacherCache method from the controller.

What does the QueryStat method do?

It does the following:

• Retrieve all Students from the database.
• Iterate each Student and retrieve the related Teacher from the cache. If not present, it queries from the database.
• Records the iteration account and total number of milliseconds elapsed.

Testing the Controller

At this point, our solution is now ready for testing. Build the solution by simply pressing the Alt + B + R keys.

Once the build is complete, press the F5 key to start.

In the browser, type the URL below.

Generate 20000 Student Records

In your browser, please enter the link below to generate 20K records of Student entity.

https://localhost:44397/student/generate/20000

Expected result:

{"inserted":20000,"elapsedInMilliseconds":111.4921}

Query Student Statistics without Cache

In your browser, please enter the link below to query all the 20K records of Student entity directly from the database.

https://localhost:44397/student/querystat

Expected result:

{"iteration":40000,"elapsedInMilliseconds":18862.6261}

Query Student Statistics withCache

In your browser, please enter the link below to query all the 20K records of Student entity directly from the cache.

https://localhost:44397/student/querystatcache

Expected result:

{"iteration":40000,"elapsedInMilliseconds":209.4439}

How to find a port?

• Right-click on the project InventoryAPI from the Solution Explorer and click the Properties.
• Click the Debug tab.
• Under Web Server Settings, you will see the App URL field that contains the port.

Links

You can see and download the actual project here.

You support is valuable to us, please do not forget to star our GitHub page.

Thank you for reading this article.