Query Optimisation

[Former Member]

Hi,

We have the following query on REGUP table. This is a cluster table.

SELECT sgtxt FROM regup

INTO l_v_sgtxt

UP TO 1 ROWS

WHERE bukrs = l_v_bukrs1

AND vblnr = l_v_docno1

AND xblnr = l_v_docnummr1

AND gjahr = sy-datum+0(4).

ENDSELECT.

We are facing a lot of issues relating to performance because of the above query. We have milllions of records in the REGUP table and would like to improve performance.

Any solution to improve performance would be greatly appreciated.

Thanks in advance.

Mick

[former_member156446]

Hi MIck if you can get the bukrs, vblnr,xblnr or some of it into another table ( itab_for ) you can write a for all entires in using that table..

data: begin of itab,
           sgtxt type regup-sgtxt,
        end of itab.

SELECT sgtxt FROM regup
INTO itab
for all entires in itab_for
WHERE bukrs in itab_for-bukrs
AND vblnr in itab_for-docno
AND xblnr in itab_for-docnummr
AND gjahr = sy-datum+0(4).

[Former Member]

What i recommend is if the query gonna exectue many times in your code then i dump the whole table regup into a local table and then i gonna perform READ operation with BINARY SEARCH.

[Former Member]

Hi All.

REGUP is a cluster table. So not sure whether by adding just the clause "for all entries" will really improve the performance.

Would welcome all suggestions as we are really stuck on this and this is hampering business ina major way.

Regards,

Mick

[Former Member]

The fact that REGUP is a cluster table makes no difference. Your problem is that you are not using the index. Using FOR ALL ENTRIES may help somewhat, but will not solve your problem. Did you try my earlier suggestion?

Rob

[Former Member]

Thanks Rob.

Will try and revert back, as also need to check not only performance but also the data that is being populated in the payment run after we change the logic.

Regards,

Mick

[Former Member]

Not sure if this will work or not but you can give it a try:

Instead of going to REGUP with BUKRS and VBLNR, go to PAYR with these fields instead. There is a secondary index on these fields. From PAYR, you can get LAUFD, LAUFI and probably other primary keys for REGUP.

Maybe something like:

SELECT * FROM  payr
  INTO TABLE payr_int
       WHERE  zbukr  = l_v_bukrs1
       AND    vblnr  = l_v_docno1
       AND    gjahr  = sy-datum+0(4).

LOOP AT payr_int.

  SELECT sgtxt FROM regup
    INTO l_v_sgtxt
    UP TO 1 ROWS
    WHERE laufd = payr_int-laufd
      AND laufi = payr_int-laufi
      AND vblnr = payr_int-vblnr
      AND xblnr = l_v_docnummr1
      AND gjahr = sy-datum+0(4).     
*      
* Do some stuff      
*      
  ENDSELECT.

ENDLOOP.

You can probably get rid of the LOOP and ENDSELECT, but I don't know the rest of your logic so I just left it.

Let me know if this works or not.

Rob

Edited by: Rob Burbank on Feb 7, 2008 6:13 PM