Skip to Content
author's profile photo Former Member
Former Member

select --endselect v/s select

when you use select ---endselect,is the complete data

read in to the user memory,then the data is read by looping through the memory.

Add comment
10|10000 characters needed characters exceeded

7 Answers

  • Posted on Jul 19, 2004 at 04:36 AM

    Yes Kaushik..you are right..Database will not be reached for each record.

    See the following example.

    Say, w_vbeln has '100' in it.

    select * from vbap where vbeln eq w_vbeln.

    clear w_vbeln.

    endselect.

    Even when you clear the contents of w_vbeln in the select..endselect..you get all the items of the sales order.

    -Srinivas.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 19, 2004 at 07:17 AM

    But, anyway, SELECT INTO TABLE

    always works faster then SELECT ... ENDSELECT.

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 19, 2004 at 08:36 AM

    Correct description:

    Each Open SQL statement is synonymous with one access to the database. This applies in particularly also to SELECT statements that is ended by ENDSELECT. Data to be read and to be written is transported in packages between the database server and the application server. You can configure the size of the packages using profile parameters (for example, the standard value for Oracle is 65 KB).

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 20, 2004 at 04:41 AM

    Does this mean,all the data is read in to the user memory

    in the form of packets and then looped through

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 21, 2004 at 06:54 AM

    Hi Srinivas,

    According to your example:

    Say, w_vbeln has '100' in it.

    select * from vbap where vbeln eq w_vbeln.

    clear w_vbeln.

    endselect.

    This only works when you declare a TABLES : VBAP statement at the beginning of the report. What this does is that it creates a runtime structure of type VBAP that is the reason when you do a clear it clears the data in the structure and not from the database. But as i assume when you do a select endselect the command loops and accesses the database each time.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Steven De Saeger

      Hi Steven,

      The table that I have used is not buffered. So I believe it proves that data is fetched in a single chunk.

      Will be really helpful if Horst can clarify his point whether, when he means chunks all the chunks are fetched once, and 1 chunk after the other (65KB).

      Regards,

      Pavan

  • Posted on Jul 26, 2004 at 07:48 PM

    Hi

    Here are the analysis results (just to inform):

    1. of records in ZTEST: 5

    Case 1: ...

    _____SELECT * FROM ztest .

    _____ENDSELECT .

    Case 2: ...

    _____SELECT * FROM ztest INTO TABLE gt_test .

    ==>

    ________Open Cursor #___Fetch #______Close Cursor #

    ___________________________________________________

    Case 1:______1____________6_______________1

    Case 2:______1____________1_______________1

    For all these 3 activities, the call type is 'DB' and filter group is 'OpenS' (OpenSQL).

    *--Serdar

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 06, 2004 at 08:57 PM

    Hi everyone,

    Remember that the SELECT statement is translated by the DB Interface into the native SQL of the underlying db. The DB Interface is really just another program that runs on the application server. The db returns as many records as fits into a packet to the DB Interface program, which in turn passes the information back to your program based on how you asked it to be passed (one at a time SELECT . . . ENDSELECT or en masse SELECT . . . INTO TABLE . . . ). So, in a sense, there is a buffering going on and SELECT . . . ENDSELECT is not causing the system to go to the db for each and every record.

    The technical settings of the table in the DDIC is used by the DB Interface to decide whether it needs to translate the Open SQL into Native SQL or whether the data already exists in the shared buffer of the application server, so is for performance gain.

    SELECT . . . BYPASSING BUFFER is telling the DB Interface that it must translate to Native SQL to retrieve the data (because you are saying you always want the latest data). This incurs a performance hit, especially for many records.

    Thus, in deciding between SELECT . . . ENDSELECT and SELECT . . . INTO TABLE, you are really deciding between individual record processing (i.e., a work area capable of holding only one record at a time, for example, to WRITE out) or bringing all records into an internal table for further processing (calculations, further manipulations, BEFORE output).

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      correction on key fields order in selects,

      if key fields are used in where clause

      it looks like that reverse order of keys works

      faster:

      select ... where key2 =.. and key1=..

      is faster than

      select ... where key1 =.. and key2=..

      maybe because it

      processed from right to left 😊