topic Re: Performance issue while working with large files. in Application Development and Automation Discussions

Performance issue while working with large files.

former_member205645 — Tue, 25 Mar 2014 06:21:57 GMT

Hello Gurus,

I have to upload about 1 million keys from a CSV file on the application server and then delete the entries from a DB table containing 18 million entries. This is causing performance problems and my programm is very slow. Which approach will be better?

1. First read all the data in the CSV and then use the delete statement?

2. Or delete each line directly after reading the key from the file?

And another program has to update about 2 million entries in a DB table containing 20 million entries. Here I also have very big performance problems(the program has been running for more the 14 hours). Which is the best way to work with such a large amount?

I tried to rewrite the program so that it will run parallel but since this program will only run once the costs of implementing a aRFC parallization are too big. Please help, maybe someone doing migration is good at this

Regards,

Ioan.

Re: Performance issue while working with large files.

yogendra_bhaskar — Tue, 25 Mar 2014 06:51:44 GMT

Hi loan ,

Schedule it in background.

regards,

Yogendra Bhaskar

Re: Performance issue while working with large files.

former_member205645 — Tue, 25 Mar 2014 07:05:48 GMT

I have just tested the first program.

Delete each line directly after reading the key from the file is definitely much faster

Re: Performance issue while working with large files.

Former Member — Tue, 25 Mar 2014 07:07:44 GMT

Hi,

I would suggest, you should split the files and then process each set.

lock the table to ensure it is available all time.

After each set ,do a commit and then proceed.

This would ensure there is no break in middle and have to start again by deleteing the entries from files which are already processed.

Also make use of the sorted table and keys when deleting/updating DB.

In Delete, when multiple entries are involved , use of an internal table might be tricky as some records may be successfully deleted and some maynot.

To make sure, first get the count of records from DB that are matching in Internal table set 1

Then do the delete from DB with the Internal tabel set 1

Again check the count from DB that are matching in Internal table set 1 and see the count is zero.

This would make sure the entire records are deleted. but again may add some performance

And the goal here is to reduce the execution time.

Gurus may have a better idea..

Regards

Sree

Re: Performance issue while working with large files.

Former Member — Tue, 25 Mar 2014 13:01:39 GMT

Hello Ioan,

Query 1 -

Try forming a range table from the uploaded million entries. For making range table you will need to put a Loop.

Post that use a single Delete statement.

Also splitting the million entries into smaller files and running parallel SE38 programs will be advantageous. Implement Key based locks so that you can run multiple SE38 programs simultaneously on same table.

Query 2 -

Follow same advice -

BR.

Re: Performance issue while working with large files.

former_member205645 — Wed, 26 Mar 2014 07:03:54 GMT

Hi,

Unfortunately I can not build ranges

It seems that processing each line rather than using "for all entries" is much faster so I will stick to that for now.

Regards,

Ioan.