Set Processing and Row by row processing

Recently in a Peoplesoft thread, I have seen many questions on set processing and row by row processing. I think it’s just a lack of clear understanding on the subject is what makes this simple concept to be repeatedly asked in the forums. I thought of explaining it in a simpler way.

We might have heard these two words mostly when dealing with applications engines. But I would like to make it clear that, set processing and row by row processing are two different methods to update data in generic. So what I meant to say is that, these two processes are not tied only to application engines. Wherever you update to sql tables, (eg: PeopleCode or codes outside peoplesoft) these two processes are applicable. Now let me explain both the process.

Row by Row Processing

By definition, with this method, we select each row and do the action. Suppose I have a table COUNTRY with below data.

COUNTRY	CITY	STATUS
USA	LA	A
IND	BLR	I
GBR	LON	I
AUS	SYD	A

Now my requirement is to clear the CITY for all countries with STATUS = I.

If I do row by row method, then I will select the first country with status I and then update the city. Then the next country, do update and so on.

If implemented in app engine, it would look like below.

Do Select Action

%Select(COUNTRY)

SELECT COUNTRY FROM PS_COUNTRY WHERE STATUS = ‘I’

Sql Action

UPDATE PS_COUNTRY SET CITY=’ ‘ WHERE COUNTRY =%BIND(COUNTRY)

If you use PeopleCode then instead of Do Select action, it will be a sql fetch and SQL action will be replaced with a SQLExec(). This is the only difference. In any language or technology if you follow this process to make update, then it is called row by row processing.

Set based processing

As the name indicates, set based processing means processing a bunch of rows as a set. On the contrary to row by row processing, in set based processing all the affected rows are updated at a stretch. If we take the previous example, the set based implementation will be as below.

SQL Action

UPDATE PS_COUNTRY SET CITY=’ ‘ WHERE STATUS =’I’

If it is peoplecode implementation, then the sql action will be replaced with a SQLExec().

Set based processing has an advantage over row by row processing. In most of the cases set based processing will be faster. The reason is obvious; set based processing needs to issue only one sql instruction, there by one db trip and reduced timings. However there are scenarios where row by row processing has an edge over the set based processing. For instance, if your select query contains joins to 2 or more high volume tables. Then it will be better to split the sql to go by row by row method. But still it varies from case to case. In general set based processing has an edge over row by row processing when it comes to performance stand point.