José Valerio Oracle Technology

PL/SQL can often add to program functionality and programmer efficiency, and there are certainly many cases in which a procedural language such as PL/SQL can do things that a nonprocedural language such as SQL cannot. For a number of reasons, a PL/SQL approach might offer great performance improvements over a traditional SQL approach.

Why to use PL/SQL?

Network Overhead reduction – High Performance – Tunable …

When programing in a traditional SQL-based application, SQL statements and data flow back and forth between the application and the database server. This traffic can cause delays even when both the application and database server are on the same machine. If the application and database are on different machines, the overhead is even higher. The use of PL/SQL stored programs can eliminate much of this overhead. A message is sent from the client to the server (the stored procedure execution request), and a minimal response is sent from the server to the client (perhaps only a return code), this is the optimal use of the database.

What about programming best practices ?

Today the new developments has all the code in the application server. An application server is a server program in a computer in a distributed network that provides the business logic for an application program. The application server is frequently viewed as part of a three-tier application, consisting of a graphical user interface (GUI) server, an application (business logic) server, and a database and transaction server. More descriptively, it can be viewed as dividing an application into:

- A first-tier, front-end, Web browser-based graphical user interface, usually at a personal computer or workstation.
- A middle-tier business logic application or set of applications, possibly on a local area network or intranet server.
- A third-tier, back-end, database and transaction server, sometimes on a mainframe or large server.

All these new and elegant developments has fired a new kind of performance problems with relational databases, basically the problem is the use of the database as a single repository and not as a powerful engine sub utilizing features and making thousand of repetitive queries loading the database with other thousands I/O requests and introducing new performance issues that make the application not scalable.

The code, should remains in the Application Server or not ?

Hibernate or not, that is the question

Today the developers use the persistence framework, therefore it’s a good idea that it’s a good choice for them but this selection could not be good for your database.  The goal of a transparent persistence framework like Hibernate is that the developer should design the object model independently from the database design. This makes sense since the two models are completely different! Then a mapping between the two is done through a configuration file. Of course we don’t work in an ideal world therefore a negative impact is to be expected.

During the application design the developers should evaluate what to do depending the application purpose. If a database procedure resolve a complex task in seconds why to add all the code in the middle tier?. In my opinion should be a mix, some of code should be developed in the middle tier and some of code should be write as a package or as store procedure, this is very convenient for the use of Oracle databases. Oracle provides powerful PL/SQL features that make fast and robust the query, now, PL/SQL uses an optimizing compiler that can rearrange code for better performance. From Oracle 10g, PL/SQL includes many automatic optimizations, so you might see performance improvements without doing any tuning. In the Oracle case there is one important thing to have in mind, you can speed up PL/SQL procedures by compiling them into native code residing in shared libraries. The procedures are translated into C code, then compiled with your usual C compiler and linked into the Oracle process. You can use this technique with both the supplied Oracle packages, and procedures you write yourself. Procedures compiled this way work in all server environments, such as the shared server configuration (formerly known as multi-threaded server) and Oracle Real Application Clusters.

So, if you are looking for performance, extreme performance, the answer is: “do it using PL-SQL” .  If you or your development team decides to use a persistence framework like hibernate you should know that probably you are resigning to many database benefits.

Probably many developers could not agree with me  and I understand, but viewing the issues from the database perspective  the optimal design frequently  could be a poor design in performance terms.

Development should work together with the database administration team, isolation makes both teams to make mistakes hard to fix later.