You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Set processing in Application Engine

4 replies [Last post]
Guest's picture
Guest

Compshack,
Can you point me to some good resource on set processing ?

I am finding very limited material on it. I understand the concept but find it difficult to understand how to implemetn it in a relatively medium complex AE.

Anyone willing to share ideas ?

If you are looking to understand, then we can use this as basis to exchange ideas and learn...

CompShack's picture
User offline. Last seen 6 years 4 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Set processing in Application Engine

Extremely efficient programs can be created using set processing techniques. Application Engine was designed with this technique in mind. Set processing is the opposite of row-by-row processing. Set processing can also be referred to as mass update.

Let me use a very simple example to explain how this is done. I'm sure you are familiar with SQR, so here is a comparison between how SQR update a table (row-by-row) compared to AE set processing.

We are going to update table1 in our SQR program

Begin-select
Key_field
Deptid
        Let $key_field = &key_field
        Do update-table1
From table1
Where account ='C'

End-select

Begin-procedure update-table1

Begin-sql
Update table1
Set deptid = 'new id'
Where fey_field = $key_field

end-procedure

You can see from the example that we are calling a procedure to update table1 for every row (one row at a time) that has an account type of "C".

Now, let’s take a look at how this is done using Set Processing in an application engine.

Update table1
Set deptid = 'new id'
Where account = 'C'

This one Update statement populates all the rows that match this criteria at once NOT row-by-row!

This is as simple as it can get, does it make sense?

n/a
Guest's picture
Guest (not verified)
Re: Set processing in Application Engine

Compshack,

If we use state record in AE, that will become row by row processing right? Please clarify me.

Thanks

CompShack's picture
User offline. Last seen 6 years 4 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Set processing in Application Engine

The set processing SQL concept has been around for many years and it is not specific to Application Engines. In other words, Set Processing is not dependent on a state record. It all depends on how you write your update statements and Application engine steps and actions.

For example, if you use a Do Select action and then an SQL action to update rows by passing your binds stored in the state record as the result of your Do Select, then YES you are doing row-by-row update. However, you can still have a state record, BUT use a mass sql update (set processing) to update more than one row at one time.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

HH's picture
HH (not verified)
Re: Set processing in Application Engine

Compshack,
I do understand the concept. But once I try to implement it for a more complex solution, I get stumped.
For e.g. lets say I need to update 10 fields on a table and need to process 100000 rows.
For each of the fields the value to be derived is not straight forward.
for e.g. for field one,
update with value from table a
if not found update from table b.
if still not found, update from table c.
if value still not found, update with value of 'NONE'.

Now if all the 10 fields values have to be derived this way. Here the options;

1. Row by row processing
1. derive the values for all the 10 fields and fire a single update statement.

2. Set processing wthout temp tables:

1. maintain field 1
First update with value from table A
if not found, update with value from table B
if still not found update with value from table c
if still not found update to 'NONE.
All are update statements.
2. Maintain all other fields in the above manner.

In essence, I am updating multiple times. Which I frankly think is an over kill.

3. Set based processing using temp tables.
( Move the data to a temp table and fire the above upates )

How would I chose what to use ? I understand that there will not be a clear answere and depends on the process requirement.

Is it possible to share a example on each and why one method is preferred over the other.

Maybe some of your readers would like to chip in too ?