SAP HANA SQL Update on large table

[Former Member]

Hi all,

I'm struggeling with a simple move of one field to another within a single table, consisting of 2 Billion recs.

To following approaches lead into the error message "transaction rolled back by an internal error: TableUpdate failed" due to Memory Overflow.

1. Simple update failed:

Update t1.QTY_2 = t1.QTY_1;

2. Cursor Approach failed eiter:

declare cursor c_cur for select * from t1 for update;    

for r1 as c_cur do
             v_count := :v_count + 1;
             Update t1.QTY_2 = t1.QTY_1

             where t1.ID = r1.ID ;
            
             if MOD(:v_count,100) = 0 then        --commit each 100 recs.
                   commit;
             end if;
    end for;
commit;

Does anybody out there has an idea, how to split the update into digestible slices?

I'm running out of ideas.

Thanks in advance

Cheers

Sven

[lbreddemann · Accepted solution]

Not sure when updating 2 billion records became 'a simple move' - but this approach would likely be problematic with most DBMS...

Anyhow, since you don't give us any more information that that you have a table and that there are two columns in the table QTY_1 and QTY_2, how about this:

update TOP 100000 <table>

     set QTY_2 = QTY_1

where QTY_2 != QTY_1;

Run that again and again as long as there are records updated and you should get through...

UPDATE - SAP HANA SQL and System Views Reference - SAP Library

[Former Member · Accepted solution]

Hi Lars,

thanks for the update. But I assume that updating the first 100000 recs will not do.

However, I thought about something like sending a commit and a merge Delta after the processing of let's say 1.000.000 recs and go ahead with the next package.

I have a monotoneous increasing 10 digit id with a 1 increment, starting somewhere.

Currently I have worked around the stuff by slicing the data in monthly packages (50.000.000 Million recs) and that worked by calling the same stored procedure n times with different Input parameters.

But I suspect that there is a more sophisticated solution than that, like a clever for do end for Loop.

Any suggestions? Every idea is welcome 

[lbreddemann · Accepted solution]

Sven,

please read again what I wrote: you should re-run the statement over and over again.

This is a batch approach to take 100000 records at a time.

That way you don't need to manually partition your data, it always takes the first x records that have not yet the target state and performs the update on those.

Assuming you run this in the SQL Editor, there will be an automatic commit after each UPDATE TOP n.. if you don't: just run a commit afterwards.

Before fiddling with delta merges etc. have you looked into partitioning?

Besides all that, what is this for in the first place? Arbitrarily updating 2 billion records must have a reason. If this happens during data loading, you may want to use EIM, SAP Data Services or SAP BW on HANA to have automatic batching...

[Former Member · Accepted solution]

Hi Lars,

In service pack 8, I'm getting an error with the Update Top approach.  It says incorrect syntax near top.  Is this a new feature of SAP Hana?

My syntax is like this:

UPDATE TOP 100000 "myschema"."myTable"  A

SET PLANT =

(

SELECT B.SUPP_PLANT

FROM PUBLIC.Some_synonym B

WHERE

  A.SHIP_TO_ID = B.SHIP_TO

AND A.MATERIAL_ID = B.MATERIAL

AND A.SALES_DATE = B."/BIC/ZDM_DATE"

)

WHERE A.PLANT IS NULL;

ERROR: sql syntax error: incorrect syntax near "TOP": line 1 col 8 (at pos 😎

I have no issue running this wide open without TOP, just trying to get faster speed by updating fewer rows with a loop.

I can easily SELECT TOP with no error.  Just UPDATE TOP will not go.

Thanks.

[lbreddemann · Accepted solution]

UPDATE TOP had been introduced with SPS 10, so it's relatively new.

As I don't have a SPS8 ready anymore I cannot build a working example for a manual approach here that is guaranteed to work on SPS 8.

The basic approach will stay the same however:

as apparently the table contains too many rows to have them all in memory at least twice (old and new version + all the extra stuff required for delta merges) the update operation needs to be split up.

Partitioning this table would be a good idea anyhow and running multiple cycles of update-commit-merge should eventually get you there.