topic Re: Select stmt taking long time. in Application Development and Automation Discussions

Select stmt taking long time.

Former Member — Fri, 27 Oct 2006 01:03:06 GMT

Hi,

This select stmt is taking very long time to execute. Can you please guide me to improve it.

SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI VBAK~NETWR

FROM LIKP

INNER JOIN LIPS ON LIKP_{VBELN = LIPS}VBELN

INNER JOIN VBAK ON LIPS_{VGBEL = VBAK}VBELN

INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT

WHERE LIKP~LFDAT IN S_LFDAT

AND LIKP~KUNNR IN S_KUNNR

AND LIKP~LIFSK = P_LIFSK.

Thanks

Veni.

Re: Select stmt taking long time.

venkata_ramisetti — Fri, 27 Oct 2006 01:12:23 GMT

Hi,

YOu can modify the code like below.

Create two temparary tables IT_OUTPUT_TEMP & IT_OUTPUT_vbak.

Get the final internal table IT_OUTPUT from these two.

SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI

lips~vgbel

FROM LIKP INNER JOIN LIPS

ON LIKP_{VBELN = LIPS}VBELN

INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT_TEMP

WHERE LIKP~LFDAT IN S_LFDAT

AND LIKP~KUNNR IN S_KUNNR

AND LIKP~LIFSK = P_LIFSK

if not i_output_temp[] is initial.

SELECT vbak_{VBELN VBAK}NETWR

into it_output_vbak

from VBAK

for all entries in i_output_temp

where vbeln = i_output_temp~VGBEL.

sort i_output_temp by vbeln.

endif.

loop at it_output_temp.

move-corresponding it_output_temp to it_output.

read table it_output_vbak with key vbeln = it_output_temp-vgbel binary search.

if sy-subrc = 0.

it_output-netwr = it_output_vbak-netwr.

endif.

append it_output.

endloop.

Thanks,

Ramakrishna

Re: Select stmt taking long time.

gopi_narendra — Fri, 27 Oct 2006 01:12:30 GMT

Use for all entries option.

SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI LIPS~VGBEL

FROM LIKP INNER JOIN LIPS ON LIKP_{VBELN = LIPS}VBELN

INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT

WHERE LIKP~LFDAT IN S_LFDAT

AND LIKP~KUNNR IN S_KUNNR.

IF NOT IT_OUTPUT[] IS INITIAL.

SELECT NETWR

FROM VBAK INNER JOIN LIPS

ON LIPS_{VGBEL = VBAK}VBELN

FOR ALL ENTRIES IN IT_OUTPUT

WHERE VGBEL = IT_OUTPUT-VGBEL

AND LIKP~LIFSK = P_LIFSK.

ENDIF.

Regards

- Gopi

Re: Select stmt taking long time.

Former Member — Fri, 27 Oct 2006 01:15:17 GMT

Hi,

Looks like there is no index on the fields that are there in the WHERE clause fields LFDAT , KUNNR , LIFSK..

Probably you can create an index on the field KUNNR in the table LIKP...If S_KUNNR is mandatory..

Thanks,

Naren

Re: Select stmt taking long time.

former_member186741 — Fri, 27 Oct 2006 01:34:32 GMT

I agree with Naren taht it's the lack of index that is killing you. Because of the smaller amount of data in VBAK it might be worth creating an index on VBAK and rewriting the select slightly,

SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI VBAK~NETWR

FROM vbak

INNER JOIN lips ON LIPS_{VGBEL = VBAK}VBELN

INNER JOIN LIkp ON LIKP_{VBELN = LIPS}VBELN

INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT

WHERE vbak~kunnr IN S_KUNNR

and LIKP~LFDAT IN S_LFDAT

AND LIKP~LIFSK = P_LIFSK.

Re: Select stmt taking long time.

Former Member — Fri, 27 Oct 2006 01:44:18 GMT

Hi,

Thank you all for your help. I donot know, how to create Index. Please help me rewrite the select stmt.

Thank you,

Veni.

Re: Select stmt taking long time.

former_member186741 — Fri, 27 Oct 2006 02:21:23 GMT

speak to someone at your site who knows how to create an index, maybe a basis person or more experienced abapper. However you code the select it will run slowly. You might improve it slightly but basically it is reading a lot of data.

You could try my select from the earlier post... it might be better than the one based on LIKP as VBAK is smaller.

Re: Select stmt taking long time.

Former Member — Fri, 27 Oct 2006 02:51:50 GMT

1) Please use 'INTO TABLE IT_OUTPUT' instead of using

';INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT'.

2) Furthermore, use posnr when selecting comparing record within likp, lips and vbak.

3) Thirdly, please put some key field comparison in the where condition, for example: likp~vbeln IN s_vbeln. As you dont comparing the key field when selecting record, it will only cause your program to run really slow.

4) You may create an index in SE11 for table likp.

Re: Select stmt taking long time.

Former Member — Fri, 27 Oct 2006 03:00:14 GMT

Be aware that if you create an index on an SAP table, there will be extra overhead every time the table is updated. Also, the new index will take up table space. Also, the users may at some time in the future decide that they don't need the report you are writing, but the index will probably stay.

I wouldn't create an index on an SAP table to speed up a single select statement. It's better to look for a programming solution.

Rob

Re: Select stmt taking long time.

Former Member — Fri, 27 Oct 2006 04:11:06 GMT

>> SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI VBAK~NETWR

>> FROM LIKP

>> INNER JOIN LIPS ON LIKP_{VBELN = LIPS}VBELN

>> INNER JOIN VBAK ON LIPS_{VGBEL = VBAK}VBELN

>> INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT

