topic Re: Performance Tuning 'Runtime Error' on Select statement in Application Development and Automation Discussions

Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 08:08:48 GMT

Hi Experts,

Good Day!

I would like to ask some help regarding a custom program that will encounter 'Runtime Error' on the below codes on how to perform performance tunning especially number 1.

SELECT A~VBELN A~ERDAT A~AUART A~VKORG A~VTWEG A~SPART A~VDATU
         A~KUNNR B~POSNR B~MATNR B~ARKTX B~ABGRU B~KWMENG B~VRKME
         B~WERKS B~VSTEL B~ROUTE
      FROM VBAK AS A INNER JOIN VBAP AS B ON A~VBELN EQ B~VBELN
                     INNER JOIN VBEP AS C ON A~VBELN EQ C~VBELN
                       AND B~POSNR EQ C~POSNR
        INTO CORRESPONDING FIELDS OF TABLE I_DATA_TAB
          WHERE A~VBELN  IN S_VBELN
            AND A~VKORG  IN S_VKORG
            AND A~AUART  IN S_AUART
            AND A~VTWEG  IN S_VTWEG
            AND A~SPART  IN S_SPART
            AND A~VDATU  IN S_VDATU
            AND A~KUNNR  IN S_KUNNRD
            AND B~MATNR  IN S_MATNR
            AND B~KWMENG IN S_KWMENG
            AND B~VRKME  IN S_VRKME
            AND B~WERKS  IN S_WERKS
            AND C~EDATU  IN S_VDATU.

SELECT VBELN FROM LIKP INTO LIKP-VBELN
              WHERE LFDAT IN S_VDATU
                AND VKORG IN S_VKORG
                AND LFART EQ 'YSTD'
                AND KUNNR IN S_KUNNRP
                AND KUNAG IN S_KUNNRD

    SELECT VBELN POSNR LFIMG MATNR WERKS
       FROM LIPS INTO (LIPS-VBELN, LIPS-POSNR, DISPLAY_TAB-DEL_QTY,
                       LIPS-MATNR, LIPS-WERKS)
             WHERE VBELN EQ LIKP-VBELN
               AND MATNR IN S_MATNR
               AND VTWEG IN S_VTWEG
               AND SPART IN S_SPART
               AND WERKS IN S_WERKS.    

     ENDSELECT.
  ENDSELECT.

SELECT DELIVERY POSNR MATNR PODLFIMG FROM T9YPODI INTO
    (T9YPODI-DELIVERY, T9YPODI-POSNR, T9YPODI-MATNR, T9YPODI-PODLFIMG)
                       WHERE MATNR   IN S_MATNR
                         AND PODDATE IN S_VDATU.

Answer's will be a great help.

~Thank You,

Lourd

Edited by: Lourd06 on Oct 23, 2009 10:32 AM

Moderator message - Welcome to SCN.

Moderator message - Please see before posting. You're in the driver's seat here. It's up to you to do some analysis before expecting that people can halp you. - post locked And please use code tags.

Edited by: Rob Burbank on Oct 23, 2009 9:13 AM

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 08:41:29 GMT

Hi,

After SELECT statement it shuold end with ENDSELECT......

SELECT A_{VBELN A}ERDAT A_{AUART A}VKORG A_{VTWEG A}SPART A~VDATU

A_{KUNNR B}POSNR B_{MATNR B}ARKTX B_{ABGRU B}KWMENG B~VRKME

B_{WERKS B}VSTEL B~ROUTE

FROM VBAK AS A INNER JOIN VBAP AS B ON A_{VBELN EQ B}VBELN

INNER JOIN VBEP AS C ON A_{VBELN EQ C}VBELN

AND B_{POSNR EQ C}POSNR

INTO CORRESPONDING FIELDS OF TABLE I_DATA_TAB

WHERE A~VBELN IN S_VBELN

AND A~VKORG IN S_VKORG

AND A~AUART IN S_AUART

AND A~VTWEG IN S_VTWEG

AND A~SPART IN S_SPART

AND A~VDATU IN S_VDATU

AND A~KUNNR IN S_KUNNRD

AND B~MATNR IN S_MATNR

AND B~KWMENG IN S_KWMENG

AND B~VRKME IN S_VRKME

AND B~WERKS IN S_WERKS

AND C~EDATU IN S_VDATU.

ENDSELECT.

SELECT VBELN FROM LIKP INTO LIKP-VBELN

WHERE LFDAT IN S_VDATU

