topic Re: regarding SELECT statements in Application Development and Automation Discussions

regarding SELECT statements

Former Member — Thu, 20 Sep 2007 18:24:25 GMT

i m following these SELECT statements:

1. SELECT <fieldname1>

FROM <DB tablename> INTO TABLE <internal table>.

2. SELECT * FROM <DB tablename> INTO CORRESPONDING FIELDS OF TABLE

<internal table>.

3. SELECT SINGLE * FROM <DB tablename> INTO CORRESPONDING FIELDS OF <work area>.

*****************************

can any of u plz help me in this...

plz give a brief description on varoius SELECT statements we can use and their functionality and perfomance....

regards & thanks

venu

Re: regarding SELECT statements

Former Member — Thu, 20 Sep 2007 18:38:59 GMT

This is good performance1.

This will get the all records from database table

SELECT <fieldname1>

FROM <DB tablename> INTO TABLE <internal table>.

This is bad performance (Do not use into

corresponding)

This will get the all records from database table

2. SELECT * FROM <DB tablename> INTO CORRESPONDING FIELDS OF TABLE

<internal table>.

If you want to get single record from database table then use below query

3. SELECT SINGLE * FROM <DB tablename> INTO CORRESPONDING FIELDS OF <work area>.

*****************************

Do not use into corresponding,this will cause performance issue

Thanks

Seshu

Re: regarding SELECT statements

Former Member — Thu, 20 Sep 2007 19:13:38 GMT

Using INTO CORRESPONDING FIELDS OF will give a small performance hit, but not much. If you add a field into the middle of the internal table that you aren't SELECTing, INTO CORRESPONDING FIELDS OF will allow this without further program changes. I use it all the time.

Please see:

<a href="/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound">Performance - what will kill you and what will leave you with only a flesh wound</a>

Rob

Re: regarding SELECT statements

Former Member — Fri, 21 Sep 2007 05:40:26 GMT

Hello,

This is a good question. Firstly you need to be introduced to bad practices to understand the better one.

SELECT....

...

End Select.

Here the select happens one record as per the loop.

Hence to select 10 records it hits the dB 10 times to fetch the records.

Select into corresponding.

This is okay not abusive as the first.

But can be avoided.

The catch here is that once fetching is done it needs to assign it to the fields which are in the internal table as corresponding to the dB table.

This arises because the field order is not the same in the internal table and in the dB. So this can be avoided by taking care to declare the fields in the internal table in the order they appear in dB.

The most effective Select query would be:

Select field1 field2 field3

from table

into table int_tab

where conditions..

lively example :

SELECT vbeln posnr vgbel

FROM lips

INTO TABLE it_lips

WHERE vgbel = it_vbap-vbeln.

Also remember that while selecting the header data for a particular record, use Single.

SELECT SINGLE vbeln

FROM vbak

INTO v_vbeln2

WHERE vbeln EQ s_vbeln-high.

Hope this would solve all your queries.

Reward points if useful.

Thanks,

Tej..

Re: regarding SELECT statements

Former Member — Fri, 21 Sep 2007 07:09:13 GMT

Hi,

The following SELECT statements which hit the d/b and get the records and makes differnce performance wise.

SELECT....END SELECT (BAD in Performance ) :

Here the select hits the d/b one record as per the loop.

Suppose if u select 10 records it hits the dB 10 times to fetch the records.

so SELECT ENDSELECT each time it hits the database table and cause perforamnce issue bec of heavy traffiec.

Ex :

SELECT matnr mbrsh

FROM mara INTO it1 UP TO n ROWS.

ENDSELECT.

SELECT INTO CORRESPONDING FIELDS...(Not good in performance).

As per good coding standards this one is also not suggestable but it not couses that much of above statement.so it is better to be avoided.

How the assigning reocords here is that once fetching is done it needs to assign it to the fields which are in the internal table as corresponding to the dB table.

Ex:

