topic Re: Select query taking more time.. in Application Development and Automation Discussions

Select query taking more time..

former_member192432 — Thu, 24 Dec 2009 10:04:13 GMT

Hi friends ,

The below select queries are taking more time , in tried for secondary index but not found .

SELECT ebeln

ebelp

loekz

aedat

TXZ01

MATNR

werks

BEDNR

menge

meins

ELIKZ

KNTTP

LABNR

FROM ekpo INTO TABLE gt_ekpo_all

FOR ALL ENTRIES IN gt_ekko WHERE

ebeln EQ gt_ekko-ebeln " commenetd by chetan on 2009/11/13

and loekz = ' '

AND matnr IN matnr

AND werks IN werks

AND bednr IN bednr.

Internal table gt_ekko is having 277283 records.

SELECT OBJECTCLAS

OBJECTID

UDATE

TCODE

FROM cdhdr INTO TABLE gt_cdhdr2

FOR ALL ENTRIES IN gt_cdhdr

WHERE objectclas = 'EINKBELEG'

AND objectid = gt_cdhdr-objectid

AND tcode = 'ME28'.

Internal table gt_cdhdr is having 277283 records.

please can any one tell how to solve it .I tried writing non primary key outside the select statement by using delete technique then also both are taking more time .

Thanks & Regards

Chetan

Re: Select query taking more time..

Former Member — Thu, 24 Dec 2009 10:34:07 GMT

Hi,

Check the points below.

1. The selecting field from EKPO should be in the same order as in DATABSE EKPO.

2. If taking more time try to create a secondary index on ebeln loekz matnr werks bednr

regards,

NZR

Re: Select query taking more time..

former_member192432 — Thu, 24 Dec 2009 10:46:37 GMT

Hi Nazeer ,

The fields are as per the order only , and i cant create secondary index as a BASIS people instruction .

Regards

chetan

Re: Select query taking more time..

Former Member — Thu, 24 Dec 2009 23:21:22 GMT

Hi Chetan,

Use Inner Joins instead of For all entries and try

-manasi

Re: Select query taking more time..

Peranandam — Fri, 25 Dec 2009 17:45:47 GMT

keep vbeln only in where clause and remove other fields from where clause. once you fetched record into internal table do deletion on internal table. dont create secondary index.

ex.

delete tabel gt_ekpo where matnr not in matnr or

werks not in werks o

r bednr not in bednr.

Re: Select query taking more time..

Former Member — Sat, 26 Dec 2009 12:02:16 GMT

Hi Chetan ,

You should use ST05(trace) to check which index is being accessed. As per my understanding , if their are no secondary index avaliable , then it will take primary index and hence you will not get a major performance improvement by creating secondary index.

Try to use some more fields in where clause , you can also make screen fields mandatory to reduce number of entries in internal table.

Hope this helps you.

Re: Select query taking more time..

Former Member — Mon, 28 Dec 2009 11:35:35 GMT

Hi ,

Make sure your Internal Table should not be intiial for that you have to use if statement before using FOR ALL ENTRIES.

Tri this it may work.

Regards,

Sudhakar Reddy Allam

Re: Select query taking more time..

Former Member — Mon, 28 Dec 2009 12:01:27 GMT

in where clause add this also after ebeln

epelp ge l_ebelp

where ebelp is of type ebelp value is initial.

Re: Select query taking more time..

jayanthi_jayaraman — Mon, 28 Dec 2009 12:22:53 GMT

Try

1. Before the forall entries, copy the contents of gt_ekko[] to gt_ekko_temp[] table and sort by ebeln and delete adjacent duplicates.

2. Use the temp table for for all entries. And in where use ebelp ne initial.

Re: Select query taking more time..

Former Member — Tue, 29 Dec 2009 07:34:05 GMT

Hi Chetan,

1. Select from EKPO :

A. First loop at internal table gt_ekpo and collect ebeln into a hash table.

B. Make use of that hash table while using for all entries.

C. As per u r requirement even u can check intial values of hash table before select statement.

2. Select from CDHDR:

A. First loop at Internal table gt_cdhdr and collect objectid into a hash table.

B. Use the hash table for all entries.

Re: Select query taking more time..

former_member192432 — Wed, 30 Dec 2009 06:54:47 GMT

hi thanks for reply..