Prioritise performance from the start
At Esatto, we understand the critical importance of creating efficient web-based applications. Performance is one of the top indicators of a good application, and users are increasingly attentive to this aspect. Consider this: even if your application boasts a stunning design and top-notch UX/UI, poor performance can shatter the user experience, driving users away and making it challenging to win them back. Therefore, it’s essential to prioritise performance from the very beginning of the development process.
Various levels of performance
Performance can be considered at various levels. This article focuses on the infrastructure layer, where we model and implement data access. While modern architectural patterns like CQRS can significantly enhance application performance and scalability, we’ll leave that for another discussion.
Most of us use ORMs (Object-Relational Mappers) for data access, as they provide a convenient abstraction layer and eliminate the need to write SQL queries from scratch. But is this approach always the best choice? Let’s explore this by categorising applications based on business and technical complexity.
Trivial applications
Trivial applications are straightforward and often described solely by their user interface. They typically lack complex business logic and use a simple three-layer architecture. In such applications, simple CRUD operations dominate, involving uncomplicated sets of entities. Using ORMs here is straightforward and efficient, allowing developers to avoid writing SQL queries and rely on the ORM for data access.
Non-trivial applications
Non-trivial applications, on the other hand, cannot be described by the user interface alone. These applications involve complex business domains and sophisticated architectures. While ORMs can still be beneficial, their use requires careful consideration due to potential performance drawbacks. The complexity of business entities and their relationships can lead to complex and inefficient queries generated by the ORM.
Separating the write and read models often proves beneficial in non-trivial applications. We can optimise data access by introducing a lighter ORM, such as Dapper, on the reading side. In these cases, developers often write SQL queries to fetch data efficiently.
Writing SQL queries in the OOP world
Transitioning to writing SQL queries in an object-oriented programming (OOP) environment can enhance performance, especially in non-trivial applications. Here are some considerations:
Understand your data model
Gain a deep understanding of your data model and how different entities relate. This knowledge helps in crafting efficient queries.
Use parameterized queries
Always use parameterized queries to prevent SQL injection attacks and improve performance.
Optimise joins and indexes
Ensure that your database tables are correctly indexed and that joins are optimised for performance.
Monitor and refactor
Regularly monitor the performance of your queries and refactor them as needed to maintain optimal performance.
Bridging the gap between OOP and SQL development
Understanding the difference between procedural and declarative programming is crucial for any developer looking to bridge the gap between OOP (e.g., C#) and SQL (e.g., T-SQL). The easiest way to illustrate this is by using a simple, everyday task: making pancakes.
Procedural approach
In a procedural language like C#, you define the exact steps to achieve your goal. Here’s how making pancakes would look:
- Go to cupboard 1 and take flour.
- Go to cupboard 2 and take two glasses.
- Go to drawer 1 and take a spoon.
- Go to the fridge and take two eggs and a carton of milk.
- Pour milk into the first glass and water into the second glass.
- Add a glass and one spoonful of flour to the bowl.
- Add two eggs, then pour the milk and water into the bowl.
- Go to cupboard 3 and take a mixer.
- Mix everything using the mixer.
- Put a pan on the stove, turn the stove on, pour some mixture into the pan, and fry the pancakes.
In this approach, every action is explicitly defined step-by-step, detailing the sequence and the exact operations to perform. Of course, total performance can be improved by performing those operations parallel but we skip that in this discussion.
Declarative approach
In a declarative language like SQL, you describe what you need and the outcome without specifying the exact steps to get there:
- You need flour, milk, water, and eggs to make pancakes.
- You will also need a glass, a spoon, a pan, and a bowl.
- All of these can be found in cupboards, drawers, and the fridge (these are tables).
- Take one glass and one spoonful of flour, one glass of milk, one glass of water, and two eggs.
- Put everything into a bowl and mix using a mixer.
- Make pancakes using a pan, a stove, and the prepared mixture.
Here, you define the resources and the desired result. You don’t specify the order in which to gather the ingredients or the exact steps.
Key differences and implications
As seen in the examples, the procedural approach specifies each step in detail, while the declarative approach focuses on the end goal and what is needed to achieve it. This fundamental difference impacts how developers think and work within these paradigms.
Procedural programming (OOP)
Focuses on a sequence of commands and operations. It requires the developer to define how to achieve a result explicitly.
Declarative programming (SQL):
Focuses on the 'what' rather than the 'how'. It requires the developer to specify the desired outcome and the necessary data, leaving the execution details to the system.
For OOP developers transitioning to SQL, this shift in mindset can be challenging but is essential for optimizing performance and writing efficient queries.
Conclusion
By taking a thoughtful approach to data access and performance optimization, you can ensure that your web-based applications look great and perform exceptionally well, providing a seamless user experience. Bridging the gap between OOP and SQL development is crucial in achieving this goal, highlighting the importance of continuous learning and adaptation in the ever-evolving tech landscape. Understanding procedural and declarative programming paradigms will empower developers to make better architectural and performance-related decisions.
