topic Re: ABAP&SQL Performance in Application Development and Automation Discussions

ABAP&SQL Performance

former_member205645 — Tue, 11 Feb 2014 08:46:05 GMT

Hello Gurus,

I have to update two Z tables from an excel file that contains 1,6 milion rows. The tables on the DB have about 30 milion rows.

I only have to update one or two columns out of 98.

The loading of the file is done and working well BUT ... in order to use UPDATE dbtab FROM itab I have to read the 1,6 milion rows. While using FOR ALL ENTRIES IN the Open-Sql generates about 1 SELECT for each ROW, which is very very bad. Also a I tried to use OPEN CURSOR and only read 5000 rows and it took about 5 min. ... very slow. i have checked all the keys and also sorted the tables.

What do you guys think? Should I write a UPDATE in a LOOP or use the SELECT ... UP TO ROWS ... FOR ALL ENTRIES and then UPDATE dbtab FROM itab.

As this is a report that will only run once I don't think I should parallelize it.

Thanks,

Ioan.

Re: ABAP&SQL Performance

thangam_perumal — Tue, 11 Feb 2014 09:14:05 GMT

Hi Loan,

Please try to use Modify statement with in loop.

Regards,

Thangam.P

Re: ABAP&SQL Performance

former_member205645 — Tue, 11 Feb 2014 09:19:55 GMT

The table after FOR ALL ENTRIES IN is way too big this is a problem(1,6 milion). I will try different sizes.

Re: ABAP&SQL Performance

former_member205645 — Tue, 11 Feb 2014 10:45:35 GMT

It works with table size 650.000 after FOR ALL ENTRIES IN. That was the problem.