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.
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
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.
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.
and in many other situations where java can do the job without needing the database.
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.
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
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