Performance issue in query

[Former Member]

Hi,

I have this query in my program, it’s taking so much time to execute. Can you pls look at this query and tell me whats wrong with it. ITAB which is used in for all enteries has around 3000 records.

SELECT * FROM regup

INTO CORRESPONDING FIELDS OF TABLE itab1

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs

AND belnr = itab-belnr

AND lifnr = itab-lifnr

AND gjahr = itab-gjahr

AND vblnr NE space.

Can you tell me how I can optimize this?

Your answers will be rewarded.

Thanks

[RichHeilman]

Make sure that you sort ITAB by BUKRS BELNR LIFNR and GJAHR before doing this select.

REgards

RIch HEilman

[Former Member]

Check with database -> how many records it has

I see ur code - the where condition does not have all primary key condition

if you have huge data then you can't decrease ..

Thanks

Seshu

[Former Member]

Rich:

I have sorted the itab, but no performance gain. REGUP is cluster table, is it becuase of this table.

Seshu:

This is what i require in where clause, I don't need all primary key's. Its taking some time 5 minutes on small selection.

Thanks,

[Former Member]

It is not easy to decrease processing unless you have less data.

even you can not create secondary index since it is cluster table.

Thanks

Seshu

[Former Member]

In addition to sorting the itab as Rich suggested, you should then delete adjacent duplicates from itab comparing bukrs belnr lifnr gjahr. This will create the smallest possible itab for selecting. if you need this table for other processing, create another work table and use it instead.

But since you are not using an index, it will still take time.

Rob

[Former Member]

But there is no duplicate records in this itab. so no performance gain. I have executed se30 runtime analysis, it's using 95.2% of database. Any suggestions...

[Former Member]

Are you sure you want all fields from REGUP? Why select * ?

[Former Member]

Yes

[Former Member]

Is there any other way of calling this cluster table, so performance can be increased.

My report is taking 98.9% of database load, when i check it from SE30 transaction n taking so much time to execute, what can be the possible reason, so i decrease this database load.

Your answer will be rewarded