Z Tables aupdate - performance tips

[luis_rod]

Hi all,

We need to update a lot of our Z tables (some of them quite big) and would appreciate any performance tips (besides the obvious "create an appropiate index").

Our problem, in pseudo-code, is as follows:

LOOP until eof.
  SELECT single record k1,  f1, f2, f3 from ZDB001.
  NF1 = F1*X. NF2 = F2*X. NF3 = F3*X.
  UPDATE ZDB001 SET F1 = NF1,  F2=NF2,  F3= NF3 where key = zdb001~k1.
ENDLOOP.

In other words, loop thru the table, updating some (currency) columns according to a simple formula. As I see it,the bottleneck would be, of course,, in the SELECT/UPDATE access times. Were I be working with Db2, for instance, I would create some SQL functions and run a massive UPDATE statement (something that, AFAIK, can't be done in the SAP version we are currently using).

Any ideas?

Thanks in advance,

Luis

[luis_rod]

Hi all,

We need to update a lot of our Z tables (some of them quite big) and would appreciate any performance tips (besides the obvious "create an appropiate index").

Our problem, in pseudo-code, is as follows:

LOOP until eof.
  SELECT single record k1,  f1, f2, f3 from ZDB001.
  NF1 = F1*X. NF2 = F2*X. NF3 = F3*X.
  UPDATE ZDB001 SET F1 = NF1,  F2=NF2,  F3= NF3 where key = zdb001~k1.
ENDLOOP.

In other words, loop thru the table, updating some (currency) columns according to a simple formula. As I see it,the bottleneck would be, of course,, in the SELECT/UPDATE access times. Were I be working with Db2, for instance, I would create some SQL functions and run a massive UPDATE statement (something that, AFAIK, can't be done in the SAP version we are currently using).

Any ideas?

Thanks in advance,

Luis

[Sandra_Rossi]

I don't know whether the dbsl for DB/2 is optimized for doing bulk SQL updates as Oracle do, but it's worth trying to create an updatable DDIC view with only the key fields + the fields you want to update, and do a :

MODIFY updatableview FROM TABLE itab.

Of course, you won't create an updatable view for every group of fields you need to mass update, so it should be done only if the performance tests show there's a clear benefit.

[luis_rod]

Sandra,

Thanks for your post.

As I see it, the problem of using a MODIFY statement is that I would need to create (and populate) a very large itab (>8M records), update its columns and run the modify afterwards. I worry mostly about the "loading" part. ¿How efficient would that be?

Thanks again,

Luis

[Sandra_Rossi]

You may do a loop with a given number of rows each time : SELECT * FROM updatableview PACKAGE SIZE 100000 ... ENDSELECT (or using its counter part OPEN CURSOR ... FETCH NEXT ... CLOSE CURSOR).

[luis_rod]

I think that would be an acceptable compromise. Half way between single selects/updates (which are quite inefficient) and pre-loading huge itabs.

Thanks again,

Luis