Performance issue - smart response required

[Former Member]

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.

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

Your answers will be rewarded.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

thanks,

[Former Member]

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

[Former Member]

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"?

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

<b>Reward points If useful</b>

[Former Member]

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

[Former Member]

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.