topic Reagrding performance- select/ endselect in Application Development and Automation Discussions

Reagrding performance- select/ endselect

Former Member — Wed, 30 May 2007 07:07:26 GMT

Hi Everyone,

Had a query as in how impaortant is it from performance point of view to not use select/ endselect rather use select into table.

Other query is if I write select into table then we need to make multiple internal table else we write select into corresponding fields of table.

I know into corresponding fields of table should be avoided but if i write as into ( itab-f1, itab-f2....) then we would need to put endselect.

And people say select endseelct is like a crime from performance point of view, somehow I am not totally convienced. There has to certatin conditions if and but where you can use one way and not the other.

If some one can give me a detailed analysis about using all the scenarios above.

I hope I am clear.

Thanks to every one reading it in advance.

Rgds,

Anu.

Re: Reagrding performance- select/ endselect

Former Member — Wed, 30 May 2007 07:10:12 GMT

Some performance tips are

1) Dont use nested select statements

2) If possible use for all entries in addition

3) In the where addition make sure you give all the primary key

4) Use Index for the selection criteria.

5) You can also use inner joins

6) You can try to put the data from the first select statement into an Itab and then in order to select the data from the second table use for all entries in.

7) Use the runtime analysis SE30 and SQL Trace (ST05) to identify the performance and also to identify where the load is heavy, so that you can change the code accordingly

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234

Reward points if useful

Regards

Anji

Re: Reagrding performance- select/ endselect

Former Member — Wed, 30 May 2007 07:11:42 GMT

Hi Anu,

select endselect statement eats all the time and it can create a big performance issue because for every single receord it has to access the BD table.

select into table and using corresponding fields is better when compared to select end select so u can go for it.

Reward if helpful.

Regards,

Ravi G

Re: Reagrding performance- select/ endselect

sreeramkumar_madisetty — Wed, 30 May 2007 07:11:46 GMT

WHile running the report go to SE38 initial screen~~> program-> execute~~> runtime analysis. From there Execute the program..

There you can find the time taken by diffrent ABAP statements in ur program...

Basically improving performance means "reducing DB time even though it increases ABAP run time"

So you have to concentarte on SELECT statements... Try to reduce the time taken by the SELECT by using different methods like..

1. Passing entire primary key

2. Using Views

3. Joins

4. Secondary Indexes

5. For all entries..

... etc

for more info...

http://saptechnical.com/Tutorials/ABAP/PerformanceSTDs.htm

You are correct....And people say select endseelct is like a crime from performance point of view, somehow I am not totally convienced. There has to certatin conditions if and but where you can use one way and not the other.

In some cases we can use that.

Regards,

Sree

Re: Reagrding performance- select/ endselect

Former Member — Wed, 30 May 2007 07:12:28 GMT

hi,

ABAP performance depends upon various factors and in devicded in three parts:

1. Database

2. ABAP

3. System

Run Any program using SE30 (performance analys) to improve performance refer to tips and trics section of SE30, Always remember that ABAP perfirmance is improved when there is least load on Database.

u can get an interactive grap in SE30 regarding this with a file.

also if u find runtime of parts of codes then use :

Switch on RTA Dynamically within ABAP Code

*To turn runtim analysis on within ABAP code insert the following code

SET RUN TIME ANALYZER ON.

*To turn runtim analysis off within ABAP code insert the following code

SET RUN TIME ANALYZER OFF.

Always check the driver internal tables is not empty, while using FOR ALL ENTRIES

Avoid for all entries in JOINS

Try to avoid joins and use FOR ALL ENTRIES.

Try to restrict the joins to 1 level only ie only for tables

Avoid using Select *.

Avoid having multiple Selects from the same table in the same object.

Try to minimize the number of variables to save memory.

The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

Avoid creation of index as far as possible

Avoid operators like <>, > , < & like % in where clause conditions

Avoid select/select single statements in loops.

Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

Avoid using ORDER BY in selects

Avoid Nested Selects

Avoid Nested Loops of Internal Tables

Try to use FIELD SYMBOLS.

Try to avoid into Corresponding Fields of

Avoid using Select Distinct, Use DELETE ADJACENT

Go through the following Document

Check the following Links

http://www.sapgenie.com/abap/performance.htm

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

check the below link

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

