topic Query takes long time in Application Development and Automation Discussions

Query takes long time

Former Member — Mon, 27 Aug 2007 12:03:15 GMT

hi experts,

I have made Purchase Requisition . User has to enetr plant, Date and Material. BUt it taks long time to execute. sometimes shows with Timeout. Below is my Code. can u plz send me the updations to make it more faster. iTs urgent

SELECT BADAT LFDAT BANFN MATNR TXZ01 MENGE MEINS EBELN EBELP WERKS INTO (IT_EKKO-BADAT, IT_EKKO-LFDAT, IT_EKKO-BANFN, IT_EKKO-MATNR_EB,

IT_EKKO-TXZ01_EB, IT_EKKO-MENGE_EB, IT_EKKO-MEINS_EB, IT_EKKO-EBELN_EB, IT_EKKO-EBELP_EB, IT_EKKO-WERKS)

FROM EBAN

WHERE EBAN~WERKS IN S_WERKS AND EBAN~BADAT IN S_DATE AND EBAN~MATNR IN S_MAT AND EBAN~LOEKZ = SPACE.

IF IT_EKKO-EBELN_EB <> SPACE.

SELECT EBELN EBELP AEDAT MATNR TXZ01 MENGE MEINS NETPR

INTO (IT_EKKO-EBELN, IT_EKKO-EBELP, IT_EKKO-AEDAT, IT_EKKO-MATNR, IT_EKKO-TXZ01_EP, IT_EKKO-MENGE, IT_EKKO-MEINS,

IT_EKKO-NETPR)

FROM EKPO

WHERE EKPO~EBELN = IT_EKKO-EBELN_EB AND EKPO~EBELP = IT_EKKO-EBELP_EB AND EKPO~LOEKZ = SPACE.

it_ekko-pend = IT_EKKO-MENGE_EB - IT_EKKO-MENGE.

SELECT single BEDAT RESWK INTO (IT_EKKO-BEDAT, IT_EKKO-RESWK)

FROM EKKO

WHERE EKKO~EBELN = IT_EKKO-EBELN.

ENDSELECT.

IF SY-SUBRC = 0 AND IT_EKKO-EBELN_EB <> SPACE.

SELECT ERDAT VBELN MATNR ARKTX LFIMG MEINS VGBEL INTO (IT_EKKO-ERDAT, IT_EKKO-VBELN, IT_EKKO-MATNR_LI,

IT_EKKO-ARKTX, IT_EKKO-LFIMG, IT_EKKO-MEINS_LI, IT_EKKO-VGBEL)

FROM LIPS

WHERE LIPS~VGBEL = IT_EKKO-EBELN AND LIPS~VGPOS = IT_EKKO-EBELP.

IF SY-SUBRC = 0.

n = 1.

LOOP AT IT_TRFRATE WHERE matnr_tr = it_ekko-matnr_li.

IF N = 1.

IT_EKKO-TRFRATE = IT_TRFRATE-TRFRATE.

select single umren from marm into vumren where marm~matnr = IT_EKKO-MATNR

and ( marm~meinh = 'L' or marm~meinh = 'KG' ).

endselect.

select single umrez from marm into vumrez where marm~matnr = IT_EKKO-MATNR

and ( marm~meinh = 'L' or marm~meinh = 'KG' ).

endselect.

compute vlfimg = ( IT_EKKO-LFIMG * vumren ) / vumrez.

IT_EKKO-NETPR = VLFIMG * IT_EKKO-TRFRATE.

CLEAR IT_EKKO-PEND_LI.

it_ekko-pend_li = IT_EKKO-MENGE_EB - IT_EKKO-LFIMG.

N = N + 1.

ENDIF.

ENDLOOP.

SELECT single WBSTK INTO (IT_EKKO-WBSTK)

FROM VBUK

WHERE VBUK~VBELN = IT_EKKO-VBELN.

ENDSELECT.

ELSE.

n = 1.

LOOP AT IT_TRFRATE WHERE matnr_tr = it_ekko-matnr_li.

IF N = 1.

IT_EKKO-TRFRATE = IT_TRFRATE-TRFRATE.

select single umren from marm into vumren where marm~matnr = IT_EKKO-MATNR

and ( marm~meinh = 'L' or marm~meinh = 'KG' ).

endselect.

select single umrez from marm into vumrez where marm~matnr = IT_EKKO-MATNR

