Re: Are there still performance benefits of stored procedures?

  • From: Guillermo Alan Bort <cicciuxdba@xxxxxxxxx>
  • To: hemantkchitale@xxxxxxxxx
  • Date: Wed, 10 Nov 2010 11:23:31 -0300

I don't know how it works on 11g, but back on 10g PL/SQL packages would run
considerably faster than java packages (both in the DB). I have not seen any
benchmark of the same specific piece of code run as a stored procedure
against an application server.

From my point of view, you have two ways to go:

1) Server/Client: you definetley need to have business logic on the DB for
consistency, security, safety and because you don't know on what kind of
client the application will run.

2) multi-tiered: you can have the business logic on the DB or the
application server. If you have a big dedicated application server, then it
will probably have a good performance. The only point here is that you may
have a LOT of traffic between the app server and the DB server, so I'd
suggest making sure that you optimize the Queries.

In any case, you should always protect your data behind stored procedures
that validate input, at least if you plan to expose the DB to something
other than the development team.

Besides: isn't being easier to maintain the most important thing when it
comes to enterprise applications? specially if you have a development team
of over three people...


On Wed, Nov 10, 2010 at 5:23 AM, Hemant K Chitale

> Yes it avoids round trips. In terms of processing "in the database" it has
> always been better.  There have been other optmizations in terms of Bulk
> Processing beginning with 9i.
> Hemant K Chitale
> sent from my smartphone
> On Nov 10, 2010 3:22 PM, "RP Khare" <passionate_programmer@xxxxxxxxxxx>
> wrote:
>  Stored procedures provide business-logic safety, reduces application code
> and business-logic is easy to maintain. But are there any performance
> benefits?
> ...........
> Rohit.

Other related posts: