Optimizing a Select Statement

[Former Member]

Hi,

I have recently been asked to generate a program that reports of payroll postings to FI. This involves creating a giant select statement from the ppoix table to gather all the postings. My select statement is as follows:

SELECT pernr "EE Number

seqno "Sequential number

actsign "Indicator: Status of record

runid "Number of posting run

postnum "Number

tslin "Line number of data transfer

lgart "Wage Type

betrg "Amount

• waers "Currency

• anzhl "Number

• meins "Base unit of measure

spprc "Special processing of posting items

• momag "Transfer to FI/CO:EE grouping for acct determi

• komok "Transfer to FI/CO: Symbolic account

• mcode "Matchcode search term

• koart "Account assignment type

• auart "Expenditure type

• nofin "Indicator: Expenditure type is not funded

INTO CORRESPONDING FIELDS OF TABLE i_ppoix

FROM ppoix

FOR ALL ENTRIES IN run_doc_xref

WHERE runid = run_doc_xref-runid

AND tslin = run_doc_xref-linum

AND spprc <> 'A'

AND lgart IN s_lgart

AND pernr in s_pernr.

where s_pernr is a select option that holds personnel nummbers and s_lgart is a select option that holds wagetypes. This statement works fine for a certain amount of personnel numbers and a certain amount of wagetypes, but once you exceed a certain limit the Database does not allow you to perform a select statement this large. Is there a better way to perform such a large select such as this one) ie: FM, or some other method I am not aware of. This select statement comes from the standard SAP delivered cost center admin report and this report dumps as well when too much data is passed to it.

any ideas would be much appreciated.

thanks.

[Former Member · Accepted solution]

You can also try using CURSOR option (OPEN/FETCH/ CLOSE)... It will help...

Regards

Anjaiah

[Former Member · Accepted solution]

I will investigate this.

thanks.

[Former Member · Accepted solution]

Hi,

as for the cursor option, do you know where I can get documentation on this method, thanks.

[Former Member · Accepted solution]

The problem here is with the select-options.

For a select statement, you cannot have more that certain amount of data.