and ( marm~meinh = 'L' or marm~meinh = 'KG' ).

endselect.

compute vlfimg = ( IT_EKKO-LFIMG * vumren ) / vumrez.

IT_EKKO-NETPR = VLFIMG * IT_EKKO-TRFRATE.

CLEAR IT_EKKO-PEND_LI.

it_ekko-pend_li = IT_EKKO-MENGE_EB - IT_EKKO-LFIMG.

N = N + 1.

ENDIF.

ENDLOOP.

SELECT single WBSTK INTO (IT_EKKO-WBSTK)

FROM VBUK

WHERE VBUK~VBELN = IT_EKKO-VBELN.

ENDSELECT.

ENDIF.

ENDSELECT.

IT_EKKO-PEND_LI = IT_EKKO-MENGE - IT_EKKO-LFIMG.

APPEND IT_EKKO.

CLEAR IT_EKKO.

ELSE.

IT_EKKO-PEND_LI = IT_EKKO-MENGE - IT_EKKO-LFIMG.

APPEND IT_EKKO.

ENDIF.

CLEAR IT_EKKO.

ENDSELECT.

IF SY-SUBRC <> 0.

it_ekko-pend = IT_EKKO-MENGE_EB.

APPEND IT_EKKO.

ENDIF.

ELSE.

it_ekko-pend = IT_EKKO-MENGE_EB.

APPEND IT_EKKO.

ENDIF.

ENDSELECT.

******************************************************************************************

THanks.

Khan

Re: Query takes long time

Former Member — Mon, 27 Aug 2007 12:06:15 GMT

Hi!

Remove the larges SELECT - ENDSELECT from your program.

Try to replace it with SELECT ... INTO TABLE ... statement and then LOOP AT ... ENDLOOP on your table.

Use SE30 also for runtime analysis.

Regards

Tamá

Re: Query takes long time

Former Member — Mon, 27 Aug 2007 12:48:42 GMT

Check other postings on SQL trace and run SQL Trace, get SQL Summary,

and post it here.

=> This will show you where the runtime is spend.

Siegfried

Re: Query takes long time

Former Member — Mon, 27 Aug 2007 13:26:29 GMT

reward if usefull

SELECT ENDSELECT makes the loop so it will take so much time to execute the program

if you avoid this then it will be very fast execution

see this

Improve performance of SELECT

This tip has been copied from SearchSap.com

Tip submitted by: Ben Meijs

As you all know, it is important to use as many key fields as possible in WHERE clauses of SELECT

statements. Sometimes you are not sure about the value of some key

fields. They seem to be empty (Initial value) but you are afraid to use this in your ABAP coding.

Using the option GE (greater equal) in your coding can improve your performance considerably

without the risk of table lines not being selected.

Code

REPORT ZZBM_SELECT_1 .

TABLES: S001.

CONSTANTS: SSOUR_INI LIKE S001-SSOUR VALUE IS INITIAL,

VRSIO_INI LIKE S001-VRSIO VALUE IS INITIAL,

SPMON_INI LIKE S001-SPMON VALUE IS INITIAL,

SPTAG_INI LIKE S001-SPTAG VALUE IS INITIAL,

SPWOC_INI LIKE S001-SPWOC VALUE IS INITIAL,

SPBUP_INI LIKE S001-SPBUP VALUE IS INITIAL.

DATA: TA_S001 TYPE STANDARD TABLE OF S001.

DATA: WA_S001 TYPE S001.

SELECT-OPTIONS:

SO_SPTAG FOR S001-SPTAG OBLIGATORY,

SO_KUNNR FOR S001-KUNNR OBLIGATORY,

SO_VKORG FOR S001-VKORG OBLIGATORY,

SO_VTWEG FOR S001-VTWEG OBLIGATORY,

SO_SPART FOR S001-SPART OBLIGATORY,

SO_MATNR FOR S001-MATNR OBLIGATORY.

START-OF-SELECTION.

SELECT * FROM S001

INTO TABLE TA_S001

WHERE SSOUR GE SSOUR_INI "Greater Equal initial value

AND VRSIO GE VRSIO_INI " idem

AND SPMON GE SPMON_INI " idem

AND SPTAG IN SO_SPTAG

AND SPWOC GE SPWOC_INI " idem

AND SPBUP GE SPBUP_INI " idem

