topic Re: Performance of SELECT statement in Application Development and Automation Discussions

Performance of SELECT statement

sudha_naik — Wed, 27 Aug 2008 13:21:00 GMT

Hello All,

The following statements are consuming huge time in my system. Could you please give me your inputs on tuning these queries

SELECT atinn FROM cabn INTO r_atinn-low

WHERE atkla = lc_chargrp_dim

AND anzst LE 8.

APPEND r_atinn.

ENDSELECT.

SELECT * FROM cabn INTO TABLE ij_cabn

FOR ALL ENTRIES IN r_atinn

WHERE atinn = r_atinn-low

ORDER BY PRIMARY KEY.

SELECT * FROM cabnt INTO TABLE ij_cabnt

FOR ALL ENTRIES IN r_atinn

WHERE atinn = r_atinn-low

ORDER BY PRIMARY KEY.

Thanks in advance

Sudha

Re: Performance of SELECT statement

Former Member — Wed, 27 Aug 2008 13:31:18 GMT

Hi,

This Select statement is taking much time because you are using SELECT .......ENDSELECT statement.

And also use If the internal table is not Initial.

SELECT atinn FROM cabn INTO r_atinn-low

WHERE atkla = lc_chargrp_dim

AND anzst LE 8.

APPEND r_atinn.

ENDSELECT.

if r_atinn is not Initial.

SELECT * FROM cabn INTO TABLE ij_cabn

FOR ALL ENTRIES IN r_atinn

WHERE atinn = r_atinn-low

ORDER BY PRIMARY KEY.

endif.

if r_atinn is not Initial.

SELECT * FROM cabnt INTO TABLE ij_cabnt

FOR ALL ENTRIES IN r_atinn

WHERE atinn = r_atinn-low

ORDER BY PRIMARY KEY.

endif.

i did not get why are you using order by Primary key, rather Sort the internal table after fetching the records in the Internal table.

regards,

Sujit

Re: Performance of SELECT statement

ThomasZloch — Wed, 27 Aug 2008 13:31:32 GMT

try this:

SELECT * FROM cabn INTO CORRESPONDING FIELDS OF TABLE it_target
         JOIN cabnt ON cabnt~atinn = cabn~atinn
                    AND cabnt~adzhl = cabn~adzhl
                    AND cabnt~spras = sy-langu
          WHERE cabn~atkla = lc_chargrp_dim
            AND cabn~anzst LE 8.

Define it_target only with those fields from CABN and CABNT that you really need for further processing. There is an index on CABN-ATKLA, so this should be OK.

Thomas

Re: Performance of SELECT statement

Former Member — Wed, 27 Aug 2008 13:35:20 GMT

Hi Sudha,

Try it like this:

DATA:
  ij_cabn like table of cabn,
  ij_cabnt like table of cabnt.

DATA:
  begin of wa_atinn,
    ATINN like CABN-ATINN,
  end of wa_atinn.

DATA:
  t_atinn like table of wa_atinn.


SELECT atinn FROM cabn INTO TABLE t_atinn
WHERE atkla = lc_chargrp_dim
AND anzst LE 8.


If t_atinn not initial.

SELECT * FROM cabn INTO TABLE ij_cabn
FOR ALL ENTRIES IN t_atinn
WHERE atinn EQ t_atinn-atinn
ORDER BY PRIMARY KEY.

SELECT * FROM cabnt INTO TABLE ij_cabnt
FOR ALL ENTRIES IN t_atinn
WHERE atinn EQ t_atinn-atinn
ORDER BY PRIMARY KEY.

endif.

With luck,

Pritam.

Re: Performance of SELECT statement

Former Member — Thu, 04 Sep 2008 11:29:03 GMT

Hi Sudha,

You can do the following things to improve the performance.

1)select all the key fields when you want to fetch entries from a table.

2) Do not use select and end select

3) Select the required fields alone and also in the order they are in the database

4) Sort the table and check if it is not initial before you use for all entries.