cancel
Showing results for 
Search instead for 
Did you mean: 

select statemnet takes long time

former_member333737
Active Participant
0 Kudos

Hello Experts,

My select query takes a long time to give its output when I select and order type say PM15.

It gives me the return message but takes a long time.

Is there any other possibility to get output much faster.

SELECT a~aufnr
       a~auart
       a~objnr
       a~werks
       b~stat INTO CORRESPONDING FIELDS OF TABLE itab1
              FROM aufk AS A JOIN jest AS B
              ON a~objnr EQ b~objnr
              WHERE a~aufnr IN  s_aufnr
              AND  a~auart in s_auart
              AND A~werks IN S_IWERK
              AND b~inact EQ  ' '.                     "can set incat = space(Active) OR incat = 'X'(Not Active)


SELECT a~aufnr
       a~auart
       a~objnr
       a~werks
       b~stat INTO CORRESPONDING FIELDS OF TABLE itab
              FROM aufk AS A JOIN jest AS B
              ON a~objnr EQ b~objnr
              WHERE a~aufnr IN  s_aufnr
              AND  a~auart in s_auart
              AND A~werks IN S_IWERK
              AND b~stat  IN  ('I0002','I0340').


READ TABLE ITAB INDEX 1.

refresh: T_METH.


  clear: T_METH.
  clear :wa_meth.

sort itab.
DELETE ADJACENT DUPLICATES FROM ITAB COMPARING AUFNR.


LOOP AT ITAB.
  wa_meth-refnumber     = 1.
  wa_meth-objecttype    = 'HEADER'.
  wa_meth-method        = 'ATPCHECK'.
  wa_meth-objectkey(12) = ITAB-AUFNR.
*  wa_meth-objectkey(12) = ITAB-OBJNR.

  append  wa_meth to t_meth.

  clear  wa_meth.
   wa_meth-refnumber     = 1.
   wa_meth-method        = 'SAVE'.
   wa_meth-objectkey(12) = ITAB-AUFNR.
*   wa_meth-objectkey(12) = ITAB-OBJNR.
  append  wa_meth to t_meth.

  call function 'BAPI_ALM_ORDER_MAINTAIN'
      tables
      it_methods   = T_METH
      return       = T_RET.


  call function 'BAPI_TRANSACTION_COMMIT'.


LOOP AT T_RET.

write:/ T_RET-MESSAGE.

EXIT.

ENDLOOP.

ENDLOOP.

Thanks and Regrds,

Nikhil.

Accepted Solutions (0)

Answers (7)

Answers (7)

kesavadas_thekkillath
Active Contributor
0 Kudos

You can neglect the second query by pulling the values in first query itself


  SELECT a~aufnr
       a~auart
       a~objnr
       a~werks
       b~stat 
       b~inact "New Addition
              INTO CORRESPONDING FIELDS OF TABLE itab1
              FROM aufk AS A JOIN jest AS B
              ON a~objnr EQ b~objnr
              WHERE a~aufnr IN  s_aufnr
              AND  a~auart  in s_auart
              AND A~werks  IN S_IWERK.
              "AND b~inact EQ  ' '.  removed.
  

now the itab1 will consists of inact EQ ' ' and b~stat IN ('I0002','I0340').

try to process the internal table.

Dont know how far it will help you

former_member333737
Active Participant
0 Kudos

Hello Guys,

Its working fine when I debugged the values entering the internal table ITAB are quick but the problem is when this value are impotring into BAPI it takes a long time.

and one more issue I am facing is when the list is displayed I dont get all PM orders which are there in AUFK table.

What might be the problem with this.

Thanks and Regrds,

nikhil.

Former Member
0 Kudos

>

> Its working fine when I debugged the values entering the internal table ITAB are quick but the problem is when this value are impotring into BAPI it takes a long time..

But your first post said that the SELECT was taking too long. Have you read before posting? You need to do some analysis before asking the forum.

Thread locked.

Rob

Former Member
0 Kudos

Hi,

Remove Corresponding fields

use only key fields in the where condition

Regards

Renu

Edited by: Renu Gusain on Sep 10, 2009 12:06 PM

Former Member
0 Kudos

Hi,

In the above select query you are using into corresponding fields that can be avoided first to improve the performance that is in your internal table decalaration you can take care that thing moreover in the select query also the fields are not in oreder it is best practice to declare internal table fields in order as well as in select query also if you can mention the fields in oreder as in DBtable.

as in index of aufk the order is like KOKRS,ABKRS and AUART fields are there in index A. So it is better and good practice that if you take AUART field as first in where condition.

so above things will increase the performance of your select query so please make changes and please let me know the result.

Former Member
0 Kudos
- remove into corresponding fields, use into table itab1
- remove loop intside that loop. use read statement 
- remove all nonkey/ non indexed field from where condition. and filter them after selection by using 'delete itab whre xyz NE =ABC' '
Former Member
0 Kudos

HI,

If possible, search for some index.

or

remove the join and fetch all the data from aufk in one internal table.

Within loop check for status and paas the required entris into other internal table.

This could reduce stress on database.

Regds,

Anil

Former Member
0 Kudos

Hi, did you check if there are indexes for the fields you are using. This is the fast way to do something on the performance. Succes

former_member537489
Participant
0 Kudos

Hello,

could be that the keys you are using for the select statement :

WHERE a~aufnr IN s_aufnr

AND a~auart in s_auart

AND A~werks IN S_IWERK

are not in the best order according to indices or key fields of AUFK.

If you change the order of the keys of the where clause, it may go faster.

please let us know.

Roxani