row-by-row solution vs. set-based solution 2

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 t​ries 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.