there is better way for this select?

[Former Member]

hallow

there is a better way to do this select (not a1,a2,a3....)

in proj_tab_name there is more fields then op1....op2

SELECT op01 op02 op03 op04 op05 op06 op07 op08 op09 op10 op11 op12

FROM (proj_tab_name)

INTO TABLE truma_tab

WHERE zyear = year.

Regards

[Former Member]

Hi,

do like this.

SELECT <b>* </b>FROM (proj_tab_name)

INTO<b> corresponding fields of </b>TABLE truma_tab

WHERE zyear = year.

<b>note:</b>but make sure that the field name in the internal table truma_tab is same as the field names in the dbtable

<b>reward if helpful</b>

rgds,

bharat.

[Former Member]

hi bharat

i ask that from the side of perfomance.

Regards

[former_member198275]

SELECT op01 op02 op03 op04 op05 op06 op07 op08 op09 op10 op11 op12

FROM (proj_tab_name)

INTO corresponding fields of TABLE truma_tab

WHERE zyear = year.

Message was edited by:

Kaushik Datta

[Former Member]

Hi,

We can use INTO CORRESPONDING FIELDS here..

But performance vise, the use of this statement is not good.. As during the data assignement the system checks for each field and it degrades the performance..

Your select query is perfect!!! BUT make sure that the fields that you are slecting from the table are the FIRST fields of your internal table..

This means.. your internal table should have the exact sequence of fields and the rest fields should be only after them..

Structure of truma_tab

op01 op02 ....... op12 next1 next2 and so on...

Thanks and Best Regards,

Vikas Bittera.

**Points for useful answers**

[Former Member]

Hi,

performance wise there is no good alternation for what u have written

rgds,

bharat.

[Former Member]

hi,

if internal have same structure of database table then try like this

SELECT * FROM (proj_tab_name)

INTO TABLE truma_tab

WHERE zyear = year.

if both structures are differ then

SELECT * FROM (proj_tab_name)

INTO corresponding fields of TABLE truma_tab

WHERE zyear = year. /// which consumes more time than select st above.

if helpful reward some points.

with regards,

Suresh Aluri.

[Former Member]

Hi

reward if usefull

SELECT *
FROM (proj_tab_name)
INTO TABLE truma_tab
WHERE zyear = year.

see here some points are there how to improv performance of the select query

Selection Criteria
Restrict the data to the selection criteria itself, rather than filtering it out using the ABAP code using CHECK statement. 
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.

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.

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.

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'.

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( * )

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'.