topic Re: Query Enhancement in Application Development and Automation Discussions

Query Enhancement

Former Member — Mon, 04 Jun 2007 07:03:30 GMT

Hello everyone,

I am trying to fetch data from LIKP and LIPS using INNER JOIN. The following are my selection criteria :

1. Shipping point/receiving point - LIKP-VSTEL (Mandatory)

2. Delivery Date - LIKP-LFDAT (Mandatory)

3. Receiving plant for deliveries - LIKP-KUNNR (Optional)

4. Ship-to party - LIKP-KUNNR (Optional)

5. Delivery Doc. No. - LIKP-VBELN (Optional)

The query I have written is as below :

SELECT P_{VBELN P}VSTEL P_{LFDAT P}KUNNR P_{LIFNR P}BLDAT

P~WADAT_IST

F_{VGBEL F}POSNR F_{MATNR F}LGORT F_{CHARG F}LFIMG

F_{MEINS F}VRKME F_{BRGEW F}GEWEI F_{LGMNG F}ARKTX

F_{SPART F}WERKS F~LFIMG

FROM LIKP AS P

INNER JOIN LIPS AS F

ON P_{VBELN EQ F}VBELN

AND P_{ERNAM EQ F}ERNAM

INTO CORRESPONDING FIELDS OF TABLE I_LIKP_LIPS

WHERE P~LFDAT IN S_DATE

AND P~VSTEL EQ S_VSTEL

AND P~VBELN IN S_DOCNO

AND P~KUNNR IN S_RWERKS

AND P~KUNNR IN S_CUSTNO

AND P~WADAT_IST EQ GIDATE

AND F~LFIMG NE 0.

Now the above query is taking a lot of time because the user does not feeds the primary key data i.e. VBELN as it is not suppose to be mandatory as per our requirement. There is also a secondary index on LFDAT but still the data access is slow.

Can anybody suggest me how to enhance this query performance.

THANX IN ADVANCE.......

Re: Query Enhancement

Former Member — Mon, 04 Jun 2007 07:08:29 GMT

HI,

you can create a view consisting of those tables and in that specify the joining conditions and then write the remaining conditions in select querry

Regards,

Naveen

Re: Query Enhancement

Former Member — Mon, 04 Jun 2007 07:09:27 GMT

Also if any FM for finding Delivery data using vstel and lfdat is there please suggest.

Re: Query Enhancement

Former Member — Mon, 04 Jun 2007 07:10:11 GMT

Change the sequence of the fields in the Where condition as

check once that this statement P~KUNNR IN S_RWERKS it may be

P~WERKS in S_RWERKS .

<b>

WHERE P~VBELN IN S_DOCNO

AND P~VSTEL EQ S_VSTEL

AND P~WADAT_IST EQ GIDATE

AND P~LFDAT IN S_DATE

AND P~KUNNR IN S_CUSTNO

AND P~WERKS IN S_RWERKS

AND AND F~LFIMG NE 0.</b>

Reward points if useful

Regards

Anji

Re: Query Enhancement

Former Member — Mon, 04 Jun 2007 07:12:37 GMT

HI,

First of all I suggest you to use for all entries..

Say your VBELN is not mandatory, It does not mean you neeed not use this,,

This selection criteria is for the user..

If he need only particular VBELN he gives the Vblen numbers,..

else if he leaves blank then all the VBLEN from database will be as output..

First check whether you are fetching data from LIKP in sequence..

see the LIKP table for seqeunce..

Then use for all entries for LIPS..

Eg:

select VBELN VSTEL ..... (this field should be in sequence as in LIKP table)

into it_likp (define all your fields in it_likp table which you are fetching from LIKP)

from LIKP

where vbeln = s_vbeln and VSTEL = s_VSTEL...

Now

SELECT VBLEN POSNR WGBEL...

into table it_LIPS (define your LIPS fileds in this table)

from lips

for all entries in it_LIKP

where vbeln = it_likp-vbeln.

This will increase your performance

rewards if useful..

regards,

nazeer

Re: Query Enhancement

pramodv — Mon, 04 Jun 2007 07:14:55 GMT

Hi,

Make us of Logical Database, it gives u the option of dynamic selection screen and the join conditions will be automatically taken care of. Your program code also reduces.

Regards,

Pramod

Re: Query Enhancement

Former Member — Thu, 14 Jun 2007 05:18:07 GMT