Using Set Processing Examples

Each of the following topics contains an example of set processing.

Payroll

In this example, suppose the payroll department needs to give a 1000 USD salary increase to everyone whose department made more than 50,000 USD profit. The following pseudocode enables you to compare the row-by-row and set-based approaches.

  • Row-by-Row:

    declare A cursor for select dept_id from department where profit > 50000;
open A;
fetch A into p_dept_id
while sql_status == OK
   update personnel set salary = (salary+1000) where dept_id = p_dept_id;
   fetch A into p_dept_id;
end while;
close A;
free A;

  • Set-Based:

    update personnel set salary = (salary + 1000)
   where exists
      (select ‘X’ from department
         where profit > 50000
         and personnel.dept_id = department.dept_id)

Note:

The set-based example employs a correlated subquery, which is important in set-based processing.

Temporary Tables

One technique for improving database performance is to use temporary tables to hold the results of common subqueries. Effective dating and setID indirection are common types of subqueries that you can replace with joins to temporary tables. With the joins in place, you can access the temporary table instead of doing the subquery multiple times. Not only do most databases prefer joins to subqueries, but if you combine multiple subqueries into a single join as well, the performance benefits can be significant.

In this setID indirection example, you see a join from a transaction table (keyed by BUSINESS_UNIT and ACCOUNTING_DT) to a setup table (keyed by SETID and EFFDT).

If using a single SQL statement, you need to bring in PS_SET_CNTRL_REC to map the business unit to a corresponding setID. Typically, you do this in a subquery. You also need to bring in the setup table a second time in a subquery to get the effective date (MAX(EFFDT) <= ACCOUNTING_DT). If you have a series of similar statements, performance may be negatively affected.

The alternative is to use a temporary table that is the equivalent of the setup table. The temporary table is keyed by BUSINESS_UNIT and ACCOUNTING_DT instead of SETID and EFFDT. You populate it initially by joining in your batch of transactions (presumably also a temporary table) once, as described previously, to get all the business units and accounting dates for this batch. From then on, your transaction and setup temporary tables have common keys, which allow a straight join with no subqueries.

For the example, the original setup table (PS_ITEM_ENTRY_TBL) is keyed by SETID, ENTRY_TYPE and EFFDT.

The denormalized temporary table version (PS_ITEM_ENTRY_TAO) is keyed by PROCESS_INSTANCE, BUSINESS_UNIT, ENTRY_TYPE and ACCOUNTING_DT, and carries the original keys (SETID and EFFDT) as simple attributes for joining to other related setup tables, as in PS_ITEM_LINES_TBL for this example.

If the program references the setup table in only one Insert/Select or Select statement, you would not see increased performance by denormalizing the temporary table. But if several SQL statements are typically executed in a single run, all of which join in the same setup table with similar setID and effective date considerations, then the performance cost of populating the temporary table initially provides long-term advantages.

  • Original setup table version:

    INSERT INTO PS_PG_PENDDST_TAO (...)
SELECT 
. . . . . 
 ( (I.ENTRY_AMT_BASE - I.VAT_AMT_BASE) * L.DST_LINE_MULTIPLR * L.DST_LINE_PERCENT /⇒
 100 ),   ( (I.ENTRY_AMT - I.VAT_AMT) * L.DST_LINE_MULTIPLR * L.DST_LINE_PERCENT / 100 ),
. . . . .
FROM  PS_PENDING_ITEM I, PS_PG_REQUEST_TAO R, PS_ITEM_LINES_TBL L,
      PS_ITEM_ENTRY_TBL E, PS_SET_CNTRL_REC S, PS_BUS_UNIT_TBL_AR B
. . . . .WHERE  AND L.ENTRY_REASON = I.ENTRY_REASON AND L.SETID = E.SETID AND L.ENTRY_TYPE = E.ENTRY_TYPE AND L.EFFDT = E.EFFDT. . . . .
 AND E.EFF_STATUS = 'A'
 AND S.RECNAME = 'ITEM_ENTRY_TBL'
 AND S.SETID = E.SETID
 AND S.SETCNTRLVALUE = I.BUSINESS_UNIT
 AND E.ENTRY_TYPE = I.ENTRY_TYPE
 AND E.EFFDT = (SELECT MAX(EFFDT) FROM PS_ITEM_ENTRY_TBL Z
                   WHERE Z.SETID = E.SETID
                     AND Z.ENTRY_TYPE = E.ENTRY_TYPE
                     AND Z.EFF_STATUS = 'A'
                     AND Z.EFFDT <= I.ACCOUNTING_DT )
 AND B.BUSINESS_UNIT = I.BUSINESS_UNIT
/

  • Denormalized temporary table version:

    INSERT INTO PS_ITEM_ENTRY_TAO
. . . . .  
SELECT DISTINCT %BIND(PROCESS_INSTANCE), I.BUSINESS_UNIT, I.ACCOUNTING_DT,
 E.ENTRY_TYPE...
. . . 
FROM  PS_PENDING_ITEM I, PS_PG_REQUEST_TAO R, 
      PS_ITEM_ENTRY_TBL E, PS_SET_CNTRL_REC S, PS_BUS_UNIT_TBL_AR B 
