topic package size in Application Development and Automation Discussions

package size

Former Member — Sat, 11 Aug 2007 04:51:23 GMT

Hi all,

i want to use package size for select query to extract 3 crore records from a table,

wat package i should give, how many records we can fetch by package size1.

it is taking too much time for processing and finall roll backing wat can i do to avoid this.

thanks

siva

Re: package size

Former Member — Sat, 11 Aug 2007 05:14:23 GMT

hI,

If the PACKAGE SIZE addition is not used, all lines of the result set are inserted in the internal table itab and the ENDSELECT statement must not be specified after SELECT.

If you specify the PACKAGE SIZE addition, all lines of the result set for SELECT are processed in a loop, which must be closed with ENDSELECT. The lines are inserted in the internal table itab in packages of n lines. n must be a type i data object that contains the number of lines. If the value of n is smaller than 0, an exception that cannot be handled occurs. If n is equal to 0, all lines of the result set are inserted in the internal table itab. If used in the FETCH statement, n lines are extracted from the current cursor position.

Rewards point if it useful.

Re: package size

Former Member — Sat, 11 Aug 2007 05:41:04 GMT

PACKAGE SIZE on SELECT statements is most useful when considering large data volumes.

Memory size example

PACKAGE SIZE is useful when you want to limit the size of internal tables returned from a SELECT statement. SAP will return the number of records specified in PACKAGE SIZE. Consider the following example which with PACKAGE SIZE uses less memory on the application server.

Straightforward Select

SELECT psphi .......
  INTO TABLE it_prps
  FROM prps
  WHERE psphi IN s_psphi.

IF sy-subrc EQ 0.
  LOOP AT it_prps.
     ....
  ENDLOOP.
ELSE.
  .... no records found ....
ENDIF.

Select with PACKAGE SIZE

SELECT psphi .....
  INTO TABLE it_prps PACKAGE SIZE 1000
  FROM prps
  WHERE psphi IN s_psphi.

  LOOP AT it_prps.
    .…
  ENDLOOP.

ENDSELECT.

IF sy-subrc NE 0.
  .… no records found ….
ENDIF.

In the first example, all records are moved to table it_prps that adhere to the selection criteria. This is a good method if all the records are required, say, for sorting.

In the second example, only up to 1000 records are moved into it_prps at a time. Note the ENDSELECT is now required for the SELECT. The internal table which contains up to 1000 records must be processed within the SELECT/ENDSELECT loop, before the next 1000 is presented into it_prps. This method saves memory on the application server, but because not all the records are presented to it_prps at one time, is not useful for a full sort – you can use the APPENDING TABLE option in these instances.

Andrew