topic Package Size in Select Query in Application Development and Automation Discussions

Package Size in Select Query

kiran_k8 — Tue, 11 Dec 2007 05:37:52 GMT

Hi Folks,

SELECT KSCHL

VKORG

VTWEG

MATNR

DATBI

DATAB

KNUMH

FROM A304

INTO TABLE Itab2

WHERE KFRST = ' '.

I want to replace the above select statement by the following.

SELECT KSCHL

VKORG

VTWEG

MATNR

DATBI

DATAB

KNUMH

FROM A304 APPENDING CORRESPONDING FIELDS OF ITAB2 <b>PACKAGE SIZE 1000</b>

WHERE KFRST = ' '.

On what basis is the figure in the package size be defined.I mean on what basis we can decide that no.1000?Will the above select statement improve performance as desired?

Thanks,

K.Kiran.

Message was edited by:

Kiran K

Message was edited by:

Kiran K

Re: Package Size in Select Query

former_member386202 — Tue, 11 Dec 2007 05:41:32 GMT

Hi,

... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

Effect

If the result set consists of multiple lines, an internal table itab of any table type can be specified after INTO or APPENDING. The row type of the internal table must meet the prerequisites.

The result set is inserted into the internal table itab line-by-line; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized before the first line is inserted. Previous lines remain intact if APPENDING is used.

Before any assignment of a line of the result set, an initial row of the internal table itab is created and the line of the result set is assigned to this row. When assigning a line of the result set to a row of the internal table with or without CORRESPONDING FIELDS, the same rules apply as when assigning to an individual work area wa (see above).

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.

If INTO is used, the internal table is initialized before each insertion and, in the SELECT loop, it only contains the lines of the current package. If APPENDING is used, a further package is added to the existing rows of the internal table for each SELECT loop or for each extraction using FETCH.

After ENDSELECT, the content of itab is not defined if INTO is used - that is, the table can either contain the lines of the last package or it can be initial. If APPENDING is used, the content of itab retains the state of the last loop pass.

Notes

In the case of an internal table with a unique key, an exception that cannot be handled occurs if an attempt is made to create a duplicate entry.

If the addition PACKAGE SIZE is specified together with FOR ALL ENTRIES, it is not passed to the database system, but is applied to the result set on the application server, after all selected rows have been read.

Example

In this example, all columns of the result set are read into an internal table whose row type is a nested structure with the same construction as the result set. Note that in practice, the column carrid exists twice in the result set with the same content and, after the assignment, this content is stored redundantly in the columns struc1-carrid and struc2-carrid of the internal table.

DATA: BEGIN OF wa,

struc1 TYPE scarr,

struc2 TYPE spfli,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY table_line.

SELECT *

FROM scarr

INNER JOIN spfli ON scarr_{carrid = spfli}carrid

INTO TABLE itab.

LOOP AT itab INTO wa.

WRITE: / wa-struc1-carrid,

wa-struc1-carrname,

wa-struc2-connid.

ENDLOOP.

Regards,

Prashant

Re: Package Size in Select Query

Former Member — Tue, 11 Dec 2007 07:16:06 GMT

PACKAGE SIZE n

Works like ... INTO wa, except that the selected data is not placed in the internal table itab line by line, but in packets of n lines. The old contents of itab are overwritten.

n <= 0 causes a runtime error.

Internally, n is placed in a type I field. Here, the usual conversion rules apply (see MOVE).

After leaving the processing loop, the contents of the internal table itab are undefined.

If the result of the selection is a table, the data is retrieved in a processing loop introduced by SELECT and concluded by ENDSELECT. The processing passes through the loop once for each line read.

Example

Output a list of all airlines (with short description and name):

DATA: itab TYPE STANDARD TABLE OF SCARR WITH NON-UNIQUE

DEFAULT KEY INITIAL SIZE 10.

FIELD-SYMBOLS: <FS> TYPE scarr.

SELECT * INTO TABLE itab PACKAGE SIZE 20 FROM scarr.

LOOP AT itab ASSIGNING <FS>.

WRITE: / <FS>-carrid, <FS>-carrname.

ENDLOOP.

ENDSELECT.

example 2

Package size can be used if you for example only want to finish processing a limited amount of data at a time due to lack of memory.

The example below read 50 records at a time from VBAK into an internal table, and selects the corresponding entries from vbap into an internal table. Then the two internal tables can be processed, and the next 50 records from VBAk can be read. Remember to reinitialize tha tables before the next read.

REPORT z_test.

TYPES:

BEGIN OF t_vbak,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

END OF t_vbak,

BEGIN OF t_vbap,

posnr LIKE vbap-posnr,

matnr LIKE vbap-matnr,

meins LIKE vbap-meins,

END OF t_vbap,

BEGIN OF t_report,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

posnr LIKE vbap-posnr,

matnr LIKE vbap-matnr,

meins LIKE vbap-meins,

END OF t_report.

DATA:

li_vbak TYPE t_vbak OCCURS 0,

l_vbak TYPE t_vbak,

li_vbap TYPE t_vbap OCCURS 0,

l_vbap TYPE t_vbap,

li_report TYPE t_report OCCURS 0,

l_report TYPE t_report.

START-OF-SELECTION.

SELECT vbeln erdat

FROM vbak

INTO TABLE li_vbak PACKAGE SIZE 50.

SELECT posnr matnr meins

FROM vbap

INTO TABLE li_vbap

FOR ALL ENTRIES IN li_vbak

WHERE vbeln = li_vbak-vbeln.

IF sy-subrc = 0.

Now you have the two internal tables li_vbak and li_vbap filled
with data.
Do something with the data - remember to reinitialize internal
tables

ENDIF.

ENDSELECT.