WHERE R.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
 AND R.PGG_GROUP_TYPE = 'B'
 AND I.POSTED_FLAG = 'N'
 AND R.GROUP_BU = I.GROUP_BU
 AND R.GROUP_ID = I.GROUP_ID
 AND E.EFF_STATUS = 'A'
 AND S.RECNAME = 'ITEM_ENTRY_TBL'
 AND S.SETID = E.SETID
 AND S.SETCNTRLVALUE = I.BUSINESS_UNIT
 AND E.ENTRY_TYPE = I.ENTRY_TYPE
 AND E.EFFDT =  ( SELECT MAX(EFFDT) FROM PS_ITEM_ENTRY_TBL Z
                   WHERE Z.SETID = E.SETID
                     AND Z.ENTRY_TYPE = E.ENTRY_TYPE
                     AND Z.EFF_STATUS = 'A'
                     AND Z.EFFDT <= I.ACCOUNTING_DT )
 AND B.BUSINESS_UNIT = I.BUSINESS_UNIT
/
INSERT INTO PS_PG_PENDDST_TAO (...)
SELECT ... 
 ( (I.ENTRY_AMT_BASE - I.VAT_AMT_BASE) * L.DST_LINE_MULTIPLR * L.DST_LINE_PERCENT /⇒
 100 ),
( (I.ENTRY_AMT - I.VAT_AMT) * L.DST_LINE_MULTIPLR * L.DST_LINE_PERCENT / 100 ),
. . . . .
FROM  PS_PENDING_ITEM I, PS_PG_REQUEST_TAO R, PS_ITEM_LINES_TBL L, 
      PS_ITEM_ENTRY_TAO E
. . . . .
WHERE
. . . . .
 AND L.ENTRY_REASON = I.ENTRY_REASON
 AND L.SETID = E.SETID
 AND L.ENTRY_TYPE = E.ENTRY_TYPE
 AND L.EFFDT = E.EFFDT
. . . . .
 AND E.BUSINESS_UNIT = I.BUSINESS_UNIT
 AND E.ACCOUNTING_DT = I.ACCOUNTING_DT
 AND E.ENTRY_TYPE = I.ENTRY_TYPE
/

Platform Issues

Set processing does not behave the same on every database platform. On some platforms, set processing can encounter performance breakdowns. Some platforms do not optimize update statements that include subqueries.

For example, environments that are accustomed to updates with subqueries get all the qualifying department IDs from the Department table and then, using an index designed by an application developer, update the Personnel table. Other platforms read through every employee row in the Personnel table and query the Department table for each row.

On platforms where these types of updates are a problem, try adding some selectivity to the outer query. In the following example, examine the SQL in the Before section and then notice how it is modified in the After section to run smoothly on all platforms. You can use this approach to work around platforms that have difficulty with updates that include subqueries.

Note:

In general, set processing capabilities vary by database platform. The performance characteristics of each database platform differ with more complex SQL and set processing constructs. Some database platforms allow additional set processing constructs that enable you to process even more data in a set-based manner. If performance needs improvement, you must tailor or tune the SQL for your environment. You should be familiar with the capabilities and limitations of your database platform and be able to recognize, through tracing and performance results, the types of modifications you need to incorporate with the basic set processing constructs described.

  • Basic version:

    UPDATE PS_REQ_LINE
SET SOURCE_STATUS = 'I'
WHERE
EXISTS
(SELECT 'X' FROM PS_PO_ITM_STG STG
WHERE
STG.PROCESS_INSTANCE =%BIND(PROCESS_INSTANCE)  AND
STG.PROCESS_INSTANCE =PS_REQ_LINE.PROCESS_INSTANCE AND
STG.STAGE_STATUS = 'I'  AND
STG.BUSINESS_UNIT = PS_REQ_LINE.BUSINESS_UNIT AND
STG.REQ_ID = PS_REQ_LINE.REQ_ID AND
STG.REQ_LINE_NBR = PS_REQ_LINE.LINE_NBR)

  • Optimized for platform compatibility:

    UPDATE PS_REQ_LINE
SET SOURCE_STATUS = 'I'
WHERE
PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND
 EXISTS
(SELECT 'X' FROM PS_PO_ITM_STG STG
WHERE
STG.PROCESS_INSTANCE =%BIND(PROCESS_INSTANCE)  AND
STG.PROCESS_INSTANCE =PS_REQ_LINE.PROCESS_INSTANCE AND
STG.STAGE_STATUS = 'I'  AND
STG.BUSINESS_UNIT = PS_REQ_LINE.BUSINESS_UNIT AND
STG.REQ_ID = PS_REQ_LINE.REQ_ID AND
STG.REQ_LINE_NBR = PS_REQ_LINE.LINE_NBR)

Note:

This example assumes that the transaction table (PS_REQ_LINE) has a PROCESS_INSTANCE column to lock rows that are in process. This is another example of designing your database with batch performance and set processing in mind.

This modification enables the system to limit its scan through PS_REQ_LINE to only those rows that the program is currently processing. At the same time, it enables a more set-friendly environment to first scan the smaller staging table and then update the larger outer table.