Update of one field but multiple records (Performance)

[marcel_driller]

Hi

I hope you can help me with this one.

In my scenario I want to update one specific field of multiple specific records in a database table. One option would be to use the update statement in addition with 'SET' for each individual record or to do the update with the use of 'FROM TABLE' but without 'SET'.

I guess it is important to know how many entries I have to update. Let's assume I have to update around 50 entries in a table with nearly 800,000 records. It is not really a performance critical task but I'm still curious what is better.

I already thought about a lot of pros and cons but did not came to a conclusion yet. Would I have to update a large amount of records and multiple fields I would definitely go for the 'FROM TABLE'. I know that each update will cause additional overhead costs but I somehow believe the 'SET' can give me a advantage as well especially for the amount of records I have.

What do you think?

Thanks for any input you have!

[Sandra_Rossi]

Here are the results of a test I had done 1.5 years ago after I have created a database view on VBAK table, with update enabled, and using ABAP UPDATE table or view FROM TABLE itab. The result would surely be the same ratio with MODIFY.

VBAK database table has 159 columns, 2216 bytes wide, and 200.000 records.

The database was ORACLE 11.2. SAP 7.40.

I created a DDIC database view on VBAK, restricted to 8 columns (comprising the column to be updated) and is 130 bytes wide. The amount of data sent to the database is reduced considerably.

Results: I ran the test 5 times, for updating the 200.000 rows, I obtained:

• 24 seconds via the database table
• 6 seconds via the view

It's of course very interesting only if there are lots of updates.

[pokrakam]

Interesting results, not entirely unexpected.

But, unless I'm missing something it doesn't cover the OPs question of SET vs FROM TABLE as both your tests used an itab as the source.

By my understanding SET should be significantly faster, because the DB doesn't need to 'receive' the data for each row as it only has a single value to apply for all rows. So the processing happens at a lower level. But I would be interested in seeing this verified on a real system. I might write a little test report if I have some time and nobody has already done this.

[Sandra_Rossi]

think it covers one part of the question as the question is about performance

[horst_keller]

It says, OP should write such a test himself 😉

[pokrakam]

I wasn't dismissing your question, I think it was a valuable contribution. Sorry if you misunderstood!

What I was suggesting is that the main gist of the Q was the comparison between FROM TABLE and SET. You provided useful insights into the first variant.

Thinking about it some more, FROM TABLE would involve the app server doing some preprocessing into a heap of SQL, whereas SET could probably be passed straight to the DB. So my money stays on SET.

[Sandra_Rossi]

I understand. My answer is one part of the topic, I recommend you to add another answer for talking exactly about the difference between FROM TABLE and SET, which one is the best in which condition. My answer is only the "third" answer for "what is the most performing way for updating".

[marcel_driller]

Thanks Sandra for the insights on your test. I did not really think about creating a view before. Good idea especially for larger tables though it does not really fit my use case. I will try to create a test report when I find some time and put my results here.

Thanks to all of you for your answer already!

[Sandra_Rossi]

I think that the answer depends on the implementation of the used database (Oracle, MSSQL, HANA, etc.), i.e. the combination of the SAP Database Interface (DBSL/Database Library) and the features of the database system, and so it may also depend on the versions of these softwares.

My answer is mostly a guess, valid for Oracle only (answer for HANA would be probably completely different), and it is for the case you want to update a column (or columns) with different values for every row i.e. you don't update a column with the same value, because then you should use only one UPDATE ... SET column = value WHERE all the rows you want, and I'm undoubtedly sure that it's faster than a MODIFY ... FROM TABLE ... This answer is also only comparing UPDATE ... FROM ... TABLE and UPDATE ... SET ... WHERE the primary key.

With Oracle, there could be 2 possibilities but I didn't test:

• Either the DBI implements a UPDATE ... FROM TABLE ... as a sequence of UPDATE ... SET ..., with all the fields of the table. In that case, there
• Or it does a mass update (FORALL UPDATE), and so it would be much faster than single updates (UPDATE alone) if you have to update many rows. It's easy to understand that it can be the same performance difference between a SELECT using a full scan and a SELECT using a table scan by index rowid.

With Oracle, I don't think there can be an advantage with many single UPDATE over one UPDATE FROM TABLE, simply because there's no chance that ABAP/Oracle can do a bulk update as they both process every statement individually.

With Oracle, about the difference of updating one column versus multiple columns, I don't think there is any difference (or very tiny), as every record is to be fully accessed on the disk (and the disk access). And I guess that when all the columns are provided for the update, Oracle detects which columns are changed, to only access the indexes which need to be updated.

PS: there could be big differences if you deal with LOB columns.

PS: you also have the MODIFY ... FROM TABLE ... which is theorically slower, as there are additional operations to know whether the record exists or not, to know if an UPDATE or an INSERT is to be done. For instance, with Oracle database, it may do INSERT for all lines of the internal table, followed by a UPDATE for all lines which couldn't be inserted, meaning they already exist.