Developing via Database Stored Procedures

Database stored procedures (SPs) allow you to save custom SQL queries and invoke them programmatically. This is great for 2 reasons:

1. The execution plan is pre-compiled, so the queries execute faster than ad-hoc SQL commands that cannot map out a plan until executed.
2. The query logic does not reside on the invoking program.

Most Database programmers give a cursory nod at 1, but never realize the specific benefits of 2; namely simplifying deployment.

At DMC, the most common Database solution we do involves connecting a central database with multiple remote applications that interface directly with hardware. (Like a PC connected to a PLC, or mobile devices with an integrated barcode scanner). In this architecture, the program must straddle two different locations, and the design breaks down to:

1. The data (to reside on the database)
2. The business logic
3. The user-interface (to reside on the HMI/user application)

Although the natural inclination is to host the business logic on the User application (language familiarity and better application development environment) don't be so quick.

Note the intentional allusion in this break-down to M-V-VM architecture & XAML/WPF, where there are significant benefits to separating out Steps 2 & 3. In XAML, the benefit is that it’s easier to coordinate separate people; developers programming the business logic and designers designing the user-interface. In SQL, clear separation of Steps 2 & 3 means that the steps don’t have to reside on the same computer, and most importantly, modifications to the business logic need not require a program upgrade and re-installation on 50 mobile devices.

It is true that the SQL elements are more work to debug, but for projects with many user-applications or frequent small changes, I’ve found the ROI to be well worth it. I estimate that I’ve saved hundreds of hours of unnecessary deployments over the years because of all the times that I’ve insisted that business logic be placed in Database stored-procedures and stored-functions rather than in the user-application.