Skip to Content
avatar image
Former Member

Update of one field but multiple records (Performance)

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Aug 03, 2017 at 06:13 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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!

  • Aug 09, 2017 at 07:11 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded