The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Monday, 28 January 2013

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.

12 comments:

  1. I am glad that I have visited your blog, really amazing. Waiting for further updates.

    Datascience Training in Chennai
    Tableau Training in Chennai
    Full Stack Training in Chennai

    ReplyDelete
  2. Great blog thanks for sharing Looking for the best creative agency to fuel new brand ideas? Adhuntt Media is not just a digital marketing company in chennai. We specialize in revamping your brand identity to drive in best traffic that converts.

    ReplyDelete
  3. Nice blog thanks for sharing Set up a aesthetic work environment that employees love to spend time in and relieve their stress. Your company needs the best corporate gardening service in Chennai and Karuna Nursery Gardens in happy to oblige you in the endeavour to make your infrastructure something worth flaunting about.

    ReplyDelete
  4. Excellent blog thanks for sharing Run your salon business successfully by tying up with the best beauty shop in Chennai - The Pixies Beauty Shop. With tons of prestigious brands to choose from, and amazing offers we’ll have you amazed.

    ReplyDelete
  5. Awesome blog thanks for sharing While choosing your perfect ride for driving, Accord Cars comes with and the best packages for you to pick from. Car rentals for self drive in Chennai are done the easier. Just pick out your plan from hourly, daily, weekly and even monthly plans available.

    ReplyDelete
  6. Very useful blog thanks for sharing With over a three decade of beauty expertise at our fingertips, we believed that everyone has the right to be beautiful. And so began the journey of our very own Pearl’s Beautician course in Chennai.

    ReplyDelete
  7. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Cyber Security Projects for Final Year

    JavaScript Training in Chennai

    Project Centers in Chennai

    JavaScript Training in Chennai

    ReplyDelete
  8. Awesome blog thankks for sharing 100% virgin Remy Hair Extension in USA, importing from India. Premium and original human hair without joints and bondings. Available in Wigs, Frontal, Wavy, Closure, Bundle, Curly, straight and customized color hairstyles Extensions.

    ReplyDelete
  9. Very useful blog thanks for sharing IndPac India the German technology Packaging and sealing machines in India is the leading manufacturer and exporter of Packing Machines in India.

    ReplyDelete

Followers