topic Re: how to optimize this select statement its a simple select.... in Application Development and Automation Discussions

how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 03:22:32 GMT

how to optimize this select statement as the records in earlier table is abt i million

and this simplet select statement is not executing and taking lot of time

SELECT guid

stcts

INTO table gt_corcts

FROM corcts

FOR all entries in gt_mege

WHERE /sapsll/corcts~stcts = gt_mege-ctsex

and /sapsll/corcts~guid_pobj = gt_Sagmeld-guid_pobj.

regards

Arora

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 03:27:11 GMT

are you using sort command before select query ? if not use it will give little bit performance

If you use primary key in where cluase -> you get good performance but as per requirement some times we should not use

if possible create secondary index for that table

thanks

Seshu

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 03:32:07 GMT

before using for all entries u should check the previous internal table is initial or not.

select primary keys first and where cdn also u should use the primay key fields first. remove join concepts...

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 03:34:17 GMT

Hi Nishant,

Just check all the things given by Sheshu, also try to use PACKAGE SIZE so that the select will not time out.

Regards,

Atish

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 04:11:29 GMT

hi Seshu and all

i am sorting the table already and checkng sy-subrc also thats basic

but the earlier internal table hold abt 10lakh records unique guid_pobj as i have sorted th earlier table based on pobj and deletec adjacent duplicates based on pobj

how does t his statement help and how to create it and what are restriction i createing it and need for it?

if possible create secondary index for that table

regards

Nishant

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 04:12:18 GMT

hi atish

can u eloborate more on packaze size option and how to use it

regards

nishant

Re: how to optimize this select statement its a simple select....

jayanthi_jayaraman — Thu, 28 Jun 2007 04:13:51 GMT

Hi,

This is the information I got from SDN once.May be it can help you.

USING CURSORS WHILE FETCHING THE DATA

Cursors are useful when large volumes of data have to be processed. Their utility could be appreciated only under such circumstances. The cursor definitely works better when we need to process millions of rows and are approaching close to the limits of "maximum memory allowed to one process”. The reason is that the command 'Fetch' does not transfer the whole processed data from database server to application server in single shot, but does it in small packets. The number of cursors that could be opened by a user is specified in the INITSID.ORA file in ORACLE. Cursors might slow down the performance of the program in certain cases.

In this method we are populating the cursors, which is a kind of internal table, which hold the data for processing. This approach is good since the cursors can hold good number of records.

Example

DATA: C TYPE CURSOR,

WA LIKE equi.

OPEN CURSOR C FOR SELECT * FROM equi

WHERE <Select condition>

ORDER BY PRIMARY KEY.

DO.

FETCH NEXT CURSOR C INTO WA.

IF SY-SUBRC <> 0.

CLOSE CURSOR C. EXIT.

ENDIF.

Do something here

ENDDO.

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 04:20:44 GMT

Hi Nishant,

When you specify the package size with select statement you can restrict the select at a time and avoid the time out error. In your case give the package size may be 25000 or 50000.

Just do F1 on package size and you will get the details.

Regards,

Atish

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 05:57:49 GMT

hi jayanthi

as u told cursor might slow down the performance of the program and iam aiming for better performance... and this selec statement itself is taking hell lot of time because of 10 lakh records in eaarlier table ane we cannnt sort nor do any thing with the earlier table,

not sure if this will work and how to use it

can u tell how to use it in as above select statment

and if i remove the where condtiong does the performance will degrade or imporove

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 08:01:48 GMT

to increase the performance u can also use FIELD SYMBOL

field symbol are nothing but just acts like POINTERS

they hold the data ....of the feilds, just like pointers .

i think this will really help u

also

1>clear ur internal table befre u write select

2>try 2 hit on primary keys

3> PACKAGE SIZE is a gud option

all the best

bhanu

Re: how to optimize this select statement its a simple select....

jayanthi_jayaraman — Thu, 28 Jun 2007 08:07:37 GMT

Hi,

You can try as below.

DATA: C TYPE CURSOR,

WA LIKE equi.

OPEN CURSOR C FOR SELECT guid

stcts

FROM corcts

FOR all entries in gt_mege

WHERE /sapsll/corcts~stcts = gt_mege-ctsex

and /sapsll/corcts~guid_pobj = gt_Sagmeld-guid_pobj.

DO.

FETCH NEXT CURSOR C INTO WA.