DATA: BEGIN OF it1 OCCURS 0,

bukrs type bukrs,

matnr TYPE matnr,

mbrsh TYPE mbrsh,

belnr type belnr,

END OF it1.

SELECT matnr mbrsh

FROM mara INTO CORESPONDING FIELDS OF TABLE it1 UP TO n ROWS.

The most efficient Select query would be:

Select field1 field2 field3

from table

into table int_tab

where conditions..

lively example :

DATA: BEGIN OF it1 OCCURS 0,

matnr TYPE matnr,

mbrsh TYPE mbrsh,

END OF it1.

SELECT matnr mbrsh

FROM mara INTO TABLE it1 .

WHERE matnr eq p_matnr.

IF sy-subrc = 0.

SORT it1 BY matnr.

ENDIF.

SELECT SINGLE :

If you want select the single records like header data you can use this statement

DATA: BEGIN OF it1 ,

matnr TYPE matnr,

mbrsh TYPE mbrsh,

END OF it1.

SELECT SINGLE matnr mbrsh

FROM mara INTO it1 .

WHERE matnr eq p_matnr.

SELECT....APPENDING TABLE :

This syn use when r need to be insert the internla table wchih it contains records already ,say tbl_bsak contins initially 10 records again u need to add some records into this same internal table with differnt validations.

SELECT * FROM bsak

APPENDING TABLE tbl_bsak

WHERE bukrs IN s_bukrs

AND augdt IN s_date

AND gjahr IN r_gjahr

AND belnr IN s_belnr

AND blart IN s_blart

IF sy-subrc = 0.

SORT tbl_bsak BY bukrs gjahr belnr monat.

ENDIF.

These are some of select stataments which you can use in reports.

I am giving some of SELECT statement TIPS while fetching data form d/b

Select Statements within Loop processing is not recommended. Preferred approach is to select data into an itab and then read the itab to access specific records
Do not use Nested Selects, Selects within Loops. or SELECT...ENDSELECT
Do not use Select * unless at least 70% of fields are needed

Select only the fields you require.
Do not use INTO CORRESPONDING
Do not do Order By on non key fields
Force optimizer to use the index where possible
If primary index can not be used, look for alternate indexes or alternate index tables
Avoid Use of LIKE in the Where clause on index fields. It will force a non index read.
Avoid Use of NOT conditions in the Where clause on index fields. It will force a non index read.
Select Single MUST have the primary key fully specified in the WHERE clause. Otherwise use Select.. Up to 1 Rows.
Avoid DISTINCT see performance standards for usage
Consider filtering on the appserver rather than in a WHERE statement
SAP Recommendation on Joins - try not to exceed a 3 Table Join

When using "Select.. For all Entries". The following 4 rules MUST be followed:

o Check to make sure driver itab is not empty

o Always SORT the itab (driver table) by keys. Specify all keys used in the Where clause

o DELETE Adjacent Duplicates Comparing the keys that were sorted.

o All Primary Key Fields must be in the Select List

Rewards with points if helpful.

Regards,

Vijay

Re: regarding SELECT statements

Former Member — Fri, 21 Sep 2007 08:00:12 GMT

Try to avoid 'into corresponding', very often it is possible that you use an internal table with the same structure as the database table, then into corresponding is not necessary.

As said above into corresponding is an offset. However, if your structures are different, then into corresponding can be useful, especially if your structures can change.

into table is for many records

single for one

Your questions are so basic that I would recommend to have a look into a good ABAP book for more and broader information.

Re: regarding SELECT statements

Former Member — Fri, 21 Sep 2007 10:08:54 GMT

i am giving total ways of writing a select query with regarding to the performance

Ways of Performance Tuning

1. Selection Criteria

2. Select Statements

• Select Queries

• SQL Interface

• Aggregate Functions

• For all Entries

Select Over more than one Internal table

Selection Criteria

