topic Re: Performance issues with Select Max in Application Development and Automation Discussions

Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 06:06:10 GMT

Hi guys,

I want to know which one is the best practice, in order to find several rows from a table that have tha newer date.

For example, i have a view that joins the tables keko and ckis, and i want to extract the following fields kstar, matnr_ckis, gpreis, menge and arbid when the date kadky is the most recent. I already try with a few methods, but they all seem to have a very poor performance.

this is how i tried:

1.-

SELECT MAX( kadky ) AS kadky kstar matnr_ckis gpreis menge arbid

INTO CORRESPONDING FIELDS OF TABLE it_cest

FROM zpp_keko_ckis

WHERE kadky eq w_kadky

and matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X'

GROUP BY kstar matnr_ckis gpreis menge arbid.

In this case, the data that i get have two dates instead of only the rows that have the last date.

2.-

SELECT MAX( kadky ) AS kadky kstar matnr_ckis gpreis menge arbid

INTO CORRESPONDING FIELDS OF TABLE it_cest

FROM zpp_keko_ckis

WHERE kadky eq (SELECT MAX( kadky ) AS kadky

INTO CORRESPONDING FIELDS OF TABLE it_cest

FROM zpp_keko_ckis

WHERE kadky eq w_kadky

and matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X' )

and matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X'.

This option doesn’t have a good performance

3.-

SELECT SINGLE MAX( kadky ) AS kadky

INTO w_kadky

FROM zpp_keko_ckis

WHERE matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X'.

SELECT kadky kstar matnr_ckis gpreis menge arbid peinh

INTO CORRESPONDING FIELDS OF TABLE it_cest

FROM zpp_keko_ckis

WHERE kadky EQ w_kadky

AND matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X'.

This option doesn’t have a good performance

I have to make this search for 50000 rows aprox, so, if I comment this part, the reports executes 5000 rows in less than 3 minutes, but which the options described already the performance is very poor and the report executes the same data in about 10 minutes.

Any suggestion will be highly appreciated.

Regards,

Eric

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 09:51:31 GMT

Hi Eric,

Could you please try like this also .

And if it is possible try to use "into table" .


SELECT MAX( kadky ) AS kadky kstar matnr_ckis gpreis menge arbid
<b>INTO  TABLE it_cest</b>
FROM zpp_keko_ckis
WHERE kadky eq (SELECT MAX( kadky ) AS kadky
                FROM zpp_keko_ckis
                WHERE kadky eq w_kadky
                and matnr_keko EQ w_mat
                AND werks EQ 'I15B'
                AND feh_sta EQ 'FR'
                AND freig EQ 'X' )
and matnr_keko EQ w_mat
AND werks EQ 'I15B'
AND feh_sta EQ 'FR'
AND freig EQ 'X'.

Hope this helps

Caglar<b></b>

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 14:00:12 GMT

HI Caglar,

I cannot use the "into table" statement, because i'm reading a few fields of the view.

Regards,

Eric

PD. In order to preserve the performance what options do i have?

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 14:04:14 GMT

hi,

avoid "in to corresponding fields". instead use "into table" for better performance.

create an internal table which will have the required fields and MOVE the data.

regards,

madhu

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 14:21:59 GMT

Hi Eric ,

removed

Caglar

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 14:25:40 GMT

Hi Eric,

Sorry for the wrong post, could you try like this , not sure but worths a try.


types : begin of i_tab, 
           kadky type ck_kadky,
           kstar   type  kstar,
           matnr_ckis type matnr ,
           gpreis type ck_kpt,
           menge type menge_d,
           arbid         type objecktid
           end    of i_tab.

data : it_tab type table of i_tab.



SELECT kadky kstar matnr_ckis gpreis menge arbid
INTO  TABLE it_tab
FROM zpp_keko_ckis
WHERE kadky eq (SELECT MAX( kadky ) AS kadky
                FROM zpp_keko_ckis
                WHERE  matnr_keko EQ w_mat
*       kadky eq w_kadky "  i'm not sure of this line,if necessary add your code        
                AND werks EQ 'I15B'
                AND feh_sta EQ 'FR'
                AND freig EQ 'X' )
and matnr_keko EQ w_mat
AND werks EQ 'I15B'
AND feh_sta EQ 'FR'
AND freig EQ 'X'.

Regards

Caglar

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 14:53:28 GMT

Hi Caglar,

I try what you suggested, but the performance is still pretty poor. Any other suggestion to increase it?.

Regards,

Eric

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 15:17:14 GMT

Hi guys,

I tried with the code describe above, and the performance increase a little. Any suggestions in order to increas it more?.

Regards,

Eric


SELECT kadky kstar matnr_ckis gpreis peinh menge arbid
  INTO TABLE it_cest
  FROM zpp_keko_ckis
  WHERE matnr_keko EQ w_mat
    AND werks      EQ 'I15B'
    AND feh_sta    EQ 'FR'
    AND freig      EQ 'X'.

  SORT it_cest BY kadky DESCENDING.

  READ TABLE it_cest ASSIGNING <wa_cest> INDEX 1.

  LOOP AT it_cest ASSIGNING <wa_cest> WHERE kadky EQ <wa_cest>-kadky.

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 15:33:46 GMT

Hi Eric,

If it is possible ,you can use additonal where clauses .

May I ask why did you use like this , because after your sort statement if you start to loop , it would be the same .

I think you don' t need this part.

READ TABLE it_cest ASSIGNING <wa_cest> INDEX 1.

Regards

Caglar

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 15:37:46 GMT

Hi Eric,

Try this code:

DATA: BEGING OF i_temp OCCURS 0,

kadky LIKE zpp_keko_ckis-kadky

END OF i_temp.

SELECT kadky

FROM zpp_keko_ckis

INTO TABLE I_TEMP

WHERE matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X'.

SORT i_temp BY kadky DESCENDING.

DELETE ADJACENT DUPLICATES FROM i_temp COMPARING kadky.

SELECT kadky kstar matnr_ckis gpreis menge arbid

INTO TABLE it_tab

FROM zpp_keko_ckis

FOR ALL ENTRIES IN i_temp

WHERE kadky EQ i_temp-kadky

and matnr_keko EQ w_mat

AND werks EQ 'I15B'

AND feh_sta EQ 'FR'

AND freig EQ 'X'.

FREE i_temp.

Chk if you are using the primary keys or index in the WHERE clause.

regards,

madhu

Re: Performance issues with Select Max

Former Member — Tue, 17 Apr 2007 16:45:35 GMT

Hi guys,

Thanks all for your support. Finally a found another table (MBEW) that has the date that i needed and added to the view.

Now the performance is pretty good.

Regards,

Eric