Performance issue with select statement

[Former Member]

Hi Experts,

I have a select statement with inner join and for all entries.

Eg : it_data has 50,000 records.

while sy-subrc eq 0.

select a~field1 a~field2 a~field3 a~field4

          b~field1 b~field2 b~field3 into table it_temp

         from Zxxxxx as a inner join Zyyyyyy as b

        on a~field1 eq b~field1

for all entries in it_data where a~field1 in s_field1

and a~field2 in s_field2 and b~field3 = it_data-field5.

endwhile.

Note: Zxxxxx has 10000 records and Zyyyyyy has 4000 records.

Problem : taking long time for execution. its a performance issue.

3doubts: 1) Can I use for all entries with inner joins with huge data.

             2) I want to replace for all entries with Ranges..............with huge data...............is it work.

             3) What about While.....endwhile loop............?

Please Suggest me.

Thanks in advance.

[ceedee666]

Hi,

where do the 50000 entries in your itab come from? Most likely also a select statement.

if this is the case why don't you try to use a join of the three tables involved instead of the join together with for all entries?

Christian

[Former Member]

Hi Nagireddy.

1. I think if you are using table keys for Zxxxxx and Zyyyyyy, it's ok.

2. You can insert 50,000 in a range. the limit of the database is approximately 5,000.

3. I do not understand to make a selection within a loop. also if the select returns data you enter an infinite loop. As I see that's the problem.

Regards,

Genaro.

[NooruBohra]

Hi,

Do not use WHILE----ENDWHILE Instead you can use IF---ENDIF

WHILE--ENDWHILE will be an endless loop if the query executed successfully and fetch some records.

Regards,

Nooruddin Bohra

[former_member201275]

No idea why you have this WHILE statement here!?

[nikolayevstigneev]

Hi, Nagireddy.

Have you tried tracing your SELECT statement in ST05? You haven't specified the structure of your tables, probably you are not using key fields or index fields of your Z tables.

Concerning your questions:

1. If you are joining tables using key fields, it's ok. But you can try joining to Zxxxxx table instead of using FOR ALL ENTRIES construction as Christian suggested.

2. Replacing FAE with Range is not a good idea in case there are many records in it_data (and you mentioned that it may have 50 000 entries, it's quite enough to get a dump in the above query).

3. I didn't get what was the need in WHILE/ENDWHILE loop.

And one more suggestion: table Zyyyyyy has 4 000 records, it_data may have 50 000 entries. In case you are using key fields in SELECT statement, have you tried sorting it_data by field5 and removing adjacent duplicates comparing field5 from it_data before the SELECT?

P.S. I didn't mention compulsory check that it_data is not initial, hoping it has been done before the SELECT.

[Former Member]

Hi,

First check the secondary Indexes of the tables if the index is not there create secondary index and try.

i suggest with for all entries using in inner join better to avoid.

[PeterJonker]

It will  definitely have a performance issue: IT WILL RUN FOREVER !!

WHILE .

ENDWHILE.

and no exit condition. If your select has a result it will continue to do the same select over and over again.

You can call that a performance problem, I would call it different, but I will not mention it on this forum.