1. Restrict the data to the selection criteria itself, rather than filtering it out using the ABAP code using CHECK statement.

2. Select with selection list.

Note: It is suggestible to make at least on field mandatory in Selection-Screen as mandatory fields restrict the data selection and hence increasing the performance.

Points # 1/2

SELECT * FROM SBOOK INTO SBOOK_WA.

CHECK: SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

ENDSELECT.

The above code can be much more optimized by the code written below which avoids CHECK, selects with selection list

SELECT CARRID CONNID FLDATE BOOKID FROM SBOOK INTO TABLE T_SBOOK

WHERE SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

Select Statements Select Queries

1. Avoid nested selects

2. Select all the records in a single shot using into table clause of select statement rather than to use Append statements.

3. When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

4. For testing existence , use Select.. Up to 1 rows statement instead of a Select-Endselect-loop with an Exit.

5. Use Select Single if all primary key fields are supplied in the Where condition .

Point # 1

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

Note: A simple SELECT loop is a single database access whose result is passed to the ABAP program line by line. Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop. One should therefore use nested SELECT loops only if the selection in the outer loop contains very few lines or the outer loop is a SELECT SINGLE statement.

Point # 2

SELECT * FROM SBOOK INTO SBOOK_WA.

CHECK: SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

ENDSELECT.

The above code can be much more optimized by the code written below which avoids CHECK, selects with selection list and puts the data in one shot using into table

SELECT CARRID CONNID FLDATE BOOKID FROM SBOOK INTO TABLE T_SBOOK

WHERE SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

Point # 3

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields . In certain scenarios, it is advisable to check whether a new index can speed up the performance of a program. This will come handy in programs that access data from the finance tables.

Point # 4

SELECT * FROM SBOOK INTO SBOOK_WA

UP TO 1 ROWS

WHERE CARRID = 'LH'.

ENDSELECT.

The above code is more optimized as compared to the code mentioned below for testing existence of a record.

SELECT * FROM SBOOK INTO SBOOK_WA

WHERE CARRID = 'LH'.

EXIT.

ENDSELECT.

Point # 5

If all primary key fields are supplied in the Where condition you can even use Select Single.

Select Single requires one communication with the database system, whereas Select-Endselect needs two.

Select Statements contd.. SQL Interface

1. Use column updates instead of single-row updates

to update your database tables.

2. For all frequently used Select statements, try to use an index.

3. Using buffered tables improves the performance considerably.

Point # 1

SELECT * FROM SFLIGHT INTO SFLIGHT_WA.

SFLIGHT_WA-SEATSOCC =

SFLIGHT_WA-SEATSOCC - 1.

UPDATE SFLIGHT FROM SFLIGHT_WA.

ENDSELECT.

The above mentioned code can be more optimized by using the following code

UPDATE SFLIGHT

SET SEATSOCC = SEATSOCC - 1.

Point # 2

SELECT * FROM SBOOK CLIENT SPECIFIED INTO SBOOK_WA

WHERE CARRID = 'LH'

AND CONNID = '0400'.

ENDSELECT.

The above mentioned code can be more optimized by using the following code

SELECT * FROM SBOOK CLIENT SPECIFIED INTO SBOOK_WA

WHERE MANDT IN ( SELECT MANDT FROM T000 )

AND CARRID = 'LH'

AND CONNID = '0400'.

ENDSELECT.

Point # 3

Bypassing the buffer increases the network considerably

SELECT SINGLE * FROM T100 INTO T100_WA

BYPASSING BUFFER

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

The above mentioned code can be more optimized by using the following code

SELECT SINGLE * FROM T100 INTO T100_WA

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

Select Statements contd… Aggregate Functions

• If you want to find the maximum, minimum, sum and average value or the count of a database column, use a select list with aggregate functions instead of computing the aggregates yourself.

Some of the Aggregate functions allowed in SAP are MAX, MIN, AVG, SUM, COUNT, COUNT( * )

