Performance Problem

[Former Member]

Hi Experts,

I am getting more than 2 million records of material data. Before I perform further operations with the data I have to validate the material number.

What would be the better approach?

like

1. Taking all the material numbers from MARA table and reading the table with the material number given in the file... if that is the case then does the internal table will hold the whole materail data? does it blow out?

2. once i take all the records into one internal table from the file if i loop through that internal table and verify each and every material number using the select single......? If it is the possibility then select inside loop giving me the poor performance.

Can anyone give me a good solution for this problem.

I really appreciate your anticipation.

[Former Member]

How much memory do you have?

Thanks,

Barjinder Singh.

[former_member156446]

hi nitesh

you are saying material from file is it.. so my vote will be for:

when ever you are getting the file and splitting at the delimitor and saving into the table do the validation there...

example:

split p_catsdata at '|' into  f1 f2 f3 f4 .... fn.

perform validate using f2.

append to table.
************************
form....
select single matnr from mara
where matnr = f2.
endform.

[Former Member]

Jack,

When I am using this select single it is giving me poor performance.

Is there any alternate to this?

[former_member156446]

hmm.. if not select single...

you need to fetch records into a temp mara.. and sort table and read it..with Binary search.

select * form mara into it_mara.
if sy-subrc eq 0.
sort mara by matnr.
endif.

loop at itab into wa_itab.
read table it_mara with key matnr = wa_itab-matnr
                                         Binary search.

if sy-subrc eq 0.
.........
endif.
endloop.

[Former Member]

Reading it at once from mara seems to be better option..

But as you said u have more than 2 million entries, it can cause dump coz. internal table can hold upto 2gb data.

I suggest go for package size option in select statement.

SELECT *
FROM <table>
INTO TABLE itab
PACKAGE SIZE <n>.

IF sy-subrc EQ 0.
\*" Process the n records
ENDIF.

ENDSELECT.

G@urav.

[Former Member]

Hi, i think you need to extract and process your data in batches.

Best way to do this, I think, is using manual FETCH commands rather than selects.

[Former Member]

Hi Nitesha,

As You told You have a internal table which is already having 2 million record right ..then whey are You placing select single inside loop..U should not do that and U should not Use Select and Endselect in loop too.

1. You need to do is, select only the required fields from DB table with more key conditions.

2. Then if the values(data) having line items You need to loop the internal table or if the table contains header details use read table with as much as conditions as per Your requirements.

3. If You need data other than MARA use for all entries and fetch the data in to another internal table.

You need to declare types: begin...

wht ever fields You need,

end...

You should not Use Select * too...

if you do't do follow few steps ... You will definately Performance issue.

Regards,

sg

Edited by: Suneel Kumar Gopisetty on Apr 29, 2008 4:50 AM

[Former Member]

Hi,

Use "FOR ALL ENTRIES" in the select statement.

For example :

itab1 -> Contains all the records from your input file.

itab2 -> Contains only valid material numbers in your input file.

select matnr

from mara

into table itab2

for all entries in itab1

where matnr = itab1-matnr.

Reward if its useful.

Regards,

Raghu.