In this post let us discuss whether stored procedures should be used instead of Hibernate based queries i.e. HSQL

If you google it, you may find there are many myths and arguments associated with the stored procedure vs HSQL. Here are a few (I’m sure that there are more):

1. sp is pre-compiled by the server, this will increase the speed.

In some case it is true especially when the SQL is quite complicated. DBAs can help us to optimise SQls, we can take advantages of Database vendor’s pre-defined functions etc. Those are the advantages, however , the case of stored procedures for update routines is an another story.

An application can easily generate optimal update SQL for a single field involving a single parameter. In contrast, a stored procedure implementation will normally end up having to provide an “all fields” update mechanism with nullable parameters. This in turn leads to null checking logic within the stored procedure that is technically unnecessary.

2. Hibernate is slow because it makes unnecessary calls.

Hibernate can be made slow through the inappropriate use of eager fetching strategies where unnecessary associations are explored.  Consider the situation of a 3 way relationship between User, Role and Authority. The requirement is to fetch all Users with a first name of “Bob” (a set of 10 Users), along with all their Roles and Authorities. Hibernate can build this object graph in 3 queries: one for each entity followed by application side merging. An sp will require a routine to filter on the User table to get the ones with the correct first name. Then the Role fetch routine will have to be called to get the Roles for each of the Users, then the Authorities fetch routine will have to be called for each of the Roles. This is more complicated than the HSQL approach and makes a bunch of unnecessary calls as well.

3. Stored procedures make it obvious where the SQL is.

In the application, the actual SQL being run is buried in the depths of the mapping framework and usually only appears in the debug logs. The location in the application code where this query is constructed is not always clear – it should be in the DAOs but can be hidden in annotations on DTOs. Consequently, it takes some time and skill to identify where the query is being constructed, and how to alter the structure to make it more efficient. However, by following well known design patterns for entity mapping, these inefficiencies can be ironed out early and permanently.

So what should we choose?

There is no silver bullet in the world. In my opinion, if you have a really complicated SQL or your DBA can handle all the biz logic, then hide it in sp, anyway, based on my knowledge to call a sp is easier than write a bunch of HSQL. 

HSQL is a good candidate for update, or delete.