AND KUNNR IN SO_KUNNR

AND VKORG IN SO_VKORG

AND VTWEG IN SO_VTWEG

AND SPART IN SO_SPART

AND MATNR IN SO_MATNR.

Package size in SELECT statements

Package size can be used to retreive a spcific number of records at a time. This can be used if you

for example only want tofinish processing a limited amount of data at a time due to lack of memory.

The exampel below read 50 records at a time from VBAK into an internal table, and selects the

corresponding entries from vbap into an internal table. Then the two internal tables can be

processed, and the next 50 records from VBAk can be read. remeber to reinitialize tha tables before

the next read.

Note the usage of SELECT - ENDSELECT !

REPORT z_test .

TYPES:

BEGIN OF t_vbak,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

END OF t_vbak,

BEGIN OF t_vbap,

posnr LIKE vbap-posnr,

matnr LIKE vbap-matnr,

meins LIKE vbap-meins,

END OF t_vbap,

BEGIN OF t_report,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

posnr LIKE vbap-posnr,

matnr LIKE vbap-matnr,

meins LIKE vbap-meins,

END OF t_report.

DATA:

li_vbak TYPE t_vbak OCCURS 0,

l_vbak TYPE t_vbak,

li_vbap TYPE t_vbap OCCURS 0,

l_vbap TYPE t_vbap,

li_report TYPE t_report OCCURS 0,

l_report TYPE t_report.

START-OF-SELECTION.

SELECT vbeln erdat

FROM vbak

INTO TABLE li_vbak PACKAGE SIZE 50.

SELECT posnr matnr meins

FROM vbap

INTO TABLE li_vbap

FOR ALL ENTRIES IN li_vbak

WHERE vbeln = li_vbak-vbeln.

IF sy-subrc = 0.

Now you have the two internal tables li_vbak and liÆ_vbap filled with data.
Do something with the data - remember to reinitialize internal tables

ENDIF.

ENDSELECT.

All of the product names here are trademarks of their respective companies. The site

www.allsaplinks.com no way affiliated with SAP AG. We have made every effort for the content

integrity. Information used on this site is at your own risk.

Re: Query takes long time

Former Member — Mon, 27 Aug 2007 15:15:23 GMT

Is there an idex on lips-vgbel and lips-vbpos in your system?

Rob

Re: Query takes long time

Former Member — Wed, 29 Aug 2007 05:55:56 GMT

HI EXPERTS,

i HAVE USED SELECT SINGLE INSTED OF SELECT..........ENDSELECT. BUT THERE IS NO IMPROVEMENT IN SPEED. WHAT ELSE i CAN DO. iTS URGENT. pLZ HELP.

tHANKS

kHAN

Re: Query takes long time

Former Member — Wed, 29 Aug 2007 12:57:43 GMT

Of course not. I repeat my question:

Is there an idex on lips-vgbel and lips-vbpos in your system?

Rob

Re: Query takes long time

Former Member — Thu, 30 Aug 2007 09:34:33 GMT

use loops rather than select

Re: Query takes long time

Former Member — Fri, 31 Aug 2007 05:37:30 GMT

ho rob,

I didnt know abt index on lips. how can i find. plz help.

Thanks.

Khan

Re: Query takes long time

Former Member — Fri, 31 Aug 2007 05:57:29 GMT

Put the variables on selection screen and put /h before executing. The program will run in debug mode. Press F5 and see exactly in which which queries it is taking time. Send me those queries, only then I will try to analyze the things and come up with the answer.

Regards,

Mallick

Re: Query takes long time

Former Member — Fri, 31 Aug 2007 10:09:58 GMT

I have now chage my code and now below is the new code. plz hav a look and send me suggestions if any modifications required.

SELECT BADAT LFDAT BANFN MATNR TXZ01 MENGE MEINS EBELN EBELP WERKS INTO (IT_EKKO-BADAT, IT_EKKO-LFDAT, IT_EKKO-BANFN, IT_EKKO-MATNR_EB,

IT_EKKO-TXZ01_EB, IT_EKKO-MENGE_EB, IT_EKKO-MEINS_EB, IT_EKKO-EBELN_EB, IT_EKKO-EBELP_EB, IT_EKKO-WERKS)

from eban

WHERE EBAN~BANFN IN S_BANFN AND LOEKZ = SPACE.

append it_ekko.

endselect.

clear it_ekko.

