Set Processing has been in existence for many many years. In fact, it has been around since the introduction to SQL. Since PeopleSoft uses a relational database and uses SQL to access data, you are able to utilize Set Processing in any application including Application engine, SQR's and even PeopleCode.
So, what is Set Processing and how can we use it?
Most programmers view data in a row by row fashion when designing their programs. To understand what Set Processing is, you first need to understand row-by-row processing. For the purpose of our example, let's assume we are going to loop through each employee's job record and update their job code to a new one based on a table that contains both the old and the new job code. The records and fields we'll be using are below:
JOB Record
EMPLID - Key
JOBCD
JOBCD_CHG Record
JOBCD - Key
NEW_JOBCD
In most cases, developers start with a DO SELECT which basically loops through each row.
DO Select
%select(EMPLID, JOBCD)
SELECT EMPLID, JOBCD from PS_JOB
The next statement would then take the bind variables from each row fetched.
SQL
%select(NEW_JOBCD)
select NEW_JOBCD from PS_JOBCD_CHG
where JOBCD = %bind(JOBCD)
Then the next statement would update the employee's record with the new JOBCD fetched in the previous SQL.
SQL
UPDATE PS_JOB
SET JOBCD = %bind(NEW_JOBCD)
WHERE JOBCD = %bind(JOBCD)
and EMPLID = %bind(EMPLID)
This example, basically illustrates how to use application engine to update the employees record with a row by row processing method. This is not the most efficient way to update the employee records. In fact, we can do this with a single statement if we used set processing. Of course, this is a very basic example, however, it illustrates my point.
SQL
UPDATE PS_JOB
set JOBCD = (SELECT A.NEW_JOBCD
FROM PS_JOBCD_CHG A
WHERE PS_JOB,JOBCD = A.JOBCD)
Again, I am being very simplistic in this example, but it does make the point of visualizing set processing as a way to update a set, group, or block of data instead of a row by row method. In other words, look for areas where multiple steps or SQL statements can be combined into one statement. The idea here is not to make large and complex series of SQL statements but to combine like information into a single efficient SQL statement.
Coding in this manner will greatly enhance the performance of your applications. Of course, you'll need to understand the key structures and indexes to ensure that your SQL is written in the most efficient manner. If you'd like to learn more about tuning Peoplesoft, you can download the Tuning Your PeopleSoft Applications for Maximum Performance eBook.
This process, although simple in nature, can have a profound effect on your program.For example, here is a tip to use when writing Set Processing SQL code.
Group like statements together into the same section. This will not enhance performance but will make your program easier to read and understand. You, as the developer, need to not only make an efficient program but one that others can review and modify after you're gone.
So, what is Set Processing and how can we use it?
Most programmers view data in a row by row fashion when designing their programs. To understand what Set Processing is, you first need to understand row-by-row processing. For the purpose of our example, let's assume we are going to loop through each employee's job record and update their job code to a new one based on a table that contains both the old and the new job code. The records and fields we'll be using are below:
JOB Record
EMPLID - Key
JOBCD
JOBCD_CHG Record
JOBCD - Key
NEW_JOBCD
In most cases, developers start with a DO SELECT which basically loops through each row.
DO Select
%select(EMPLID, JOBCD)
SELECT EMPLID, JOBCD from PS_JOB
The next statement would then take the bind variables from each row fetched.
SQL
%select(NEW_JOBCD)
select NEW_JOBCD from PS_JOBCD_CHG
where JOBCD = %bind(JOBCD)
Then the next statement would update the employee's record with the new JOBCD fetched in the previous SQL.
SQL
UPDATE PS_JOB
SET JOBCD = %bind(NEW_JOBCD)
WHERE JOBCD = %bind(JOBCD)
and EMPLID = %bind(EMPLID)
This example, basically illustrates how to use application engine to update the employees record with a row by row processing method. This is not the most efficient way to update the employee records. In fact, we can do this with a single statement if we used set processing. Of course, this is a very basic example, however, it illustrates my point.
SQL
UPDATE PS_JOB
set JOBCD = (SELECT A.NEW_JOBCD
FROM PS_JOBCD_CHG A
WHERE PS_JOB,JOBCD = A.JOBCD)
Again, I am being very simplistic in this example, but it does make the point of visualizing set processing as a way to update a set, group, or block of data instead of a row by row method. In other words, look for areas where multiple steps or SQL statements can be combined into one statement. The idea here is not to make large and complex series of SQL statements but to combine like information into a single efficient SQL statement.
Coding in this manner will greatly enhance the performance of your applications. Of course, you'll need to understand the key structures and indexes to ensure that your SQL is written in the most efficient manner. If you'd like to learn more about tuning Peoplesoft, you can download the Tuning Your PeopleSoft Applications for Maximum Performance eBook.
This process, although simple in nature, can have a profound effect on your program.For example, here is a tip to use when writing Set Processing SQL code.
Group like statements together into the same section. This will not enhance performance but will make your program easier to read and understand. You, as the developer, need to not only make an efficient program but one that others can review and modify after you're gone.
No comments:
Post a Comment