IF SY-SUBRC <> 0.

CLOSE CURSOR C. EXIT.

ENDIF.

Do something here

ENDDO.

I tried the following situtation.It is working fine.

types : begin of ty,

matnr type mara-matnr,

maktx type makt-maktx,

end of ty.

data itab1 type standard table of mara.

select * from mara into table itab1.

*if sy-subrc eq 0.

*select matnr maktx from makt into table itab for all entries

*in itab1 where matnr = itab1-matnr.

*endif.

DATA: C TYPE CURSOR,

WA type ty.

OPEN CURSOR C FOR select matnr maktx from makt for all entries

in itab1 where matnr = itab1-matnr.

DO.

FETCH NEXT CURSOR C INTO WA.

IF SY-SUBRC <> 0.

CLOSE CURSOR C. EXIT.

ENDIF.

write : / wa-matnr.

ENDDO.

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 28 Jun 2007 08:11:04 GMT

HI Atish

didnt got much on F1 help can u suggest more for package size

as tehre are sevreal other select statements also for and we are looping it varius inernal tables so where does this package size fits there?

and can u give a example how to use package size as in above statement?

regards

Arora

Re: how to optimize this select statement its a simple select....

Former Member — Wed, 04 Jul 2007 14:22:03 GMT

goto se38, ctrlf8, type in select, press enter double click on select into, ctrlf type in package and press enter, there you'll also find a piece of abap on how to use this

Re: how to optimize this select statement its a simple select....

Former Member — Tue, 10 Jul 2007 02:31:15 GMT

hi all / Atish

can any one give examplet of package size ans will it avoid time out error

and in my case the data is about 20000 for next select so how to use package size to break this 20000 data to diffrenct packets and how to use for next select statement?

or how to combine all packates to a single next internal table

regards

arora

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 20 Dec 2007 19:59:06 GMT

Hi Arora,

Using Package size is very simple and you can avoid the time out and as well as the problem because of memory. Some time if you have too many records in the internal table, then you will get a short dump called TSV_TNEW_PAGE_ALLOC_FAILED.

Below is the sample code.

DATA p_size = 50000

SELECT field1 field2 field3

INTO TABLE itab1 PACKAGE SIZE p_size

FROM dtab

WHERE <condition>

Other logic or process on the internal table itab1

FREE itab1.

ENDSELECT.

Here the only problem is you have to put the ENDSELECT.

How it works

In the first select it will select 50000 records ( or the p_size you gave). That will be in the internal table itab1.

In the second select it will clear the 50000 records already there and append next 50000 records from the database table.

So care should be taken to do all the logic or process with in select and endselect.

Some ABAP standards may not allow you to use select-endselect. But this is the best way to handle huge data without short dumps and memory related problems.

I am using this approach. My data is much more huge than yours. At an average of atleast 5 millions records per select.

Good luck and hope this help you.

Regards,

Kasthuri Rangan Srinivasan

Re: how to optimize this select statement its a simple select....

Former Member — Thu, 20 Dec 2007 21:51:15 GMT

Using the PACKAGE SIZE addition will help only if you have memory problems, not timeout problems. I suspect that you are not using an index properly in the SELECT, but since I do not see that table in our system, I cannot tell you how to proceed. Can you tell us the indexes on your table?

Rob

Re: how to optimize this select statement its a simple select....

Former Member — Mon, 24 Dec 2007 19:12:58 GMT

Hi Nishant,

I donno why fetching 1 milllion data is giving you TIME OUT. It might be due to in suffeicient hardware support.

Packet size can help you select data in multiple packets which obviously can avoid time out.

I am fetching data from a table for 10 million records in the internal table and it ran well enough. Sorting before the for all entries might help in te select performance, instead you can also use SORTED table on the key ctsex and guid_probj. Also delete adjacent duplicates based on ctsex and guid_probj which wil reduce data in the driver table.

Always check for initial condition before using for all entries.

Regards,

Arun Devidas

Re: how to optimize this select statement its a simple select....

Former Member — Mon, 24 Dec 2007 19:21:58 GMT

Hi Arora,

I checked this table and your fields of the where clause are not in any index. There is one index that has guid_pobj but you need to create a secondary index that will have both fields of your where clause.

You might have been able to join your first table and corcts but accessing corcts with non-key fields would still be a problem.

so see if creating a secondary index is possible and then test it out.

Hope this helps.

Filler