#loading

V2 Stored procedures usage

V2 Stored procedures usage

Oct 15, 2019

Stored procedure (SP) – is a set of instructions stored in the database and available for applications. It can consolidate the logic of data validation, access management, transactional processing, complex or expensive queries and much more. Most common RDBMS support the user-defined SP using SQL, extended SQL (T-SQL, PL/SQL) or even general-purpose languages (SQL CLR, Java, JavaScript).

Most of the solutions implemented by our team require interaction with the data stored in the SQL Server. For this purpose, we use ORM tools like Entity Framework, EF Core or NHibernate in legacy projects. Each of them allows us to easily query data and without deep diving into the database structure. Mostly, ORM functionality is enough for 90% of all tasks. In some rare cases, when ORM cannot provide a better flexibility or specific functionality, we need to do something else. For such cases SP can be used. As usual, SP has its advantages and disadvantages.

SP pros:

1. Ability to use all optimization hints and RDBMS features

2. Possibility of using it as an additional abstraction layer and encapsulation of complex   processes

3. Ability to change the logic without app recompiling

SP cons:

1. Strong SQL and RDBMS knowledge required to take a real advantage

2. Harder to debug, store in version control systems and deploy

3. Trickier to test, to support and to refactor in future

Optimization hints and features

SP code allows adding query hints to your logic to boost the performance and do exactly what is needed. Hints help to run query using specific index or to lock selected records, for example. Usually, the query optimizer selects the best execution plan for ORM generated queries and there’s no need to manually do any optimizations.

Another useful feature is the ability to parse the saved JSON or XML data and use its fields for querying without loading all data to the application through the network.

Transactions encapsulation

In case of interactive logic, the flow app makes a query, reads the result, and, perhaps, makes more queries based on the previous results, etc. Queries data is transferred back and forth between the app and the database, which are usually located on different machines. Consequently, a lot of time is spent on the network communications. The usage of SP’s to prepare data gives a higher performance compared to retrieving data out via SQL and then manipulating it outside of the database.

For these reasons, it’s more effective to submit all transaction data into SP and to process it inside the database, thus avoiding the overhead of disk I/O or network.

Dynamic updates

The application business logic can be easily changed by modifying SP directly in the database. It allows it to change only a certain SP, improve the performance or fix it without app deployment.

The ability to edit SP from the database doesn’t mean you should definitely do this. Such an approach is very error prone and all changes should be made, tested and added very carefully into the version control system. There is a big chance to harm a large part of the application.

Strong knowledge and performance

Developers should be experienced in SQL and, specifically, in  RDBMS to produce really effective queries and not to harm the server performance. The database server is often much more performance sensitive then the app server. Its resources shared between many application servers and a badly written SP (memory, CPU, disk usage) can cause much more trouble than a badly written application method.

Testing, debugging

SP testing ensures that the execution of the stored procedures generates the expected result. Usually test integration is much trickier then app testing. A modern IDE, such as Visual Studio, now provides the ability to integrate unit testing for SP, functions or views. However, its features can hardly resemble those of modern testing frameworks.

The debugging process of SP’s is important, but not easy. Luckily SQL Server Management Studio has builtin debugging features, which allow you to see what is actually going on, and to step through SP logic in a similar fashion to working in Visual Studio

Use-case

In our solutions we use the combination of ORM, raw queries and SPs to communicate with the database. Apps use SP’s to build data sets, cover complex queries, search helpers, and to update denormalized data, scheduled jobs, ETL process.

ORM provides a user-friendly interface and covers more than 95% of our queries. The rest 5% of complex or not trivial queries we wrap into SP’s or rarely write it as raw SQL.

SP is closely integrated with the business logic of the application and it has become an important part of our solutions. It helps to bypass ORM bugs, improve query performance, allows dynamic modifications, and permits DBA to fix or tweak the performance with no help of the development team.

The usage of SP also adds additional requirements for tracking of its changes. We use migrations for our database and any SP changes should be added as the migration script; otherwise you may get a non-consistent database state with unexpected issues.

As a result of the ORM+SP collaboration, we can implement requests fast, add any necessary optimizations or even modify the business logic without any code changes.

Summary

Despite the disadvantages of using SP, it proved to be an excellent solution for projects with various level of complexity.

Its usage allows to get rid of unnecessary data selection, unnecessary complication of the code and to bypass ORM limitations.

However, there is still the need to be extremely careful and to pay attention to the testing and versioning. There’s no need to hurry up with optimizations and move everything to SP, since modern ORM can cover most of your needs. Every ORM tool we use supports the stored procedures. Instead of moving everything to SP’s, just use them when needed.

BE THE FIRST WHO GETS UPDATES
Using Corsac Blog and website you agree to our Privacy Policy and Terms and Conditions.