• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

to use stored procedure or not

 
azhar bharat
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I am one of those person who used to and still use the stored procedures. Not because I like it, in fact I have to depend on SQL developers to develop or optimize the stored procedure.
Now, as I read more and more articles on good design, I am beginning to rethink on my practice of using stored procedures(SP). Actually a bit confused too.
Many of these articles say "do not use stored procedures because it pushed the business logic to the database (or model tier in MVC context)".
It sounds logical but doesn't using SP have its advantages?
How do I decide which one is better? When should I use or not use SP?

I hope to get some expert opinions on this. Thanks in advance.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As you say,


I hope to get some expert opinions on this,


I am also waiting in the queue
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
IMHO, Stored Procedures are preferred because of the network time is reduced (round-trip time from JDBC to Database and the reverse) as there will be many number of calls if its not present.

But it depends on the actual requirement on its usage.

Still, i await some good inputs from the *experts*!
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I avoid SP because they are not portable from one database to another, and because they reflect what I view as an out of date architecture.

In Client-Server days, say PowerBuilder, the SP was the last bastion of protection for the data and the best unit of reuse. Clients called the SP and the SP made sure that things were done correctly. Nowadays the application server owns and protects the data and provides reusable services. Clients call application code and the code calls the database. We never give out database access to other systems.

One opinion only. Any big SP fans out there?
 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We use them a lot in our Oracle Applications ERP. The public APIs are written in PL/SQL. There's no risk of increasing vendor buy-in. It's Oracle software written for Oracle forms/reports running on Oracle Fusion middleware talking to an Oracle database.

We try not to use them for other applications, where platform independence (software and hardware) matters to us.

Regards, Jan
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I avoid SP because they are not portable from one database to another, and because they reflect what I view as an out of date architecture.


Thats a good point Stan James. Can you please explain the second part of the statement? What exactly do you mean to say, out-of-date architecture?
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Raghavan Muthu:
Can you please explain the second part of the statement? What exactly do you mean to say, out-of-date architecture?


It's what Stan talks talks about in the following paragraph: A two-tier architecture with just a client that talks directly to a database (i.e., no app server). In that scenario, there would be no protection from a rogue client if there was none in the database.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Ulf. But as per his conclusion on his second paragraph, nowadays the Application Server delegates the call from the client to database - means, by which the intrusion may not happen by that time as we assume previously the client must have been authenticated as well authorized.

What exactly it is related with out-of-date architecture even when the SP's are being used with the presence of App.Server - is what my question.

Am i confusing?
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The point is that the DB (or the SPs) don't need to perform the kinds of checks they needed to do with a two-tier system, because the app server takes care of that. So that kind of logic should not reside in the DB, in order to have a clean separation of concerns.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

The point is that the DB (or the SPs) don't need to perform the kinds of checks they needed to do with a two-tier system, because the app server takes care of that. So that kind of logic should not reside in the DB, in order to have a clean separation of concerns.


Thank you again Ulf. I did not think of as i was under assumption that the checks would be taken care before even the request/control is passed on to SP. Moreover if there are any violations on schema owner etc (which we have not checked), the db will throw an error. Thats what i have also worked with SPs.

Now its clarified. Thanks
 
azhar bharat
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks all.

I get the point now.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic