topic Re: Tuning Select Statement . field sequence and where clause in Application Development and Automation Discussions

Tuning Select Statement . field sequence and where clause

Former Member — Tue, 14 Nov 2006 08:07:11 GMT

Hi All

Are there any general guidelines how to write select < field sequence >where clause < field sequence ? Is that shuld be in order of the field sequence in tables?

And how to use this when we have a view or a inner - join . Is that separate from normal select statement that is using FOR ALL ENTRIES.

Please let me know any general guidelines available on this,

Amol

Re: Tuning Select Statement . field sequence and where clause

Former Member — Tue, 14 Nov 2006 08:17:01 GMT

hi,

The only guideline is that your sequence in select should be same sequence you have in your internal table if you are not using "into corresponding fields of table".

Atul

Re: Tuning Select Statement . field sequence and where clause

Former Member — Tue, 14 Nov 2006 08:23:02 GMT

Hi Amol

When you are doing performance Tuning ,but these things in ur mind.

When writing select stmtn.

select field1

field2 ...

fields should be in order the way it is in DB table.

In Where clause also try to use the Primary index fields.

If you are using for all entries check sy-subrc = 0 after first select.

Try to avoid join if you are using more than 2 tables on select stmnt.

When you are reading select inside the loop..endloop.

Replace that select with Read stmnt and keep that select outside the loop and use for ell anetries..

Read stmnt shud follow binary search and before reading make sure u will have sort.

Re: Tuning Select Statement . field sequence and where clause

former_member283648 — Tue, 14 Nov 2006 08:31:06 GMT

Hi,

While selecting any records from database tables following points should be bared in mind.

1. Selection fields should be in same order as in the database table and of course in the internal table also,

2. Try to specify where clause fields as much as possible.

3. Try avoid using into corresponding fields. This will improve performance while handling huge data. Instead declare a local table with fields u want to select and then move it to ur internal table with more fields.

While using for all entries.

1. It is very important to make sure the table that u are using with for all entires addition is not empty. Because empty table here would lead to selection of whole database table.

2. Make sure the table dont contain any duplicates. Even though SAP will skip duplicate entries, unnecessarily you are loading SAP to check and skip the duplicaes.

Hope these are usefull.

Re: Tuning Select Statement . field sequence and where clause

Former Member — Tue, 14 Nov 2006 08:36:16 GMT

Have a look at below weblog

/people/hema.rao/blog/2006/09/25/performance-tuning--an-overlooked-activity

Also have a look at below tips

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/abapPerformanceand+Tuning&

Avoid using SELECT...ENDSELECT... construct and use SELECT ... INTO TABLE.

Use WHERE clause in your SELECT statement to restrict the volume of data retrieved.

Design your Query to Use as much index fields as possible from left to right in your WHERE statement

Either enable buffering in your database table or create Indexes to speed up the query.

Use FOR ALL ENTRIES in your SELECT statement to retrieve the matching records at one shot.

Avoid using nested SELECT statement, SELECT within LOOPs.

Avoid using INTO CORRESPONDING FIELDS OF TABLE. Instead use INTO TABLE.

Avoid using SELECT * and Select only the required fields from the table.

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

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers

Re: Tuning Select Statement . field sequence and where clause

Former Member — Tue, 14 Nov 2006 09:04:07 GMT

Hello Amol,

I have another hint:

The statement FOR ALL ENTRIES will package the select statements for every five entries in the internal table. So in comparison to the following code sequence...

LOOP AT itab.
   SELECT * FROM table WHERE key = itab-key.
   ...
ENDLOOP

the number of select statements is reduced to 20% with

SELECT * FROM table INTO TABLE ...
     FOR ALL ENTRIES IN itab
     WHERE key = itab-key

If I'm expecting a <i>huge</i> amount of data a go a step further and create my own packages by building a range table with around 100-500 entries and execute a select there...


LOOP AT itab.
   IF counter < 500.
      APPEND itab-key TO range-tab.   " just code example
   ENDIF.
   IF count >= 500.
      SELECT * FROM table APPENDING TABLE ...
         WHERE key IN range_tab
   ENDIF.
   " adjust and calculate counter
ENDLOOP.
* Don't forget last select statement after loop

Best wishes,

Florin