performance issue with select query and for all entries.

[Former Member]

hi,

i have a report to be performance tuned.

the database table has around 20 million entries and 25 fields.

so, the report fetches the distinct values of two fields using one select query.

so, the first select query fetches around 150 entries from the table for 2 fields.

then it applies some logic and eliminates some entries and makes entries around 80-90...

and then it again applies the select query on the same table using for all entries applied on the internal table with 80-90 entries...

in short,

it accesses the same database table twice.

so, i tried to get the database table in internal table and apply the logic on internal table and delete the unwanted entries.. but it gave me memory dump, and it wont take that huge amount of data into abap memory...

is around 80-90 entries too much for using "for all entries"?

the logic that is applied to eliminate the entries from internal table is too long, and hence cannot be converted into where clause to convert it into single select..

i really cant find the way out...

please help.

[Former Member]

80 to 90 entries is absolutely not too many entries to use FOR ALL ENTRIES.

I would look at whether or not the key is being used effectively in both SELECTs and how much data is being returned.

Rob

[Former Member]

thank you for replying..

yes, you are absolutely right, primary key is not used..

i asked customer for addition of the primary key (that was basically to reduce the number of entries taken into internal table ) but could not get the approval because they said, its not known by many..

if i had to use primary key, i had to make it obligatory to make it effective + had to put some restrictions on the difference..

but it somehow could not get through.....

so, do u want to say 20 minutes is good enough time for accessing 14 million (testing data, production is 20 million ) entries without using primary key?

[Former Member]

If it's OK with the customer, it's OK with me.

Rob

[Former Member]

Chinmay,

Even though you tried to ask the question with detailed explanation, unfortunately it is still not clear.

It is perfectly fine to access the same database twice. If that is working for you, I don't think there is any need to change the logic. As Rob mentioned, 80 or 8000 records is not a problem in "for all entries" clause.

>

> so, i tried to get the database table in internal table and apply the logic on internal table and delete the unwanted entries.. but it gave me memory dump, and it wont take that huge amount of data into abap memory...

>

It is not clear what you tried to do here. Did you try to bring all 20 million records into an internal table? That will certainly cause the program to short dump with memory shortage.

> the logic that is applied to eliminate the entries from internal table is too long, and hence cannot be converted into where clause to convert it into single select..

>

That is fine. Actually, it is better (performance wise) to do much of the work in ABAP than writing a complex WHERE clause that might bog down the database.

[Former Member]

thanks for replying ...

yea, i thought one select might give me better performance... instead of two database accesses...

and yes, i actually tried that..

thanx for ur suggestions