topic Re: Performance Problem in Select query in Application Development and Automation Discussions

Performance Problem in Select query

Former Member — Fri, 10 Oct 2008 11:43:05 GMT

Hi,

I have performance Problem in following Select Query :

SELECT VBELN POSNR LFIMG VRKME VGBEL VGPOS

FROM LIPS INTO CORRESPONDING FIELDS OF TABLE GT_LIPS

FOR ALL ENTRIES IN GT_EKPO1

WHERE VGBEL = GT_EKPO1-EBELN

AND VGPOS = GT_EKPO1-EBELP.

as per trace i have analysed that it is fetch the complete table scan from the LIPS table and table contants almost 3 lakh records.

Kindly Suggest what we can do to optimize this query.

Regards,

Harsh

Re: Performance Problem in Select query

valter_oliveira — Fri, 10 Oct 2008 12:05:11 GMT

It happens because LIPS doesn't have an index to VGBEL (and VGPOS).

Since I think you cannot use VBFA to make an alternative selection you probably have to create an index in LIPS with field MANDT-VGBEL (or vgbel and vgpos, but vgbel is enough I think).

Regards,

Valter Oliveira.

Re: Performance Problem in Select query

Former Member — Fri, 10 Oct 2008 12:08:17 GMT

types: begin of line,

vbeln type lips-vbeln

posnr type lips-posnr

lfimg type lips-lfimg

vrkme type lips-vrkme

vgbel type lips- vgbel

vgpos type lips-vgpos

end of line.

data: itab type standard table of line,

wa type line.

IF GT_EKPO1[] IS NOT INITIAL.

SELECT VBELN POSNR LFIMG VRKME VGBEL VGPOS

FROM LIPS INTO TABLE ITAB

FOR ALL ENTRIES IN GT_EKPO1

WHERE VGBEL = GT_EKPO1-EBELN

AND VGPOS = GT_EKPO1-EBELP.

ENDIF.

Re: Performance Problem in Select query

valter_oliveira — Fri, 10 Oct 2008 12:14:56 GMT

INTO CORRESPONDING FIELDS OF TABLE is not a performance problem ! You do what Yogesh proposed (it's better) but won't solve your problem tough.

Regards,

Valter Oliveira.

Re: Performance Problem in Select query

Former Member — Fri, 10 Oct 2008 13:17:45 GMT

You should be able to use the sales document flow table (VBFA) to do this.

Rob

Re: Performance Problem in Select query

valter_oliveira — Fri, 10 Oct 2008 18:03:59 GMT

You should be able to use the sales document flow table (VBFA) to do this

Since we are dealing with non SD documents i'm not sure if you can, but I don't have a SAP system here to check it right now.

Regards,

Valter Oliveira.

Re: Performance Problem in Select query

Former Member — Fri, 10 Oct 2008 18:21:36 GMT


> Since we are dealing with non SD documents i'm not sure if you can, but I don't have a SAP system here to check it right now.

Hmmm...

I missed the part about purchase orders, so maybe note 800219 is applicable.

Rob

Re: Performance Problem in Select query

Former Member — Tue, 14 Oct 2008 09:32:36 GMT

Hi,

The comment made by Valter Oliveira is right. You have to create secondary index for LIPS

Re: Performance Problem in Select query

Former Member — Wed, 15 Oct 2008 11:23:05 GMT

thanks all

my question is answed

thanks bye

Re: Performance Problem in Select query

Former Member — Wed, 15 Oct 2008 17:43:27 GMT

Harsh,

It sure would help others if you could mention how the issue was solved too. Now, thats the idea behind SDN, isnt it?

Regards,

Rashmi.