Increasing C# data access performance by 90% using RepoDb 2nd-Level Cache
Tags: csharp, dotnet, beginners, tutorial
Published: 2020-01-09 06:24:06

---
title: Increasing C# data access performance by 90% using RepoDb 2nd-Level Cache
published: true
description: This is an article about RepoDb 2nd-Level caching benefits.
tags: csharp, dotnet, beginner, tutorial
---

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](https://github.com/mikependon/RepoDb/wiki/Customizing-a-Cache).

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](http://listofrandomnames.com/index.cfm?generated).

## 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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.
```csharp
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.
```csharp
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.

```csharp
[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.

```csharp
[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.

```csharp
[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](https://github.com/mikependon/Tutorials/tree/master/Blogs/DEV.to/Cache/SchoolAPI).

--------

You support is valuable to us, please do not forget to star our [GitHub](https://github.com/mikependon/RepoDb) page.


Thank you for reading this article.