See the following link if it's any help:

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

Check also http://service.sap.com/performance

and

books like

http://www.sap-press.com/product.cfm?account=&product=H951

http://www.sap-press.com/product.cfm?account=&product=H973

http://www.sap-img.com/abap/more-than-100-abap-interview-faqs.htm

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

Performance tuning for Data Selection Statement

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

Run Time Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c6/617cafe68c11d2b2ab080009b43351/content.htm

SQL trace

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

CATT - Computer Aided Testing Too

http://help.sap.com/saphelp_47x200/helpdata/en/b3/410b37233f7c6fe10000009b38f936/frameset.htm

Test Workbench

http://help.sap.com/saphelp_47x200/helpdata/en/a8/157235d0fa8742e10000009b38f889/frameset.htm

Coverage Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c7/af9a79061a11d4b3d4080009b43351/content.htm

Runtime Monitor

http://help.sap.com/saphelp_47x200/helpdata/en/b5/fa121cc15911d5993d00508b6b8b11/content.htm

Memory Inspector

http://help.sap.com/saphelp_47x200/helpdata/en/a2/e5fc84cc87964cb2c29f584152d74e/content.htm

ECATT - Extended Computer Aided testing tool.

http://help.sap.com/saphelp_47x200/helpdata/en/20/e81c3b84e65e7be10000000a11402f/frameset.htm

Just refer to these links...

You can go to the transaction SE30 to have the runtime analysis of your program.Also try the transaction SCI , which is SAP Code Inspector.

Check this out sql tracer

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

Also refer

Performance Tuning

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234

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

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

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

http://www.erpgenie.com/abap/performance.htm

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

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

http://help.sap.com/saphelp_nw2004s/helpdata/en/c6/617cafe68c11d2b2ab080009b43351/frameset.htm

http://help.sap.com/saphelp_nw2004s/helpdata/en/d1/801f7c454211d189710000e8322d00/frameset.htm

Re: Reagrding performance- select/ endselect

Former Member — Wed, 30 May 2007 07:12:57 GMT

Hi,

Reduce the load on the database engine

If all the primary keys of a table can be provided in the where clause it is a good practice to use SELECT SINGLE. Rather than using SELECT UP TO 1 ROWS etc.

E.g. Do not use the following statement:-

Select matnr up to 1 rows from mara

into mara-matnr

where matnr = itab-matnr.

Instead use the following statement:-

Select single matnr from mara

into mara-matnr

where matnr = itab-matnr.

Cluster and pool tables store data from various data objects and so is the most convenient point for accessing data. But this has adverse affect on the performance. It is always a good practice to avoid reading directly from cluster and pool tables.

Identify all the transparent tables, which store the required data. Though the data cannot be retrieved from one table it is much faster than retrieving from cluster or pool tables.

E.g. Do not use the following statement: -

Select belnr buzei audat

From bseg

Into ( bseg-belnr,

bseg-buzei,bseg-audat)

Where belnr in s_cust.

Endselect.

Instead use the following statement:-

Select belnr buzei audat

From bsak

Into ( bsak-belnr, bsak-buzei,bsak-audat)

Where belnr in s_cust.

Endselect.

****do rewards if usefull

Regards,

vijay

Re: Reagrding performance- select/ endselect

Former Member — Wed, 30 May 2007 07:39:16 GMT

hi anu,

try to avoid select and endselect bcaz it select's one reocrd from database and move into <itab>, agin it goes to database for the second record and moves to <itab> and so on............... if database table has lakhs of records then u can assume performance of the report,

if u use into table, at a time it can select avbl records and move to the <itab> at a single shot.

then about move-corresponding, try to avoid even this bcaz it takes one record from database and insert into particular field of <itab> after finding the same field in <itab>, when database table structure and <itab> structure mismatches,

try do define same structure in <itab> as data base table strucure, then u can avoid move-corresponding.

regards,

seshu.

Re: Reagrding performance- select/ endselect

Former Member — Thu, 14 Jun 2007 16:32:43 GMT

I beleive any SQL statement be it SELECT/END SELECT or SELECT SINGLE etc is executed once only at the DB server level. The DB then returns all the found entries in an internal table to the application server.

The loop between the SELECT/ENDSELCT is then executed for each record between the user level and the application server.

soyab