>> WHERE LIKP~LFDAT IN S_LFDAT

>> AND LIKP~KUNNR IN S_KUNNR

>> AND LIKP~LIFSK = P_LIFSK.

Based on the select that you have in your post, this is what I see.

As output, you only need fields from LIKP and only one field from VBAK.

All your fields on the WHERE clause is on LIKP data.

You only need LIPS for the Sales document number.

Can you please make a copy of your program and try this below code?

It would be interesting to see if the extra vgbel sort would help.


TYPES:
  BEGIN OF TY_LIKP,
    VBELN TYPE LIKP-VBELN,
    LFDAT TYPE LIKP-LFDAT,
    KUNNR TYPE LIKP-KUNNR,
    KNKLI TYPE LIKP-KNKLI,
    NETWR TYPE VBAK-NETWR,
    VGBEL TYPE LIPS-VGBEL,
  END OF TY_LIKP.

DATA:
  I_LIKP TYPE STANDARD TABLE OF TY_LIKP
    INITIAL SIZE 0
    WITH HEADER LINE,

  BEGIN OF I_VGBEL OCCURS 0,
    VGBEL TYPE LIPS-VGBEL,
  END OF I_VGBEL,

  BEGIN OF I_VBAK OCCURS 0,
    VBELN TYPE VBAK-VBELN,
    NETWR TYPE VBAK-NETWR,
  END OF I_VBAK.

FIELD-SYMBOLS:
  <FS_LIKP> TYPE TY_LIKP.

SELECT LIKP~VBELN LIKP~LFDAT LIKP~KUNNR LIKP~KNKLI LIPS~VGBEL
  INTO CORRESPONDING FIELDS OF TABLE I_LIKP
  FROM LIKP
  JOIN LIPS ON LIPS~VBELN EQ LIKP~VBELN
  WHERE LIKP~LFDAT IN S_LFDAT
  AND   LIKP~KUNNR IN S_KUNNR
  AND   LIKP~LIFSK EQ P_LIFSK.

IF I_LIKP[] IS NOT INITIAL.

  LOOP AT I_LIKP ASSIGNING <FS_LIKP>.
    CLEAR I_VGBEL.
    I_VGBEL-VGBEL = <FS_LIKP>-VGBEL.
    APPEND I_VGBEL.
  ENDLOOP.

  SORT I_VGBEL.
  DELETE ADJACENT DUPLICATES FROM I_VGBEL.

  IF I_VGBEL[] IS NOT INITIAL.

    SELECT VBELN NETWR INTO TABLE I_VBAK
      FROM VBAK
      FOR ALL ENTRIES IN I_VGBEL
      WHERE VBELN EQ I_VGBEL-VGBEL.

    SORT I_VBAK BY VBELN.

    FREE I_VGBEL.

    LOOP AT I_LIKP ASSIGNING <FS_LIKP>.
        CLEAR I_VBAK.
        READ TABLE I_VBAK WITH KEY VBELN = <FS_LIKP>-VGBEL
                                   BINARY SEARCH.
        IF SY-SUBRC EQ 0.
          <FS_LIKP>-NETWR = I_VBAK-NETWR.
* Note that you don't need to do the Modify statement here
* because of the field symbol
        ENDIF.
      ENDIF.
    ENDLOOP.

* Just ignore I_LIKP-VGBEL from all your reporting needs

  ENDIF.
ENDIF.

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:08:58 GMT

Hi,

I included vbeln in where clause, but not much difference, it is still taking lot of time. Can I change this any more without creating index.

SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI VBAK~NETWR

FROM LIKP

INNER JOIN LIPS ON LIKP_{VBELN = LIPS}VBELN

INNER JOIN VBAK ON LIPS_{VGBEL = VBAK}VBELN

INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT

WHERE LIKP~VBELN IN S_VBELN

AND LIKP~LFDAT IN S_LFDAT

AND LIKP~KUNNR IN S_KUNNR

AND LIKP~LIFSK = P_LIFSK.

Thank you

Veni.

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:18:47 GMT

Is S_VBELN empty?

How long does it take if you put in exactly one document number into this select-option?

Rob

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:26:05 GMT

Hi Rob,

It took 6 min to execute and 3 records came in the output. I gave delivery date and delivery block. Delivery number and ship to party were empty.

Thank you,

Veni.

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:29:02 GMT

The point of putting the delivery document on the selection screen is that this must be entered so that you can use an index.

As a test, put in one deleivery and see how long that takes.

Rob

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:34:53 GMT

Hi Rob,

It came very fast. But If user keeps this blank, then it will take long time again right. So do you think I should make this field obligatory?

Please advise me.

Thank you

Veni.

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:37:04 GMT

If the user agrees, then yes, make it mandatory.

Rob

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:44:50 GMT

Hi Rob,

Incase if they say no, is there any way I can change this stmt so that it takes less time.

SELECT LIKP_{VBELN LIKP}LFDAT LIKP_{KUNNR LIKP}KNKLI VBAK~NETWR

FROM LIKP

INNER JOIN LIPS ON LIKP_{VBELN = LIPS}VBELN

INNER JOIN VBAK ON LIPS_{VGBEL = VBAK}VBELN

INTO CORRESPONDING FIELDS OF TABLE IT_OUTPUT

WHERE LIKP~VBELN IN S_VBELN

AND LIKP~LFDAT IN S_LFDAT

AND LIKP~KUNNR IN S_KUNNR

AND LIKP~LIFSK = P_LIFSK.

Thanks

Veni.

Re: Select stmt taking long time.

Former Member — Mon, 30 Oct 2006 17:51:05 GMT

Not the way it is.

If the user is willing to add the order date, then yes.

Rob