topic Re: Performance issue - smart response required in Application Development and Automation Discussions

Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:12:24 GMT

Hi Guyz,

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.

This query is taking 2.5 mins to execute, I have to increase its performance.

FYI

itab has 2677 number of records

and final data in table itab1 has 3536 number of enteries. Its taking huge time, 2.5 mins to execute this portion. How can I increase the performance of this query.

Pls it's urgent.

Note: If google can fetch the data from around the globe in fractoin of seconds, then at least it's not impossible.

Your answers will be rewarded.

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:18:48 GMT

Good afternoon, Sal.

Well, in my opinion, you should replace the '*' with the fields you need (and only the ones you need).

Thats a start.

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:21:46 GMT


SELECT FIELD1 FIELD2 FIELD3  
FROM regup
INTO 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.

Just replace the * with the needed fields....

Greetings,

Blag.

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:26:31 GMT

I'm already using the fields, I have not typed them here.

thanks,

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:30:29 GMT

My assumptions are :

1. REGUP is the cluster table,so you can't create index - this is ruled out

2. are you using sort command before sql query - it will give liitle bit good performance

3. Use primary key's in where clause - this will have major performance

4. Declare internal table without header line - it will give little bit good performance

Thanks

Seshu

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:32:34 GMT

These is the regup table primary key:

The ones you are using start with *

MANDT

LAUFD

LAUFI

XVORL

ZBUKR

*LIFNR

KUNNR

EMPFG

VBLNR

*BUKRS

*BELNR

*GJAHR

BUZEI

Maybe you should try to use the most of the primary key that you can.

How do you fill "itab"?

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 19:43:50 GMT

I don't think there is much you can do other than add LAUFD to the selection. That will allow it to use at least part of the index.

Are there any index tables that you can use that have the same information you are looking for?

Rob

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 20:40:14 GMT

My report is taking 98.9% of database load, when i check it from SE30 transaction n taking so much time to execute due to this query.

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 20:53:09 GMT

Its not possible to use all primary key's in where clause, becuase i dont have the requried data. Any other solution...

Is there any other standard table from where i can select the same data? I know about REGUP_CORE, but it has not all the data which i require.

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 21:04:31 GMT

Try this ...remove from the WHERE condition 'AND vblnr NE space.' and fetch the data into ITAB1 ..then sort ITAB1 with VBLNR and delete from ITAB1 where VBLNR = Space.

Reward points If useful

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 22:16:30 GMT

Saket I have used this, but no performance increase...

Re: Performance issue - smart response required

Former Member — Tue, 26 Jun 2007 23:45:46 GMT

It would be useful to know what business requirement this is trying to meet - it looks like you are trying to trawl through the payment run line items table for a series of document numbers, but you may already have some of this same data available from BKPF & BSEG, or at least more quickly accessible by travelling through these tables first.

For example, I'm not sure if it's site configured or standard, but I have seen the LAUFD and LAUFI value concatenated into the BKPF-BKTXT field on the paying (clearing) document, e.g. "20070205-123456", which would mean you could then get to the REGUP data much more efficiently.