The problem with your select becomes complex because of the FOR ALL ENTRIES in and the huge s_pernr and the 40 million records :(.

I am guessing that the s_lgart will be small.

How many entries do you have in internal table "run_doc_xref"?

If there are not that many, then I would suggest this:

TYPES:
  BEGIN OF ty_temp_ppoix,
   pernr   TYPE ppoix-pernr,
   lgart   TYPE ppoix-lgart,
   seqno   TYPE ppoix-seqno,
   actsign TYPE ppoix-actsign,
   runid   TYPE ppoix-runid,
   postnum TYPE ppoix-postnum,
   tslin   TYPE ppoix-tslin,
   betrg   TYPE ppoix-betrg,
   spprc   TYPE ppoix-spprc,
  END OF ty_temp_ppoix.
DATA:
 i_temp_ppoix TYPE SORTED TABLE OF ty_temp_ppoix
   WITH NON-UNIQUE KEY pernr lgart
   INITIAL SIZE 0
   WITH HEADER LINE.
DATA:
  v_pernr_lines TYPE sy-tabix,
  v_lgart_lines TYPE sy-tabix.

IF NOT run_doc_xref[] IS INITIAL.
  DESCRIBE TABLE s_pernr LINES v_pernr_lines.
  DESCRIBE TABLE s_lgart LINES v_lgart_lines.
  IF v_pernr_lines GT 800 OR
     v_lgart_lines GT 800.
* There is an index on runid and tslin. This should be ok
* ( still bad because of the huge table 😞  )
    SELECT pernr lgart seqno actsign runid postnum tslin betrg spprc
* Selecting into sorted TEMP table here
      INTO TABLE i_temp_ppoix
      FROM ppoix
      FOR ALL ENTRIES IN run_doc_xref
      WHERE runid = run_doc_xref-runid
      AND   tslin = run_doc_xref-linum
      AND   spprc <> 'A'.

* The sorted table should make the delete faster
    DELETE i_temp_ppoix WHERE NOT pernr IN s_pernr
                        AND   NOT lgart IN s_lgart.

* Now populate the actual target
    LOOP AT i_temp_ppoix.
      MOVE: i_temp_ppoix-pernr TO i_ppoix-pernr.
*  and the rest of the fields
      APPEND i_ppoix.
      DELETE i_temp_ppoix.
    ENDLOOP.

  ELSE.

    SELECT pernr seqno actsign runid postnum tslin lgart betrg spprc
* Selecting into your ACTUAL target here
      INTO TABLE i_ppoix
      FROM ppoix
      FOR ALL ENTRIES IN run_doc_xref
      WHERE runid = run_doc_xref-runid
      AND   tslin = run_doc_xref-linum
      AND   spprc <> 'A'
      AND   pernr IN s_pernr
      AND   lgart IN s_lgart.

  ENDIF.
ELSE.
* Error message because of no entries in run_doc_xref?
* Please answer this so a new solution can be implemented here
* if it is NOT an error
ENDIF.

Hope this helps.

Regards,

-Ramesh

[Former Member · Accepted solution]

Here is something for the CURSOR method.

You will still have the same problem since there are too many entries in s_pernr.

DATA:
  V_CURSOR TYPE CURSOR.

OPEN CURSOR WITH HOLD V_CURSOR FOR
      <Your Select statement here>.

DO.
  FETCH NEXT CURSOR S_CURSOR
    <INTO or APPENDING INTERNAL TABLE as you want it>
* INTO will delete existing entries in targer ITAB
    PACKAGE SIZE 10000.  "This will bring 10,000 recs at
                         "a time
  IF SY-SUBRC NE 0.
    CLOSE CURSOR V_CURSOR.
    EXIT.   " Exit the DO loop
  ENDIF.
* Processing . . . .
* Don't use statement that can do a COMMIT or ROLLBACK.
* A COMMIT or ROLLBACK will close the cursor
ENDDO.

Good luck.

Cheers,

-Ramesh

[Former Member]

This is not efficient, but it should work:

REPORT ztest NO STANDARD PAGE HEADING LINE-SIZE 255.

TABLES:         ppoix.
SELECT-OPTIONS: s_pernr FOR ppoix-pernr.
RANGES:         r_pernr FOR ppoix-pernr.
DATA:           no_lines LIKE sy-tabix.

DESCRIBE TABLE s_pernr LINES no_lines.

* Use 1 for testing. Actual number depends on system.
IF no_lines > 1.
  r_pernr[] = s_pernr[].
  REFRESH s_pernr.
  CLEAR   s_pernr.
ELSE.
  REFRESH r_pernr.
  CLEAR   r_pernr.
ENDIF.

SELECT * FROM ppoix
  WHERE pernr IN s_pernr.
  CHECK ppoix-pernr IN r_pernr.
ENDSELECT.

Rob

[Former Member]

Hi,

Is your problem resolved?

If any of the suggestions helped, please award points and close the question.

Regards,

-Ramesh

[Former Member]

Jamie,

Hope these pointers help:

1.) Please don't use INTO CORRESPONDING FIELDS OF TABLE

DEFINE the table with the exact fields and use

INTO TABLE

2.) Always check if internal table "run_doc_xref" has

any values in it before the select. If this internal

table is blank, then all the records from table PPOIX

will be read.

Can you please post:

1.) How many records do you have in table PPOIX in your

production system?

2.) How are the select options being entered?

As ranges or as individual values?

Thanks,

-Ramesh

[Former Member]

Hi,

They are being entered as single values.

I would say there are a good 40,000,000 entries in the PPOIX table.

thanks.

[Former Member]

I will try your approach by removing the into corresponding.

thanks.

[guillaume-hrc]

Did you try with the logical database approach ?

[Former Member]

I am not aware of any logical database approaches to access this table.

thanks.

[Former Member]

Try make 2 queries by spliting the select option values into half,

ex.

say s_pernr has low 1000 and high 5000

so process the query for the first 2500 (1000 to 2500) records and write a second query for the remaining 2500 records (2501 till 5000 ).

Second option is to put more conditions in the where clause

Hope this helps

Thanks

Advait.

[Former Member]

Hi,

I did break it up into two and it still dumped.

thanks.