For years, I’ve been developing web applications based on ASP.Net frameworks. And more recently with the ASP.Net Core framework.
On the basis of this experience, I’m going to present you, in a series of articles, some ways of optimizing the performance of .Net web applications.
How can we improve applications?
These frameworks have evolved considerably since version 2.0. It is now quite simple to start a project from within theVisual Studio IDE to respond to a customer request.
Web applications are often linked to one or more databases (directly, or indirectly via APIs). Recent versions of SQL Server Management Studio enable developers to “tune” these databases to a minimum in order to obtain queries with acceptable response times.
Building a web application with a minimum of graphical rendering is therefore within the reach of any development team. However, it’ s essential that the application’s performance remains optimal as each version is deployed. Indeed, the more an application evolves, the slower screen loading becomes. Optimizing application performance at a given stage of project maturity is often a challenge!
That’s why it’s important to keep a constant eye on your application’s performance, right from the design stage. Then, throughout its development.
In this series of articles, I’m going to talk about a few tricks I’ve used in the course of my experiments to make application navigation smoother.
The database
Optimizing an application starts at the very beginning of the project, with the database.
Designing a relational database according to best practices is crucial, because it’s where everything starts. Before embarking on the design of the database schema, it is essential to catalog the data you are going to use, in the knowledge that this catalog will constantly evolve.
The trick is to be able to identify the reference tables and the tables that will contain the data. Then imagine the calls that, via stored procedures or from an ORM, will query all these tables. All of this, with the aim of minimizing the number of joins between these tables.
SQL optimization
Analysis of query processing with SQL Server Management Studio
There are simple tools that developers use every day, such as SQL Server Management Studio. This powerful tool provides a number of useful features that many people are unaware of, such as the execution plan.
This feature analyzes the way the SQL engine processes a query to identify its weak points. This will help the developer to rewrite the query to improve response times, redefine keys, constraints or table indexes.
SQL Server Profiler, a powerful tracing tool
SQL Server Profiler is another tool I use very often to rewrite database calls. I use it because the applications I design are almost always based on the use of an ORM such as Entity Framework or NHibernate. And ORM means generated queries. So it’s SQL code that you don’t necessarily control, and which very often produces a large number of joins.
This interface allows you to trace all SQL queries passing through the database. This makes it possible to adjust projections, reduce the number of SELECT query joins and improve response times.
But these tools can only diagnose slowness problems, not solve them: you need a modicum of common sense when designing an application.
Let’s take the example of reference tables: these tables contain quasi-static information, necessary for setting application parameters or enriching data. For example, a table containing zip codes will not have the same cardinality as a table containing civilities. As the latter only contains 2 records (Mr, Mrs), there’s no need to create a table that will necessarily generate joins: it’s preferable to create an enumeration on the code side. Why would you do this? Because this enumeration will never evolve.
Server-side pagination
Finally, there’s one last topic I’d like to cover in this article, and that’s paginated lists. Component libraries, such as Telerik, allow you to do this in 3 clicks. It’s child’s play!
But it’s also a pitfall: pagination is natively client-side, not server-side. This is fine when the number of records to be paginated is small. On the other hand, it becomes a real problem when the number of records to be paginated becomes large.
It’s good practice to implement a server-side paging technique right from the start of the project, as this type of component is generally very widely used.
There are, of course, many other tricks that can be used to improve response times to database calls, such as caching, setting ORM parameters, etc… But it’s a start. 🙂
As for the other tips, I’ll cover them in the next articles in this series! The next one is dedicated to optimizing application performance on the code side.
