In our Fitradar backend solution we are heavily using a database in order to implement the necessary use cases. Almost every call to our backend API includes calls to the database. In order to make it easier to work with database from C# code we are using Entity Framework Core. The library allows us quickly and easy save C# objects in database and fetch persisted C# objects from the database. In many use cases the EF Core is used just for these basic operations – saving and fetching but the business logic is isolated in other classes. Nevertheless for some use cases we discovered that it is more straightforward to execute the business logic directly in the database in the form of stored procedures or user functions. And in this article I wanted to share some of the experience we had while were working on complex database operations and the solution we arrived at.
One of the main reasons why we started to consider moving some parts from the C# code to the database was the user experience. We noticed that some operations on the mobile application are taking considerable amounts of time and might degrade overall user experience and we started to look how to improve the performance of those slow operations. Some of the reasons we discovered that were causing the long response time were multiple calls to the database and unoptimized database operations. Striving for the clean code in our backend solution we tried to avoid stored procedures, user functions and triggers. Introducing SQL code that can’t be mapped to the C# code within a single use case would mean introducing another codebase. So far EF core allowed us to abstract from SQL code and we very happy to have all the logic in C# code. Besides one more language and codebase there were following issues that were holding us back from using SQL directly:
- Adding SQL code in DbContext in the form of strings would take away the Intellisense for SQL and that would make us deal not only with logic errors but with syntactic errors as well in the runtime.
- Refactoring with Visual Studio built-in tools would become more complex
- In case we needed to debug our method that uses stored procedure or fires SQL trigger we would have to debug C# and SQL code separately
- Working with two codebases readability of single use case is degrading – in order to understand the full logic we have to switch between C# and SQL
- There are different logging systems for ASP.NET Core and SQL and therefore it is harder to keep unified log format.
- More complex deploy process
But at the same time for complex persistence and database query operations we would have:
- Significant gain in performance
- Terse code, since SQL is specifically designed to work with relational databases and many operations can be expressed in much shorter code compared with C# and LINQ without losing the readability.
The main question we had to answer in order to understand whether we need to move some of the logic to stored procedures, triggers and user functions was: “Is the gain in performance worth it?” For some use cases we could see the improvements even with the test data and for the other use cases we predicted that changes we are planning to introduce will start to impact the performance once certain amount of data will be stored in a database. And so we decided to move some of our logic from C# to SQL code, but before that we really wanted to mitigate some of the above-mentioned problems we discovered during analysis. And to do that we decided to create separate SQL Server Database Project instead of keeping the SQL code in EF Core DbContext class and by doing that we solved the problem with structuring the code, Intellisense and SQL code maintenance.
P.S. Visit our website: https://www.fitradar.me/ and join the waiting list. We launch very soon!