Should I cache SQL database data and query it locally? Or have the app query the database directly?

Question

I am making an application in C#, which is the first professional project I will be adding to my portfolio. The application connects to a remote SQL Server and queries specific tables to get data such as Student information, details on courses, and data of student enrollment in each course.

My question is, even though the query will return a single record most of the time, is there a need to cache the tables? There are only 3 tables (Students, Courses, Enrollment), and Courses is the only table to doesn't change all that often, at least in comparison to the other two.

In a nutshell, the app is a CLI that lets the user view school courses, registered students and the student's respective enrollment in those courses. The app has the functionality of entering student info such as their name, their mailing address and contact information, which is then persisted to the SQL Server. The same goes for the Course details like the CourseID, Name and description. As well as the enrollment, which is where the server joins the StudentID and CourseID in the record to show that the specified student is enrolled in that course.

I am currently running a local instance of MSSQL, but plan to create a lightweight Virtual Machine to hold the SQL server to replicate a remote access scenario.

I figure that if the application is ever deployed to a large scale environment, the tables will grow to a large size and a simple query may take some time to execute remotely.

Should I be implementing a cache system if I envision the tables growing to a large size? Or should I just do it out of good practice?

So far, the query executes very quickly. However, that could be due to the fact the MSSQL installation is local or the fact that the tables currently only have 2-3 records of sample data. I do in the future plan to create more sample data to see if the execution time is managable.

Answer 1

Caching is a good option for the kind of data which is to be accessed frequently but does not change so frequently. In your case it would apply to 'Courses' for which you said that data won't change frequently.

However, for data that is going to grow in size in future and will have frequent inserts/updates to it, it is better to think about optimizing the way they are stored and retrieved from the data stores. In your case, the tables 'Student'and 'Enrollment' are such tables where it is expected to have lots of inserts/updates over time.

So it is better to write optimized procedures to perform CRUD operations on these tables and keep indexes of the right sort on the tables as well. It will not only provide better manageability of data but also give the performance that you are looking for, when compared to caching the results.

Answer 2

Caching is an optimisation tool. Try to avoid premature optimisation, especially in cases when you do not know (and can't even guess) what you are optimising for (CPU, Network, HD speed etc).

Keep in mind that databases are extremely efficient at searching and retrieving data. Provided adequate hardware is available, a database engine will always outperform C# cache structures.

Where caching is useful is in scenarios where network latency (between DB and the app) is an issue or chatty application designs (multiple simple DB calls into small tables in one interaction/page load).

Answer 3

Well for a C# app (Desktop/mobile), cache system is a good practice.But you can make a project for a school without cache system because it doesn't weaken your app performance a lot. Its up to you whether you want to use it or not.