This is another article series about the set based algorithm which I used when I ported Oracle functions to PostgreSQL functions.
Whether you are using Oracle or SQL Server or PostgreSQL or Cubrid, the application design is actually the main reason why you get contention, poor performance, and slow latency. The SQL statement that you run, when you issue it, how you issue it, and what data the query tries to extract, has a profound effect on the database system.
One of the largest reasons why you get poor performance is really to do with applicatioin design itself. There is no magic bullet out there where the database just does everything for you and you can completely relax. If we care about performance, we should be aware of how our SQL statements affect the database system because the database system is just sitting there, receiving SQL calls
we issue. In a nut shell, we have to think like we are databases.
Otherwise we are going to get longer transaction latency and longer lock
waits.
In this note I will offer an example of how you can improve database performance by using the set based mechanics.
The following is an Oracle procuere we are investigating.
CREATE OR REPLACE PROCEDURE P_UPDATE_COMMENT(p_ord_dt
VARCHAR2 )
IS
v_comment ONLINE_ORDER%TYPE;
CURSOR C1 IS SELECT *
FROM ONLINE_ORDER
WHERE ORD_DT = p_ord_dt
FOR
UPDATE; --prohibit
other transactions from modifying the rows.
BEGIN
FOR rec IN C1 LOOP
IF rec.ORD_STATUS_CD = '1' THEN v_comment = 'Received';
END IF;
IF rec.ORD_STATUS_CD = '2' THEN v_comment = 'Confirmed';
END IF;
IF rec.ORD_STATUS_CD = '3' THEN v_comment = 'Cancelled';
END IF;
IF rec.ORD_STATUS_CD = '4' THEN v_comment = 'Ordered';
END IF;
UPDATE ONLINE_ORDER
SET COMMENT = v_comment
WHERE CURRENT OF C1; --access the table with the rowid of the row.
END LOOP;
COMMIT;
END;
The Oracle procedure above is using what Tom Kyte called 'slow by slow algortithm'.
It is declaring a cursor extracting a few rows and it is locking the
rows to prevent other transactions from changing the rows. It then
fetches one row and checks the value of the column ORD_STATUS_CD and
stores a value in the variable v_comment. And then it calls an UPDATE
statement with the rowid of the row replacing the COMMENT with
v_comment. And it iterates the process until it consumes all the rows in
the C1 cursor, which means that if the cursor C1 has 100 rows the
database has to run the UPDATE statement 100 times.
Below
is a procedure I ported to PostgreSQL. I used the set based techniques.
As you can see the procedure is calling the UPDATE statement just one
time. There is no explicit locking statement. There is no declaration of
a variable.
CREATE
OR REPLACE PROCEDURE P_UPDATE_COMMENT (
p_ord_dt VARCHAR )
LANGUAGE
SQL
AS
$$
UPDATE ONLINE_ORDER
SET COMMENT
= CASE WHEN ORD_STATUS_CD = '1' THEN 'Received'
WHEN
ORD_STATUS_CD = '2' THEN 'Confirmed'
WHEN
ORD_STATUS_CD = '3' THEN 'Cancelled'
WHEN
ORD_STATUS_CD = '4' THEN 'Ordered'
END
WHERE ORD_DT
= p_ord_dt;
--COMMIT; --depending on how you configure your WAS, you have to comment out COMMIT.
$$
Wrap Up
When it comes to database performance, the application design matters. Try to use the set based techniques.