loop at it_ekko.

IF it_ekko-EBELN_EB <> SPACE.

SELECT SINGLE EBELN EBELP AEDAT MATNR TXZ01 MENGE MEINS NETPR

INTO (IT_EKKO-EBELN, IT_EKKO-EBELP, IT_EKKO-AEDAT, IT_EKKO-MATNR, IT_EKKO-TXZ01_EP, IT_EKKO-MENGE, IT_EKKO-MEINS,

IT_EKKO-NETPR)

FROM EKPO

WHERE EKPO~EBELN = IT_EKKO-EBELN_EB AND EKPO~EBELP = IT_EKKO-EBELP_EB and EKPO~LOEKZ = SPACE.

it_ekko-pend = IT_EKKO-MENGE_EB - IT_EKKO-MENGE.

SELECT single BEDAT RESWK INTO (IT_EKKO-BEDAT, IT_EKKO-RESWK)

FROM EKKO

WHERE EKKO~EBELN = IT_EKKO-EBELN.

modify it_ekko.

clear it_ekko.

else.

it_ekko-pend = IT_EKKO-MENGE_EB.

modify it_ekko.

endif.

endloop.

loop at it_ekko where ebeln <> space.

SELECT SINGLE ERDAT VBELN MATNR ARKTX LFIMG MEINS VGBEL INTO (IT_EKKO-ERDAT, IT_EKKO-VBELN, IT_EKKO-MATNR_LI,

IT_EKKO-ARKTX, IT_EKKO-LFIMG, IT_EKKO-MEINS_LI, IT_EKKO-VGBEL)

FROM LIPS

WHERE LIPS~VGBEL = IT_EKKO-EBELN AND LIPS~VGPOS = IT_EKKO-EBELP.

IF SY-SUBRC = 0.

LOOP AT IT_TRFRATE WHERE matnr_tr = it_ekko-matnr_li.

IT_EKKO-TRFRATE = IT_TRFRATE-TRFRATE.

modify it_ekko.

endloop.

endif.

modify it_ekko.

endloop.

loop at it_ekko where vbeln <> space.

select single umren from marm into vumren where marm~matnr = IT_EKKO-MATNR

and ( marm~meinh = 'L' or marm~meinh = 'KG' ).

select single umrez from marm into vumrez where marm~matnr = IT_EKKO-MATNR

and ( marm~meinh = 'L' or marm~meinh = 'KG' ).

compute vlfimg = ( IT_EKKO-LFIMG * vumren ) / vumrez.

IT_EKKO-NETPR = VLFIMG * IT_EKKO-TRFRATE.

CLEAR IT_EKKO-PEND_LI.

it_ekko-pend_li = IT_EKKO-MENGE_EB - IT_EKKO-LFIMG.

SELECT single WBSTK INTO (IT_EKKO-WBSTK)

FROM VBUK

WHERE VBUK~VBELN = IT_EKKO-VBELN.

modify it_ekko.

endloop.

********************************************************************************************

Thanks.

Khan

Re: Query takes long time

Former Member — Fri, 31 Aug 2007 11:00:05 GMT

hey y u r using [ into internal tablename with field.......] rather than its better to use into table <itab> right?????

and please avoid select with append and endselect..........

and please mention all the key fields ( order should be as it is in database table ) in where condition.......

after fetching the data from eban use for all entries with ebeln in where condition to fetch the data from ekpo....

may i know y u have started fetching from eban......rather than that first fetch ebeln from ekko ....based on ebeln in ekko ...fetch the data from ekpo......after that fetch the data from eban.......

this will result better performance

reward points if helpful

Re: Query takes long time

Former Member — Fri, 31 Aug 2007 11:37:09 GMT

hi,

According the requirement from Functional guy, as he enters PR no. it wil fetch PR detail from EBAN. for every PR there is STO no. then for STO no. it fetch data from EKPO. and at last according to STO it fetched Delivery Detail from LIPS. there are cases where STO doesnt created for any respective PR No. or Delivery doesnt there for any respective STO.

So I made the code according to that.

Thanks.

Khan

Re: Query takes long time

Former Member — Fri, 31 Aug 2007 13:05:18 GMT

go to the data dictionary; enter lips as t etable and press 'display'. On the display screen, there are a number of buttons; press 'indexes' and tell us if there is anything. In our system there is, but it looks like a "Z" index.

Rob