topic Re: Z Tables aupdate - performance tips in Application Development and Automation Discussions

Z Tables aupdate - performance tips

luis_rod — Fri, 23 Mar 2018 16:00:00 GMT

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

Re: Z Tables aupdate - performance tips

Sandra_Rossi — Fri, 23 Mar 2018 16:25:54 GMT

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.

Re: Z Tables aupdate - performance tips

luis_rod — Fri, 23 Mar 2018 19:54:30 GMT

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

Re: Z Tables aupdate - performance tips

Sandra_Rossi — Fri, 23 Mar 2018 20:14:38 GMT

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).

Re: Z Tables aupdate - performance tips

luis_rod — Fri, 23 Mar 2018 20:19:39 GMT

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