topic Re: Multiple Select Statments or Internal Table in Application Development and Automation Discussions

Multiple Select Statments or Internal Table

Former Member — Thu, 01 Jul 2010 17:39:19 GMT

I am in the process of developing a new cube with a very complex transformation to load the cube.

The ABAP code that I am using has to do many select statements to multiple ODS structures as well as read from the cube being loaded to gather the previous week's data.

My problem is time, I am loading on average 4.5 million records each week when I load this cube. On a similar cube with similar calculations but not as many I am getting run times of about 8 hours for completion. This is also on a DB2 database.

My question is this, is it faster to load an internal table for each data package that would be roughly 1.5 million records for the internal table or to do many select statements that drill down to a specific level that would identify a single record in the ODS but do this 1.5 million times.

So should I load an internal table and loop through that or should I do 1.5 million select statements that grab just the information needed?

We are looking to optimize this process because it currently will fail with timeouts because it is taking so long to run.

Any help?

Re: Multiple Select Statments or Internal Table

Rui_Dantas — Thu, 01 Jul 2010 17:44:56 GMT

I would definitely go with the one select, multiple reads approach.

It is a best practice in BW transformations to read into an internal table in the start routine, and then read the internal table in each rule, and that's how I have achieved the best results. Just make sure that you have a fast access to the internal table, so define it as SORTED or HASHED.

Rui Dantas

Re: Multiple Select Statments or Internal Table

Former Member — Thu, 01 Jul 2010 17:47:52 GMT

So you think it would be best to define a Global internal table that is simply read by each of the parrellel data packages?

If we decide to go this route, what if we delete the line from the table after it has been read from the table as it will never be needed by two records. Would it be faster to leave it in and delete all at the end or would it be faster to delete each record, not sure if the table would "resort" each time or not.

Re: Multiple Select Statments or Internal Table

Rui_Dantas — Thu, 01 Jul 2010 17:55:15 GMT

If you can define a unique key, use a hashed table: the access time will be constant, so I wouldn't bother deleting the used records (though I have never actually tried it; you can always test it and post the results... )

Re: Multiple Select Statments or Internal Table

former_member193284 — Sat, 18 Sep 2010 21:26:32 GMT

Hello,

I would suggest to use field symbols as it doesnt occupy memory and are faster. Also use read statement with binary search where ever applicable.

while firing select queries ensure your fetch only required fields.

Also avoid into corresponding clause in your select statements

Ensure you either have primary index or secondary index for your select queries ( Do run ST05 to find out which query might take time )

Ensure you run SE30 runtime analysis while coding your program. Might help to reduce time.

Also if possible create views and fetch data from the views would help for faster results.

Hope this helps you.

Sumit