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.
the above content you shared is very useful and the way of presentation is easy to understand.
ReplyDeleteSelenium Training in Bangalore
Selenium Training Institutes in Bangalore
Hadoop Training in Bangalore
Ethical Hacking Course in Bangalore
RPA Training in Bangalore
Data Science Training in Bangalore
German Language Course in Bangalore
Best AWS Training in Bangalore
ReplyDeleteI am feeling happy to read this. You gave nice info to me. Please update more.
Ethical Hacking course in Chennai
Ethical Hacking Training in Chennai
Hacking course
ccna course in Chennai
Salesforce Training in Chennai
Angular 7 Training in Chennai
Web Designing course in Chennai
Ethical Hacking course in Thiruvanmiyur
Ethical Hacking course in Porur
Ethical Hacking course in Adyar
I am glad that I have visited your blog, really amazing. Waiting for further updates.
ReplyDeleteDatascience Training in Chennai
Tableau Training in Chennai
Full Stack Training in Chennai
I would like to thank you so much for sharing with us and I have many ideas after visiting your post. Well done...
ReplyDeleteJMeter Training in Chennai
JMeter Certification
Linux Training in Chennai
Pega Training in Chennai
Primavera Training in Chennai
Unix Training in Chennai
Placement in Chennai
Tableau Training in Chennai
Oracle Training in Chennai
JMeter Training in T Nagar
JMeter Training in OMR
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.
ReplyDeleteNice 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.
ReplyDeleteExcellent 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.
ReplyDeleteAwesome 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.
ReplyDeleteVery 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.
ReplyDeleteI 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.
ReplyDeleteCyber Security Projects for Final Year
JavaScript Training in Chennai
Project Centers in Chennai
JavaScript Training in Chennai
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.
ReplyDeleteVery 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.
ReplyDeletewonderful article contains lot of valuable information. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
ReplyDeleteThis article resolved my all queries.good luck an best wishes to the team members.continue posting.learn digital marketing use these following link
Digital Marketing Course in Chennai