Designing real client-server applications

The ERP I developed and maintained for many years is a suite of client-server applications. Its various client applications are developed in .NET:

• A « thick client » in the form of a WinForms application.
• A Blazor Server Interactive website.
• A MAUI application for inventory management.
• A hybrid MAUI application designed to address more specific business requirements.
• …

All these applications rely on the same database server, MS SQL Server.

Understanding performance gains

Practical experience has shown me that every connection to the SQL server comes at a cost. Despite the optimisation provided by .NET through connection pooling, every connection takes time. An extremely small amount of time, admittedly. But when these connections are repeated, they have a tangible impact on the software’s response times as perceived by the user.

This is where a true « religion » emerges, with its own dogmas and practices that are followed to the letter.

• No SQL connection inside an iteration.
• Grouping multiple SQL queries into a single script, sent at once through a single connection.
• Avoiding (or minimising) the use of those convenient little utility functions that retrieve just one piece of data from the database.
• …

It is clear that this way of structuring the code is somewhat unusual and even goes against some current development practices.

Every SQL query must be optimised, « handcrafted », to squeeze out every last millisecond that can possibly be saved. In this context, where every SQL query is carefully optimised, an ORM is generally not the most appropriate tool.

In addition, there are the best practices that come with programming against SQL Server:

• No SELECT * FROM.
• Using SqlBulkCopy to load data quickly onto the SQL server.
• Adding the appropriate indexes (sometimes indexes on persisted computed columns).
• Using SQL CLR rather than T-SQL for more complex algorithms.
• …

Balancing the workload between client and server

SQL Server can handle part of the « business » algorithms. This relieves the client of that part of the workload. The SQL server already has the data at hand, so it is sometimes best placed to execute an entire lengthy process and simply return the expected result.

Well-designed (and non-recursive) triggers are our friends, because they are the best place to execute code that must always run whenever specific data changes. A perfect example is updating stock quantity counters according to incoming and outgoing goods movements.

Data integrity

A database is a powerful tool for ensuring data integrity. But the database schema must be properly designed from the outset so that its relationships accurately reflect the real-world environment represented by the data.

At this level, it makes sense to translate a whole series of data validation rules into constraints (in the SQL sense of the term):

• Non-empty keys
• Unique data
• VAT numbers
• Structured payment references
• …

Ultimately, this guarantees that regardless of the client software, and regardless of who developed it, the data will always comply with what the system as a whole expects.

Linux

SQL Server was exclusively available on Windows for many years. It now runs just as well on a Linux server. This makes it possible to reduce certain licensing costs while providing the end user with an entirely identical experience.

All my most recent installations have been deployed on Ubuntu.

In a client-server architecture, the database is not merely a storage space from which applications retrieve information. It is a genuine component of the software, and every interaction with it must be carefully considered and optimised. When used effectively, it contributes to performance, handles part of the business logic and guarantees data integrity, independently of the applications that use it.