image by freephotocc on pixabay.com

Stored Procedures vs ORM Framework: When to use each one and Why

There is a long lasting debate whether Stored Procedures are more suitable for an application than ORM frameworks. If we were to expand this topic and discuss to find a definite answer, we could argue for days, without concluding.

First Things First,

Lets make a small review of the Stored Procedure definition

Stored Procedures are used just like SQL statements with the difference they can be stored for later use. Instead of calling the SQL statement directly, the Stored Procedure is called. Optionally we can use parameters as well.

example …

CREATE PROCEDURE <ProcedureName>
  @Param1 int = null,
  @Param2 varchar(50) = null
AS
 
  SQL Statement .....

GO; 

Advantages of Stored Procedures

1. Better Performance since Stored Procedures are compiled once and executed directly from the database

2. Improved Security since access to the database can be isolated from only through Stored Procedures

3. Cleaner application code, since SQL statements are located in the database layer

4. Altering a Stored Procedure does not require to restart Application Server

Disadvantages of Stored Procedures

1. Testing is hard since any error thrown by a Stored Procedure can only be tested during runtime
2. Debugging is almost impossible
3. Stored Procedures changes cannot be stored in Version Control Systems like svn and git
4. Stored Procedures cannot always be migrated to newer versions of the database server.
5. Stored Procedures cannot be migrated to a different kind of database Article here explains in detail
6. Database Administrator or Developer with the same role, is needed in order to write the Stored Procedures
7. Stored Procedure ResultSet/DataSet returned, a convertion is needed to convert the result to a list of objects. Therefore an Object Mapper is needed for each type of ResultSet/Dataset
8. Do not use Object Oriented approach thus more boilerplate database code to be written is required
9. Do not support out of the box Audit Logging
10. More boilerplate code

Now lets review the ORM ‘Object Relational Mapping’ Framework

ORM is a technique of accessing and converting data, from a relational database through an object-oriented language.

Advantages

1. Creates an abstract layer over traditional JDBC connectivity
2. Minimizes boilerplate code
3. Allows compatibility with multiple types of Databases. Migration to a different type of Database is made possible
4. Supports Rapid Application Development
5. Reduces Development Cost
6. Supports the DDD architecture’s Domain layer, by using Entities which are mapped to the Database tables. No need for custom Object Mappers
7. Improved orchestration of business objects (Entities) in the Domain -Business Layer
8. Transaction Management capabilities
9. Changes in the queries are recorded in version control systems like svn and git
10. Some ORM frameworks, like Hibernate support out of the box Audit Logging (Hibernate Envers)

Disadvantages

1. Application Server need re-deployment each time a change is made in the ORM’s queries.
2. Complex queries are slow and have low performance
3. Is slower than Stored Procedures and SQL queries

Some ORM Frameworks

• TopLink
• Spring DAO
• Hibernate
• Entity Framework
• NHibernate

Should we use Stored Procedures or ORM Frameworks ?

The answer is simple,

It depends on the requirements and complexity of the project to be developed

If the system to be developed, has simple business rules, and is a lightweight application, then go with the use of Stored Procedures.

On the other hand, lets take the usual scenario,

where the system to be developed, has complicated business logic and rules, with a large set of database tables.

In this case, we have no option but to use ORM framework.

Ask yourself these few simple questions,

• Can you meet the deadlines?
• How many persons the development team has? Are there enough developers to cover all the extra effort needed to write and support the Stored Procedures and custom Object Mappers?
• What is the Development Cost?
• What about future database migration to a different type of database. For example, from Oracle Db to an open source one, like PostgreSQL? Or to a Non-SQL DB like MongoDb ?

We can suggest a hybrid model,

ORM and Stored Procedures working together.

ORM queries can be used for fetching one record (Entity) for CRUD operations. That is, for a simple record to be edited or to be created, this is the best solution

Stored procedures can be used to retrieve or process large batch data that require fast performance. Summary pages that retrieve large amount of data, like for instance, fetching today sales records, can make use of Stored Procedures.

A nice paper I found in Google Scholar, that investigates the ORM framework utilization in .Net framework

A Comparative Study of the Features and Performance of ORM Tools in a .NET Environment

Concluding,

although the above it is not a definite answer or solution, each developer, team leader, architect can choose the solution that best fits the system requirements.