topic Re: Effective Select in Application Development and Automation Discussions

Effective Select

Former Member — Fri, 25 Jul 2008 09:19:48 GMT

Dear experts

I have a confusion and i am facing this from beginning...

How can we write SELECT effectively (performance) . How to choose where condition well...Because performance of my program reduced so many times with where condition...

Plz explain this in a simple way(Many time i was not able to understand links those i am receiving)

Thanks in advance

somesh

Re: Effective Select

matt — Fri, 25 Jul 2008 09:21:21 GMT

Make sure the fields in your where clause match an index on the table, as far as possible.

Don't use OR.

matt

Re: Effective Select

Former Member — Fri, 25 Jul 2008 09:22:13 GMT

Try to give the entire key in the where clause .. in select

statement.

Re: Effective Select

Former Member — Fri, 25 Jul 2008 09:24:23 GMT

Hi Venkat,

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/abap/abap%2bperformance%2band%2btuning

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/abap/abap%2bperformance

u can even know abt the tools for performance tuning by searching for se30 and st05 in this forum.

Regards.

Eshwar.

Re: Effective Select

former_member787646 — Fri, 25 Jul 2008 09:25:01 GMT

1. Specify field names in the SELECT list instead of using SELECT *

2. Add key fields in the WHERE condition to filter the data quickly.

3. Do not use OR / INNER OR operators.

4. Do not use Order By Clause

5. Do not use NOT statements (Negation Operator)

Hope this would help you.

Murthy

Re: Effective Select

Former Member — Fri, 25 Jul 2008 09:26:38 GMT

Hi,

1.Try to include all the key fields in the where conditions.

2.Never use 'select *'.....select with the specified fields..

3.Try to use index of the table in ur select query

Re: Effective Select

Former Member — Fri, 25 Jul 2008 09:27:03 GMT

Hi Venkat,

1.In general, use a SELECT statement specifying a list of fields instead of a SELECT * to reduce network traffic and improve performance. For tables with only a few fields the improvements may be minor, but many SAP tables contain more than 50 fields when the program needs only a few. In the latter case, the performace gains can be substantial. For

example:

select vbeln auart vbtyp from table vbak

into (vbak-vbeln, vbak-auart, vbak-vbtyp) where ...

Instead of using:

select * from vbak where ...

2.Row-level processing and SELECT SINGLE

Similar to the processing of a SELECT-ENDSELECT loop, when calling multiple SELECT-SINGLE commands on a non-buffered table (check Data Dictionary -> Technical Info), you should do the following to improve performance:

o Use the SELECT into <itab> to buffer the necessary rows in an internal table, then

o sort the rows by the key fields, then

o use a READ TABLE WITH KEY ... BINARY SEARCH in place of the SELECT SINGLE command. Note that this only make sense when the table you are buffering is not too large (this decision must be made on a case by case basis).

3.SELECT and SELECT SINGLE

When using the SELECT statement, study the key and always provide as much of the left-most part of the key as possible. If the entire key can be qualified, code a SELECT SINGLE not just a SELECT. If you are only interested in the first row or there is only one row to be returned, using SELECT SINGLE can increase performance by up to three times.

4.In WHERE Condition try to use key fiels,that will improve performances,,,,

Hope it is helps.

Regards,

T.D.M.

Re: Effective Select

Former Member — Fri, 25 Jul 2008 09:36:42 GMT

Hi Venkat,

There are verity of method of querying data from sap dictionary. I tell you one by one.

1. first you must know all primary key of a table. you should fire the query on a table with its primary field in condition. this way you will receive fist out put. for examples in table BKPF fields BUKRS,

BELNR and GJAHR are consitute together a primary key of this table so to fetch data from BKPF you must add condition in query as

Select BUKRS BELNR GJAHR BLART BLDAT BUDAT

into table it_bkpf

from bkpf

where BUKRS eq p_bukrs and

BELNR eq p_belnr and

GJAHR eq p_gjahr.

2. After using the primary key if your query is still taking time then create index on the field which you have added in condition of select.

3. Do not use any type of JOIN statement in query

4 Always use FOR ALL ENTRIES IN.

5. If FOR ALL ENTRIES IN is taking time then use like that

loop at it_bkpf_temp.

select bukrs belnr gjahr buzei witht wt_withcd wt_qsshh

into it_withitem

from with_item

where bukrs eq 'PIIL' and

gjahr eq it_bkpf_temp-gjahr and

belnr eq it_bkpf_temp-belnr and

hkont in s_hkont and

wt_withcd in s_qsskz.

append it_withitem.

clear it_withitem.

endselect.

endloop.

6. Do not use Select *. Choose name of fields.

Follow thse methods your no query will take time.

Regards

rajesh

Re: Effective Select

Former Member — Fri, 25 Jul 2008 09:44:37 GMT

HI Venkat,,

Performance tuning for Data Selection Statement

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)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

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 isnu2019t 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

Use the selection criteria

SELECT * FROM SBOOK.

CHECK: SBOOK-CARRID = 'LH' AND

SBOOK-CONNID = '0400'.

ENDSELECT.

SELECT * FROM SBOOK

WHERE CARRID = 'LH' AND

CONNID = '0400'.

ENDSELECT.

Use the aggregated functions

C4A = '000'.

SELECT * FROM T100

WHERE SPRSL = 'D' AND

ARBGB = '00'.

CHECK: T100-MSGNR > C4A.

C4A = T100-MSGNR.

ENDSELECT.

SELECT MAX( MSGNR ) FROM T100 INTO C4A

WHERE SPRSL = 'D' AND

ARBGB = '00'.

Select with view

SELECT * FROM DD01L

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

SELECT SINGLE * FROM DD01T

WHERE DOMNAME = DD01L-DOMNAME

AND AS4LOCAL = 'A'

AND AS4VERS = DD01L-AS4VERS

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

SELECT * FROM DD01V

WHERE DOMNAME LIKE 'CHAR%'

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

Select with index support

SELECT * FROM T100

WHERE ARBGB = '00'

AND MSGNR = '999'.

ENDSELECT.

SELECT * FROM T002.

SELECT * FROM T100

WHERE SPRSL = T002-SPRAS

AND ARBGB = '00'

AND MSGNR = '999'.

ENDSELECT.

Select u2026 Into table

REFRESH X006.

SELECT * FROM T006 INTO X006.

APPEND X006.

ENDSELECT

SELECT * FROM T006 INTO TABLE X006.

Select with selection list

SELECT * FROM DD01L

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

ENDSELECT

SELECT DOMNAME FROM DD01L

INTO DD01L-DOMNAME

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

ENDSELECT

Regards,

Sreekar.Kadiri

Re: Effective Select

Former Member — Sat, 23 Aug 2008 07:17:48 GMT