topic Re: question regarding select staments in Application Development and Automation Discussions

question regarding select staments

Former Member — Thu, 27 Sep 2007 06:31:43 GMT

Hello Every one ,

which one is better to use ....

Nested Select staments or Inner Joins .... performance issue ...and which one is more suggestable...

Thanx & Regards,

PHANINDER

Re: question regarding select staments

Former Member — Thu, 27 Sep 2007 06:39:28 GMT

Hi,

Both of them affect the performance. So it is not adviced to use them. Try to use FOR ALL ENTRIES.

Check these links for more information

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_Introduction.asp

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

http://www.erpgenie.com/abap/performance.htm#Modifying%20a%20set%20of%20lines

http://ezinearticles.com/?SAP-ABAP-Performance-Tuning-Tips-and-Tricks&id=294417

Regards,

Priyanka.

Re: question regarding select staments

former_member189629 — Thu, 27 Sep 2007 06:39:38 GMT

Phani,

From a performance point of view, neither nested SELECTs or JOINS are advisable, unless there is no other option. Instead do individual and limited SELECTs into different tables sequentially, using FOR ALL ENTRIES and bind your output data into your final OP itab after all ut data is retrieved thru individual SELECTs. Ensurre the WHERE conditions are on the PKeys or Indices and also check the order of fields in the WHERE condition.

reward if helpful,

Regards,

Karthik

Re: question regarding select staments

hymavathi_oruganti — Thu, 27 Sep 2007 06:41:15 GMT

inner join is always better than nested selectes.

regarding better performance techniques, u can see tips and tricks in the tcode se30.

Re: question regarding select staments

Former Member — Thu, 27 Sep 2007 06:42:52 GMT

Hi Phaninder ,

Joins are prefered as in case of joins the filtering of data is done at the database level and there is only once call to the database , but in the case of nested select there will be multipl database calls.

Reagdrs

Arun

Re: question regarding select staments

Former Member — Thu, 27 Sep 2007 06:43:10 GMT

Hi,

For better performance you should use inner join and at the same time you can use for all entries also. But never get into nested select statement.

Go to SE30 in that you can have tips and tricks button for better performance.

Thanks,

Sriram Ponna.

Re: question regarding select staments

Former Member — Thu, 27 Sep 2007 06:45:58 GMT

Hi,

Nested selects

The plus:

Small amount of data

Mixing processing and reading of data

Easy to code - and understand

The minus:

Large amount of data

when mixed processing isn’t needed

Performance killer no. 1

Select using JOINS

The plus

Very large amount of data

Similar to Nested selects - when the accesses are planned by the programmer

In some cases the fastest

Not so memory critical

The minus

Very difficult to program/understand

Mixing processing and reading of data not possible

Regards,

Bhaskar

Re: question regarding select staments

Former Member — Thu, 27 Sep 2007 06:51:17 GMT

i am giving you the select statements peformance

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

finally compared to nested select , joins , for all entries

the performance is better in this path

nested selest -> joins -> for all entries

Reward if usefull