Fastest way to download 20 million records as a file

[nagaraosunkara]

Hello All,

We have a requirement of downloading 20 million record entries from abap table as a file. What would be the fastest way to do this? Is there any way to get it done in 90 mins?

Following are the blockers that we see:

1. We have fetch data to internal table only with limit of 200,000(due to memory),

2. We thought splitting data in blocks and place in AL11, so that users can download, but due to security constraints can't give access to business users for AL11

3. downloading 200,000 records as a file is taking 3-4 mins, which implies we can download around 3 to 4 million in an hour and downloading 20 million will take around 5 hours and basis had set time out as 120 mins.

Any pointers will be helpful.

Thanks and Regards,

Naga

[michael_eaton3]

The technique you can use will be based on the SAP system you want to extract from. For example, to extract from ECC or S/4HANA on-premise, Data Services is probably the easiest and quickest.

I recently wrote a streaming process to extract large datasets from ABAP tables, I could extract 16 million rows in 14 mins, whereas Data Services did the same extract in 7 minutes.

[nagaraosunkara]

Can you please share the starting point on how to do this?

how to write streaming process and how to do is data service?

Is this on BTP? If yes, we aren't on BTP yet.

Thanks,

Naga

[MustafaBensan]

Hi Naga,

What is the business purpose of downloading a file with 20 million records? What does the business user do with such a large file after downloading?

Regards,

Mustafa.

[michael_eaton3]

If your SAP system is on-premise and you are licensed to use SAP Data Services, that will be the easy option for you as it supports RFC streaming for large datasets, see RFC Streaming

SAP Datasphere also supports a similar streaming mechanism, see ABAP RFC Streaming

If you want to write your own streaming mechansim, review the Data Services ABAP function module /BODS/RFC_STREAM_READ_TABLE. The concept is simple, within a SELECT -> ENDSELECT loop, assemble a small batch of records and occaisonllay write them to your destination, countinue until all rows read.

[nagaraosunkara]

Hello Mustafa,

This was my first question to the business. How can anyone verify 20 million records of data!!

business says that they want to have this data as part of compliance.

Regards,

Naga

[Sandra_Rossi]

I'm surprised that you are not talking about the volume in bytes, and you are not talking about compression.

It's an important aspect of this requirement.

[nagaraosunkara]

Hello Michael Eaton,

Thanks for the info.

I don't think we have SAP data services.

We are looking at solution either using abap or bw!

Regards,

Naga

[nagaraosunkara]

Hello Sandra Rossi,

if we are downloading file of 200,000 records it's taking 140 MB for a txt file and taking around 3-4 minutes for processing, does compressing the files increase the run time?

I see zipping file as an option to compress.

Can you please share any further points on compressing the file further.

Thanks and Regards,

Naga

[michael_eaton3]

The solution using SELECT -> ENDSELECT loop is an ABAP solution.

[nagaraosunkara]

Hi Michael,

we are implementing something similar but run time using that approach is a concern.

Regards,

Naga

[Sandra_Rossi]

Yes, compression = huge improvement of performance.

zipping is short for compressing in the language of information systems. There are many formats for compressing or zipping, but of course I'm talking about any efficient compression which has been used for decades in information systems. We don't need a super-efficient algorithm for such small amount of data 😉

[Sandra_Rossi]

Note that your concern should not be the timeout, but the Service Level Agreement of 90 minutes (is it an SLA or just what you want, based on the current 2h timeout?), because you can easily make your ABAP program ignore this timeout limit, as you can see in the forum (also in the SAP notes if I remember well).

[nagaraosunkara]

Hello Sandra,

Thanks for your inputs. Upon zipping the file, we could see that the file size reduced from 100MB to 8MB,

and processing time down from 1.5 minutes(90 seconds) to 25 seconds.

Regards,

Naga

[matt]

Write a program that gathers the data in background and stores it on the application server (which some people AL11, which it isn't. AL11 is just a way of looking at files stored on the application server) in a file share.

[nagaraosunkara]

Hi Matthew,

As mentioned, due to security constraints we can't give access to business users for AL11.

Right now, we are going with the compression logic as it is working as per expectations.

Thanks & regards,

Naga

[matt]

You are not giving business users access to AL11. AL11 is a transaction that allows you to view files on the application server.

You can create a file share on another server, and give the application server access to it. You write the files there, the users download. The users have no access to the application server file system from their PCs. Just the file share.

In this solution, the users have access to a shared drive somewhere on your network that contains their files.

[nagaraosunkara]

Can you please share the steps that are required for writing file to another server?

does creating another server, will lead to a cost overhead?

[matt]

Sure, if you add another server, it's going to cost whatever a server costs. You could use an existing server if one is available.

As for the how to set up a file share on another server, talk to your it people. From your perspective, it's just another folder you'd be able to view from AL11. It just isn't physically stored on the application server.

[nagaraosunkara]

Thanks Matthew for the info.

We will go ahead with the compression logic, as it works for us without much efforts (Able to download 2 Million records in five minutes, and the zipped files taking around 80MB space).