Performance issues with Select Max

[Former Member]

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

[Former Member]

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>

[Former Member]

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?

[Former Member]

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

[Former Member]

Hi Eric ,

removed

Caglar

[Former Member]

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

[Former Member]

Hi Caglar,

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

Regards,

Eric

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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