topic Re: performance problem in select data from data base in Application Development and Automation Discussions

performance problem in select data from data base

Former Member — Fri, 22 Jun 2007 08:13:52 GMT

hello all,

could you please suggest me which select statement is good for fetch data form data base if data base contain more than 10 lac records.

i am using SELECT PACKAGE SIZE n statement, but it's taking lot of time .

with best regards

srinivas rathod

Re: performance problem in select data from data base

Former Member — Fri, 22 Jun 2007 08:17:28 GMT

HI,

Here is the link for Select query optimization link

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

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

Regards

Sudheer

Re: performance problem in select data from data base

Former Member — Fri, 22 Jun 2007 13:27:10 GMT

If you are selecting many records, there's no way you can improve performance beyond a certain point. Can you paste your code?

Rob

Re: performance problem in select data from data base

Former Member — Fri, 22 Jun 2007 15:50:56 GMT

hello Rob,

I have around 7 lacs entries in a Z table. Based on requirements sometimes Key fields are also not being provided.

Database selection is taking more than 5 mins after that the table output is generated as a ALV tree control. it is taking again some time to build nodes of ALV tree.

Sometimes i am getting TIME_OUT dump also.

Is there still any possibility of performance improvment?

select code is here..

SELECT * FROM Zitab INTO CORRESPONDING FIELDS OF

TABLE lt_spool PACKAGE SIZE 5000 WHERE

dokar IN so_dokar AND

doknr IN so_doknr AND

dokvr IN so_dokvr AND

doktl IN so_doktl AND

recid IN lr_recid AND

com_typ1 IN so_comtp AND

reasonbpr IN so_contx AND

dokst IN so_dokst AND

aennr IN so_aennr AND

sent_date IN so_stdat AND

confirm_date IN so_cfdat AND

remind_date IN so_rmdat AND

closed_date IN so_cldat AND

sap_packet IN lr_packt.

...

endselect.

Thanks.

Re: performance problem in select data from data base

Former Member — Fri, 22 Jun 2007 16:11:38 GMT

PACKAGE SIZE won't really help performance. You can use it when you are returning so much data that the internal table can't hold them all and it dumps with a memory problem. That may or may not be the case here (it probably is). In any event, try increasing the package size as much as you can. Change it from 5000 to 50000. If that dumps (with a memory error) decrease the package size; if it works, increase it. Take the maximum package size that doesn't cause a dump.

I should also say that if you aren't using the index, that will probably be the primary source of the problem.

Rob

PS - a bit of an understanding issue on my part. Is lac a million?

Message was edited by:

Rob Burbank

Re: performance problem in select data from data base

Former Member — Fri, 22 Jun 2007 16:16:28 GMT

Hi Srinivas,

if you have huge data and selecting ,you could decrease little bit time if you use better techniques.

I do not think SELECT PACKAGE SIZE will give good performance

see the below examples :

ABAP Code Samples for Simple Performance Tuning Techniques

1. Query including select and sorting functionality

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on

f_{matnr = g}matnr where g_{stlal = '01' order by f}ernam.

Code B

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01'.

sort itab_new by ernam.

Both the above codes essentially do the same function, but the execution time for code B is considerably lesser than that of Code A. Reason: The Order by clause associated with a select statement increases the execution time of the statement, so it is profitable to sort the internal table once after selecting the data.

2. Performance Improvement Due to Identical Statements – Execution Plan

Consider the below queries and their levels of efficiencies is saving the execution

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01' .

sort itab_new.

select f_{matnr f}ernam

f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as

f inner join mast as g on f~matnr =

g_{matnr where g}stlal

= '01' .

Code D (Identical Select Statements)

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

werks like mast-werks,

aenam like mast-aenam,

stlal like mast-stlal,

end of itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01' .

sort itab_new.

select f_{matnr f}ernam f_{mtart f}matkl g_{werks g}aenam g~stlal

into table itab_new from mara as f inner join mast as g on f~matnr =

g_{matnr where g}stlal = '01' .

Both the above codes essentially do the same function, but the execution time for code B is considerably lesser than that of Code A. Reason: Each SQL statement during the process of execution is converted into a series of database operation phases. In the second phase of conversion (Prepare phase) an “execution plan” is determined for the current SQL statement and it is stored, if in the program any identical select statement is used, then the same execution plan is reused to save time. So retain the structure of the select statement as the same when it is used more than once in the program.

3. Reducing Parse Time Using Aliasing

