When to PL/SQL over Java Code?

What are the developers doing wrong?

Many developers take the result set from the database. Use data structures like Maps, List etc. to perform filtering and other processing tasks which can be easily done at the database level. Doing maximum amount of data processing in SQL is efficient and faster than writing logic inside the java code.

Loading data from the database into application memory and writing it back to the database after computation is a waste of application memory, bandwidth and time.

When NOT to use PL/SQL?

Sending data to the database server for validation should be avoided. This will save a connection to the database, in case the validation fails. The web server handles the request, performs validation and returns the response back to the client.

If the validation is dependent on a database record, the developer needs to perform the validation at the database level. The validation can be split into application level and database level. Database level validations should be performed after application level validations. Because any validation failure at the application level will help skip database dependent validations.

You should use the standard java validation api for validating the POJO. JSR 380: Javax Validation API

Benefits

Exposing less information and columns from the database and sending required data for view purpose to application server will help reduce security risks. PL/SQL does not require type conversions due to close integration with SQL.

Computation in PL/SQL

The example below is purely logical and can be written as application code. Saving an extra trip to the database server. To execute the below function, a connection is established. The result returned from the database server is sent back to the client by the application server.

DROP FUNCTION IF EXISTS ASUM;
CREATE FUNCTION ASUM(n int) RETURNS int

BEGIN
    DECLARE s INT DEFAULT 0;
    WHILE n > 0 DO
        SET s= s + n;
        SET n = n - 1;
    END WHILE;
    RETURN s;
END;

DROP FUNCTION IF EXISTS HASH_RANDOM;
CREATE FUNCTION HASH_RANDOM() RETURNS VARCHAR(500)
BEGIN
    DECLARE HASH VARCHAR(100);
    DECLARE T_ID INT;

    SELECT str,id into HASH, T_ID from random where id = (select max(id) from random);
    SET HASH = MD5(CONCAT(HASH,':', T_ID));

    RETURN HASH;
END;

SELECT HASH_RANDOM((select min(id) from random))
view raw

But the second function performs computation on table records. This is a good practice. Since data is not sent back to the application server for processing, the computation is written in PL/SQL coupled with the query. And it performs better compared to code written in the application server.

When to use Java code?

1. When there is an algorithm already present in the application layer or something that cannot be written in PL/SQL.
2. Sending the inputs required from the database to application logic.
3. For sending, results returned from database to client for view purpose (e.g XML, JSON)

and in many other situations where java can do the job without needing the database.

Advantages of Stored procedure

A stored procedure is better, as compared to inline SQL queries because they give the power to execute multiple SQL queries in a single call. This helps in database efficiency as it requires only one connection rather than having multiple connections for multiple SQL queries.

• Fast and efficient
• Less coding
• Easy to use
• Secure
• All queries in a single stored procedure with a single call

DROP PROCEDURE  IF EXISTS createAccount;
CREATE PROCEDURE createAccount(OUT accid varchar(100), OUT acckey char(120))
BEGIN

SET @key = concat("website",randomString(33));

SET @pass = concat("",randomString(120));

insert into user(user_name, password)
values (@key, @pass);

insert into user_role (role_name, user_id) 
values('seller', (select user_id from user where user_name = @key));

SELECT @key, @pass;

END

Performance comparison pl sql vs java

At first glance, the main performance degradation for a SQL request is sending and receiving the request across the network. We also know that most boosts in performance rely on the architect eliminating or reducing the number of round trips to the database.

A logical assumption is that batching requests and then sending them as a group would greatly enhance performance. But how much does it enhance performance? And what's the threshold that seems to be the decision point? Is a single statement faster in a package? Two statements? Or is it only when you have 25-50 statements that a package becomes the ideal choice? These are the questions that I hope to answer in the benchmark tests in the following section.

The full performance comparision is available here