topic Performance issue in query in Application Development and Automation Discussions

Performance issue in query

Former Member — Fri, 22 Jun 2007 19:26:49 GMT

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

Re: Performance issue in query

RichHeilman — Fri, 22 Jun 2007 19:32:10 GMT

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

REgards

RIch HEilman

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 19:32:23 GMT

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

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 20:40:30 GMT

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,

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 20:45:40 GMT

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

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 20:55:55 GMT

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

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 21:20:53 GMT

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...

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 21:25:45 GMT

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

Re: Performance issue in query

Former Member — Fri, 22 Jun 2007 21:29:43 GMT

Yes

Re: Performance issue in query

Former Member — Mon, 25 Jun 2007 19:53:14 GMT

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