AND VKORG IN S_VKORG

AND LFART EQ 'YSTD'

AND KUNNR IN S_KUNNRP

AND KUNAG IN S_KUNNRD.

ENDSELECT.

SELECT VBELN POSNR LFIMG MATNR WERKS

FROM LIPS INTO (LIPS-VBELN, LIPS-POSNR, DISPLAY_TAB-DEL_QTY,

LIPS-MATNR, LIPS-WERKS)

WHERE VBELN EQ LIKP-VBELN

AND MATNR IN S_MATNR

AND VTWEG IN S_VTWEG

AND SPART IN S_SPART

AND WERKS IN S_WERKS.

ENDSELECT.

SELECT DELIVERY POSNR MATNR PODLFIMG FROM T9YPODI INTO

(T9YPODI-DELIVERY, T9YPODI-POSNR, T9YPODI-MATNR, T9YPODI-PODLFIMG)

WHERE MATNR IN S_MATNR

AND PODDATE IN S_VDATU.

ENDSELECT.

Regards,

Shankar.

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 08:50:57 GMT

Hi Shankar,

What we want to achieve is to have the retrieval/Select statement undergo performance tuning. And I think the suggestions you gave will still cause a 'Runtime Error' in the program. Thanks!

~Thank you

Lourd

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 08:54:02 GMT

Kindly go to TCODE ST22 and analysis the errors. And which SELECT statement returns error or all of them?

REgards,

Thien

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 08:56:41 GMT

Hi,

If you can change your code from iNNER JOIN....to FOR ALL ENTRIES.AVOID using ENDSELECT.Even if you need to check for some condition check,always use ITAB.

select f1 f2

from VBAK

into table it_vbak

where vbeln in so-vbeln

if it_vbak is not initial.

select f1 f2

from VBAP

into tble it_vbap

for all entries in it_vbak

where vbeln eq it_vbak-vbeln.

endif.

hope this helps,

thanks

Edited by: p317980 on Oct 23, 2009 10:57 AM

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 09:06:59 GMT

you know that this thread will be closed soon.

Runtime error has a reason please check in ST22, is it wrong or a time-out?

Check one by one in ST05, see sticky thread above, come back with result.

And please never add or read this 'Replace JOIN by FAE'

Siegfried

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 09:14:16 GMT

Hi All,

We've checked the transaction ST22 it is TIME OUT. I really need your help on this the program will dump in number 1 Select statement. Can you help me perform a performance tunning.

In transaction ST22

Runtime Errors TIME_OUT

Date and Time 21.10.2009 08:51:33

Short text

Time limit exceeded.

What happened?

The program "ZV0PSR10" has exceeded the maximum permitted runtime without

interruption and has therefore been terminated.

What can you do?

Note down which actions and inputs caused the error.

To process the problem further, contact you SAP system

administrator.

Using Transaction ST22 for ABAP Dump Analysis, you can look

at and manage termination messages, and you can also

keep them for a long time.

Error analysis

After a specific time, the program is terminated to make the work area

available to other users who may be waiting.

This is to prevent a work area being blocked unnecessarily long by, for

example:

- Endless loops (DO, WHILE, ...),

- Database accesses with a large result set

- Database accesses without a suitable index (full table scan)

The maximum runtime of a program is limited by the system profile

parameter "rdisp/max_wprun_time". The current setting is 1200 seconds. If this

time limit is

exceeded, the system attempts to cancel any running SQL statement or

signals the ABAP processor to stop the running program. Then the system

waits another 60 seconds maximum. If the program is then still active,

the work process is restarted.

~Thank you

Lourd

Edited by: Lourd06 on Oct 23, 2009 11:22 AM

Edited by: Lourd06 on Oct 23, 2009 11:33 AM

Re: Performance Tuning 'Runtime Error' on Select statement

ThomasZloch — Fri, 23 Oct 2009 09:55:16 GMT

As Siegfried said, this will probably be closed, because you should also search for existing similar discussions here.

Your selections contain all necessary key fields, BUT depending on how the S_... selection options are filled, you will see a completely different response time. E.g. fill one single value in S_VBELN, and your first select will be lightning quick, I bet.

Thomas

Re: Performance Tuning 'Runtime Error' on Select statement

Former Member — Fri, 23 Oct 2009 11:52:55 GMT

HI Siegfried,

Why would you not use a FAE?

I have just coded a program for SD module,using the same set of TABLES,it works just fine...Please advice