Consider the following extract.

Maxno = 0.

Select * from zflight where airln = ‘LF’ and cntry = ‘IN’.

Check zflight-fligh > maxno.

Maxno = zflight-fligh.

Endselect.

The above mentioned code can be much more optimized by using the following code.

Select max( fligh ) from zflight into maxno where airln = ‘LF’ and cntry = ‘IN’.

Select Statements contd…For All Entries

• The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

• Large amount of data

• Mixing processing and reading of data

• Fast internal reprocessing of data

• Fast

The Minus

• Difficult to program/understand

• Memory could be critical (use FREE or PACKAGE size)

Points to be must considered FOR ALL ENTRIES • Check that data is present in the driver table

• Sorting the driver table

• Removing duplicates from the driver table

Consider the following piece of extract

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

The above mentioned can be more optimized by using the following code.

Sort int_cntry by cntry.

Delete adjacent duplicates from int_cntry.

If NOT int_cntry[] is INITIAL.

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Endif.

Select Statements contd… Select Over more than one Internal table

1. Its better to use a views instead of nested Select statements.

2. To read data from several logically connected tables use a join instead of nested Select statements. Joins are preferred only if all the primary key are available in WHERE clause for the tables that are joined. If the primary keys are not provided in join the Joining of tables itself takes time.

3. Instead of using nested Select loops it is often better to use subqueries.

Point # 1

SELECT * FROM DD01L INTO DD01L_WA

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

SELECT SINGLE * FROM DD01T INTO DD01T_WA

WHERE DOMNAME = DD01L_WA-DOMNAME

AND AS4LOCAL = 'A'

AND AS4VERS = DD01L_WA-AS4VERS

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

The above code can be more optimized by extracting all the data from view DD01V_WA

SELECT * FROM DD01V INTO DD01V_WA

WHERE DOMNAME LIKE 'CHAR%'

AND DDLANGUAGE = SY-LANGU.

ENDSELECT

Point # 2

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

Point # 3

SELECT * FROM SPFLI

INTO TABLE T_SPFLI

WHERE CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK'.

SELECT * FROM SFLIGHT AS F

INTO SFLIGHT_WA

FOR ALL ENTRIES IN T_SPFLI

WHERE SEATSOCC < F~SEATSMAX

AND CARRID = T_SPFLI-CARRID

AND CONNID = T_SPFLI-CONNID

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

The above mentioned code can be even more optimized by using subqueries instead of for all entries.

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

Reward if usefull

Re: regarding SELECT statements

Former Member — Sun, 23 Sep 2007 09:03:11 GMT

Hi Shree,

just my two cents.

It is not true that for SELECT ... END SELECT each single record is fetched from the database. The database interface still do array fetches (this could mean some hundred to some thousand rows dependent on the row width - but could be also much less) but the rows are processed in the application server row by row. It is still slower than the second example, but it would be much worse if it would be implemented as you described it (as long as PACKAGE SIZE is not used).

This type of SELECT statement has still some advantages. If you have to run over the hole table and you have memory constraints because of the table size and your memory, it could be an option to save memory.

I would also agree to Rob, that INTO CORRESPONDING helps a lot to write more stable programs (stable here meant stable against structure changes), which is always worth the small performance impact.

Regards

Ralph

Re: regarding SELECT statements

Former Member — Mon, 24 Sep 2007 08:14:45 GMT

about the 'INTO CORRESPONDING', it is often overestimated what a fieldlist (specifying fields instead of select *) will help. Fieldlist require new structures and the 'INTO CORRESPONDING'.

Use fieldlist only if you can decease table width dramatically

For the small rest 'INTO CORRESPONDING' is o.k.

The last comment that also the 'SELECT ... ENDSELECT' uses an array interface is right.

Overall, the major performance issue with SELECTS is the correct usage of indices! This is point 1, point 2 and point 3, all other are of much lower importance!

Siegfried