topic Re: Performance Issue in Select Query ? in Application Development and Automation Discussions

Performance Issue in Select Query ?

Former Member — Thu, 07 Oct 2010 06:21:21 GMT

Hi All ,

Can anyone suggest the correct us of the Select query in order to avoid performance constraint.

 SELECT likp~vkorg     "Sales organization
         likp~vstel     "Shipping point/receiving point
         likp~vbeln     "Delivery
         lips~posnr     "Delivery item
         likp~lfart     "Delivery type
         likp~wadat     "Planned goods movement date
         likp~podat     "Date (proof of delivery)
         likp~kunnr     "Ship-to party
         likp~erdat     "Date on which the record was created
         likp~lfdat     "Delivery date
         likp~bolnr     "Actual POD date
         likp~wadat_ist "Actual goods movement date
         likp~waerk     "SD document currency
         lips~matnr     "Material number
         lips~lfimg     "Actual quantity delivered (in sales units)
         lips~vrkme     "Sales unit
         lips~vgbel     "Document number of the reference document
         lips~vgpos     "Item number of the reference item
    FROM ( likp AS likp INNER JOIN lips AS lips ON
           likp~vbeln = lips~vbeln )
    INTO CORRESPONDING FIELDS OF TABLE gt_do
   WHERE likp~vkorg = p_vkorg
     AND likp~vstel IN s_vstel
     AND likp~lfart IN s_lfart
     AND likp~erdat IN s_erdat
     AND likp~wadat IN s_wadat
     AND likp~podat IN s_podat.

The Above c ode takes tooo much time to ftch the data .

Can it be corrected inorder to get teh data as fast as possible.

Regards,

Saurabh

Re: Performance Issue in Select Query ?

Former Member — Thu, 07 Oct 2010 09:40:16 GMT

Saurabh,

instead of join try to use for all entries

Thanks

Bala Duvvuri

Moderator message: please ignore the above advice from someone who hasn't read* *even though it's a sticky on this forum

Edited by: Matt on Oct 7, 2010 11:55 AM

Re: Performance Issue in Select Query ?

Rui_Dantas — Thu, 07 Oct 2010 09:48:12 GMT

Hi,

First, please ignore the above suggestion to use for all entries.

To optimize a query you need to know how the data is restricted. From what you've shown we know that you are selecting data from one sales organization ( = p_vkorg) but nothing more.

What are the contents of s_vstel?

What are the contents of s_lfart?

What are the contents of s_erdat?

What are the contents of s_wadat?

What are the contents of s_podat?

Rui Dantas

Re: Performance Issue in Select Query ?

Former Member — Thu, 07 Oct 2010 10:48:57 GMT

Hi Saurabhgoels ,

1) The fields which your are fetching is not in a proper order as they are in table. I have corrected their order...see below.

2) Dont use into corresponding fields of itab, instead use into fields for better performance. So declare an internal table based on the order of fields of your select query.

3) Even in the where condition, the fields are not in proper order and they are in the table. Also I have corrected their order.

Select likp~VBELN

likp~ERDAT

likp~VSTEL

likp~VKORG

likp~LFART

likp~WADAT

likp~LFDAT

likp~KUNNR

likp~WAERK

likp~BOLNR

likp~WADAT_IST

likp~PODAT

lips~POSNR

lips~MATNR

lips~LFIMG

lips~VRKME

lips~VGBEL

lips~VGPOS

FROM ( likp AS likp INNER JOIN lips AS lips ON

likp_{vbeln = lips}vbeln )

INTO TABLE gt_do

WHERE likp~erdat IN s_erdat

AND likp~vstel IN s_vstel

AND likp~lfart IN s_lfart

AND likp~vkorg = p_vkorg

AND likp~wadat IN s_wadat

AND likp~podat IN s_podat.

This will definitely improve the performance of your select query.

Regards,

Md Ziauddin

Re: Performance Issue in Select Query ?

Rui_Dantas — Thu, 07 Oct 2010 11:09:25 GMT


> 1)  The fields which your are fetching is not in a proper order as they are in table.  I have corrected their order...see below.
> 2)  Dont use into corresponding fields of itab, instead use into fields for better performance. So declare an internal table based on the order of fields of your select query.
> 3)  Even in the where condition, the fields are not in proper order and they are in the table.  Also I have corrected their order.
> This will definitely improve the performance of your select query.
> Md Ziauddin

Again, please ignore there recommendations. They all have very minor or no impact on performance.

Re: Performance Issue in Select Query ?

ravi_lanjewar — Thu, 07 Oct 2010 13:03:40 GMT

Hi,

Split your query in 2 part

1) First Read data from table LIKP into internal table

2) Check where data read from likp table is not empty

3) Read data from lips using for all entries of data likp which priviouslly read.

Some cases join condition is not gives better performance.

If likp table has large volume of data then create the index otherwise no need to create the index.

Re: Performance Issue in Select Query ?

Former Member — Thu, 07 Oct 2010 14:32:02 GMT

as written above the only useful comment is:

What is in the IN-conditions


   WHERE likp~vkorg = p_vkorg
     AND likp~vstel IN s_vstel
     AND likp~lfart IN s_lfart
     AND likp~erdat IN s_erdat
     AND likp~wadat IN s_wadat
     AND likp~podat IN s_podat.

With selective conditions it will be fast, with not selective conditions it will be slow.

Re: Performance Issue in Select Query ?

Former Member — Fri, 08 Oct 2010 10:17:09 GMT

Hi..

split the code and check..it may be faster than ur join.

SELECT likp~vkorg "Sales organization

likp~vstel "Shipping point/receiving point

likp~vbeln "Delivery

likp~lfart "Delivery type

likp~wadat "Planned goods movement date

likp~podat "Date (proof of delivery)

likp~kunnr "Ship-to party

likp~erdat "Date on which the record was created

likp~lfdat "Delivery date

likp~bolnr "Actual POD date

likp~wadat_ist "Actual goods movement date

likp~waerk "SD document currency

FROM likp INTO TABLE <Table 1>

WHERE likp~vkorg = p_vkorg

AND likp~vstel IN s_vstel

AND likp~lfart IN s_lfart

AND likp~erdat IN s_erdat

AND likp~wadat IN s_wadat

AND likp~podat IN s_podat.

and second query for getting the values from LIPS

SELECT lips~vbeln "Delivery

lips~posnr "Delivery item

lips~matnr "Material number

lips~lfimg "Actual quantity delivered (in sales units)

lips~vrkme "Sales unit

lips~vgbel "Document number of the reference document

lips~vgpos "Item number of the reference item

FROM lips

INTO TABLE <table 2> WHERE likp~vkorg = p_vkorg

for all entries in <Table 1>

where vbeln = table1-vbel.

else..

in ur original querry try to avoid INTO CORRESPONDING FIELDS..IT MAY HELP A LITTLE BIT..and in your query you havent used key field..so it will take lot time as that delivery table has lot of entries

THANKS

SHANKAR

Edited by: shankar on Oct 8, 2010 12:18 PM

Edited by: shankar on Oct 8, 2010 12:19 PM