A statement which does not have a cached execution plan should be parsed before execution; this parsing phase is a highly time and resource consuming, so parsing time for any sql query must include an alias name in it for the following reason.

1. Providing the alias name will enable the query engine to resolve the tables to which the specified fields belong to.

2. Providing a short alias name, (a single character alias name) is more efficient that providing a big alias name.

Code E

select j_{matnr j}ernam j_{mtart j}matkl

g_{werks g}aenam g~stlal into table itab_new from mara as

j inner join mast as g on j_{matnr = g}matnr where

g~stlal = '01' .

In the above code the alias name used is ‘ j ‘.

4. Performance Tuning Using Order by Clause

If in a SQL query you are going to read a particular database record based on some key values mentioned in the select statement, then the read query can be very well optimized by ordering the fields in the same order in which we are going to read them in the read query.

Code F

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

end of itab_new.

select MATNR ERNAM MTART MATKL from mara into table itab_new where

MTART = 'HAWA' ORDER BY MATNR ERNAM MTART MATKL.

read table itab_new with key MATNR = 'PAINT1' ERNAM = 'RAMANUM'

MTART = 'HAWA' MATKL = 'OFFICE'.

Code G

tables: mara, mast.

data: begin of itab_new occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

mtart like mara-mtart,

matkl like mara-matkl,

end of itab_new.

select MATNR ERNAM MTART MATKL from mara into table itab_new where

MTART = 'HAWA' ORDER BY ERNAM MATKL MATNR MTART.

read table itab_new with key MATNR = 'PAINT1' ERNAM = 'RAMANUM'

MTART = 'HAWA' MATKL = 'OFFICE'.

In the above code F, the read statement following the select statement is having the order of the keys as MATNR, ERNAM, MTART, MATKL. So it is less time intensive if the internal table is ordered in the same order as that of the keys in the read statement.

5. Performance Tuning Using Binary Search

A very simple but useful method of fine tuning performance of a read statement is using ‘Binary search‘ addition to it. If the internal table consists of more than 20 entries then the traditional linear search method proves to be more time intensive.

Code H

select * from mara into corresponding fields of table intab.

sort intab.

read table intab with key matnr = '11530' binary search.

Code I

select * from mara into corresponding fields of table intab.

sort intab.

read table intab with key matnr = '11530'.

Thanks

Seshu

Re: performance problem in select data from data base

Former Member — Sat, 23 Jun 2007 09:54:17 GMT

Hi srinivas,

your where clause is too long

it will only confuse the database optimizer on which index to use

so what you need to do is re-arrange the where clause of the query in the order in which you primary index / secondary index is defined.

the rest of the conditions can be evaluated within the program unless they filter a lot of data.

also avoid INTO CORRESPONDING FIELDS

also try to make the first few index fields mandatory in your program (or ensure that they are filled with some valid default values)

Kindly reward points to helpful answers

you can use ST05 & check which index (if any) is being used during the select process...

Re: performance problem in select data from data base

Former Member — Sat, 23 Jun 2007 20:28:17 GMT

You need to change your select.

1) Dont give *. Instead select only those fields you need to populate into the internal table from the database table in the same order as present in the db table.

2) Dont use into correspoding. Instead create an internal table with only those fields you will fetching from the database table and use Select ... INTO TABLE

3) Dont use Select Endselect. This works like a loop. Instead use Select <fields> ...

into table <internal table> from <database table> where ...

4) Do not give too many conditions while selecting from database. Use only primary fields in the where condition or any field for which secondary indexes are maintained and filter the records from the internal table by using delete statement.

Delete it_spool where not Dokar IN so_dokar.

Delete it_spool where not doknr IN so_doknr.

...

Example: Selecting data from HR table PA0001.

DATA:

begin of i_0001 occurs 0,

pernr like pa0001-pernr,

begda like pa0001-begda,

endda like pa0001-endda,

bukrs like pa0001-bukrs,

werks like pa0001-werks,

btrtl like pa0001-btrtl,

persg like pa0001-persg,

persk like pa0001-persk,

orgeh like pa0001-orgeh,

end of i_0001.

Select pernr begda endda bukrs werks btrtl persg persk orgeh

from pa0001

into table i_0001

where pernr in s_pernr

and endda >= sy-datum

and begda <= sy-datum.

if sy-subrc = 0.

delete i_0001 where not bukrs in s_bukrs and

not werks in s_werks and

not btrtl in s_btrtl and

not persg in s_persg and

not persk in s_persk and

not orgeh in s_orgeh.

endif.

Regards

Navneet