• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Do we really need StoredProcedures ?

 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Last friday evening my CTO asked me more or less the same question, and at a first time I thought that it was a silly question - all of us know that Stored Procedures are very fast and reliable. As usual, a "silly question" revealed not to be silly at all, just because I tried to search a bit on the web about this argument and I found several articles arguing against Stored procedure usage. Most precisely, arguments are most of time against coding business logic in SP, and usually SP are considerated ok for data validation, or when you have scenarios where the same operation on data may be performed by different clients.
So, after blaming myself for have been so superficial, I decided to post this question on JavaRanch.
What are you experiences about, guys ?
 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good question Claude

Well SP can be good and bad depending on how you look at things.

Good because they are fast, reliable, SQL based

Bad because they are not portable (DB specific to some extent), vendor lock-in


I have seen companies use SP to implement business logic when their entire technology stack is the same vendor. This at first seems a good idea right? But what happens if you change DB from Oracle to SQL Server or similar. Using specific DB may sneak in DB-specific functions/features in SP. For example, Oracle PL/SQL has the LISTAGG function, what's that in ANSI SQL?

Figuring out such things when changing DB vendor takes time and effort.


On a different perspective, if "all" business logic is done in coding then application maintenance and deployment may be so frequent. Yet having such commonly changed business logic in SP may be a good idea ... who knows?'

 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My personal opinion is that business logic should be coded in an high level language, while SPs should be used only for heavy data crunching and when high efficiency is needed. I think that SPs have two major drawbacks: aren't portable at all (so that one shoul wonder why use an architecture neutral language like Java, only to be tied up to a specific DBMS vendor), and honestly are written in a language far to be pleasant, difficult to debug, and difficult to be organized in atomic-task blocks. This is why I would use SP only to linearize data logic -for example to avoid too difficult to read joins I sometimes prefer to write an SP with a main while / end cycle with different subqueries within. But there are people that would really code near everything in T-SQL for example, that's why I asked this question.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
On the current project we rely heavily on stored procedures (functions and triggers too). But we only use the stored procedures for heavy data processing. The data in our database is automatically imported from csv files each night. The csv files are processed and validated using Java, then through JDBC the data is inserted into the appropriate tables and then the stored procedures process all this data: adding primary keys, (spatial) indexes, creating other (helper) tables for the proper operation of the application and so on.

But we only try to use the SP only when it's the best fit for the job!
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would like to share my experience on this. Personally I try to avoid SP and like to write the logic in Java. Sometimes it results in an negligible performance hit.
There was a requirement for account number valildation in a payment system application in a bank and with the structure of the DB of the Core banking System it was not possible to get the data in a single query and do the validation in Java.
7-8 queries had to be run depending on the result of the previous query.
There I took the help of SP.
My SP takes 100 account numbers does the validation and returns the status of each and we noticed a significant performance improvment.

Of course we did not have to worry about DB vendor change because banks do not go for DB vendor change everyday.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends on how likely it is that you'll be switching database platforms, how much data you need to process in one go, and what your team's skills are.

I've worked on a lot of Oracle systems where we had to do a lot of bulk data processing i.e. thousands of records at a time, often with fairly complex business logic. You really don't want to be shipping GB of data across the network and processing it all in Java, so it makes a lot of sense to put this processing in the database. Also, stored procedures (in Oracle PL/SQL at least) can be optimised by the database engine to improve performance, and the code can be checked at compile-time e.g. to make sure the column names etc are correct, instead of at runtime (as with JDBC SQL strings). And your productivity is probably going to be higher if you are coding database-oriented logic with a database-oriented language.

In my experience the argument about switching databases can be a distraction. This does happen, but I've only seen it a few times (moving from a low-end DB to Oracle) in over 20 years. There is a trade-off here, as there's no point living with a painfully slow/unreliable database application for years on end, just in case you decide to switch databases at some point in the future. If you have to move to a new DB one day, it's usually easier to unpick stored procedures and convert them to Java (or the stored proc language in your new DB) than the other way around, because languages like PL/SQL have much less boilerplate and ceremony so the DB logic is usually fairly easy to extract. Of course, if you expect to change DBs often, or you have to support multiple DB platforms, then you would keep your logic in Java if possible to avoid this extra work.

One disadvantage of stored procedures is around code versioning and deployment: if you're sharing a schema, then any PL/SQL code you deploy may impact the work of other people working in the same DB schema, so this needs to be managed carefully.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another issue with stored procs is related to passing parameters. In my experience it is quite easy to end with a lot of parameters to work with, especially if you want to use stored procs for interactively selecting data. In this case, I used to use a table to keep all in and out params, with a record ID as unique parameter to be actually passed. This way, I avoid to change often stored procs signatures.
I totally agree that changing db vendor is quite rare; nevertheless, lack of stored portability across vendors usually locks you to a platform if stored are widely adopted.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic