Performance select statement

[Former Member]

SELECT * FROM CATSPS INTO TABLE TCATSPS

WHERE TRANSFER = 'X' .

SELECT RUECK

STOKZ

CATSBELNR

FROM AFRU

INTO table T_AFRU

for all entries in tcatsps

WHERE CATSBELNR = TCATSPS-BELNR

AND STOKZ = TCATSPS-STOKZ.

In the above statement, Tcatsps internal table is getting around 3 Lakh records. Becos of which when i run the second select statement, time is taken for ever.

Can I improve the performance statment of 2nd select statemtn in any other effective way.

Thanks

Regds

Venu

[Former Member]

Hi,

In the 1st select statement, select only necessary fields instead of select *.

eg.

SELECT F1 F2

from Table1

Appending table <internal table - itab1>

WHERE <Condition >.

If sy-subrc = 0.

SELECT F3 F4 F5

from Table2

Appending table <Internal Table - itab2>

FOR ALL ENTRIES IN itab1

WHERE <Condition>.

endif.

Reward if helps.

Cheers

[Former Member]

hi,

Make the changes in bold. It removes the duplciates and hence reduces the time of exceution.

SELECT * FROM CATSPS INTO TABLE TCATSPS

WHERE TRANSFER = 'X' .

<b>

if not t_catsps[] is initial.

tcatsps_temp[] = tcatsps[].

sort tcatsps_temp by stokz belnr.

delete adjacent duplicates from tcatsps_temp comparing stokz belnr.

</b>

SELECT RUECK

STOKZ

CATSBELNR

FROM AFRU

INTO table T_AFRU

for all entries in <b>tcatsps_temp</b>

WHERE CATSBELNR =<b> TCATSPS_temp-BELNR</b>

AND STOKZ = <b>TCATSPS-STOKZ</b>.

<b>endif.</b>

Regards,

Sailaja.

[former_member404244]

Hi,

I would suggest u don't use SELECT *.

types : begin of t_struct,

field1

field2

.....

.....

.....

end of t_struct.

data : i_stryct type standard table of t_struct.

now write the select query for the field values u want to retrieve...

seconly when u r using for all entries u remember that u have to check for initiality of the master table..

in ur case.

u have to use.

also sort the internal table by belnr STOKZ

after ur first select statement.

then delete the adjacent duplicate entries from tcatsps

if not tcatsps[] is initial.

SELECT RUECK

STOKZ

CATSBELNR

FROM AFRU

INTO table T_AFRU

for all entries in tcatsps

WHERE CATSBELNR = TCATSPS-BELNR

AND STOKZ = TCATSPS-STOKZ.

endif.

regards,

nagaraj

[Former Member]

I would recomment two ways.

1)

For the first statment mention the fields names which are only needed and put those to an internal table.

SELECT f1 f2 f3 FROM CATSPS INTO TABLE itab_TCATSPS
WHERE TRANSFER = 'X' .

Before using <b>FOR ALL ENTRIES</b> always use

If NOT itab_TCATSPS[] IS INITIAL.

2) Try using <b>INNER JOIN</b> with both tables.

SELECT T1~F1 T1~F2 T1~F3 AF~RUECK AF~STOKZ AF~CATSBELNR
      FROM AFRU AS AF INNER JOIN T1 AS TCATSPS 
      ON  T1~BELNR = AF~CATSBELNR AND T1~STOKZ = AF~STOKZ.

Hope this helps you...

~thomas

[Former Member]

Hello Venu,

I am sure select query's you have written will take lot of time because you are not using <u><b>Primary key</b></u> in both select statements. Try to use Primary key's.

Thanks

suresh

[Former Member]

Hi,

Follow bvelow logic and remove duplicates fromtable

TCATSPS so that tab;le entries will be reduced

and it will improve performance.

SELECT belnr stokz FROM CATSPS INTO TABLE TCATSPS

WHERE TRANSFER = 'X' .

if not TCATSPS[] is inaitial.

TCATSPS_temp[] = TCATSPS[].

delete TCATSPS_temp where belnr is initial.

sort TCATSPS_temp by belnr stokz.

delete adjacent duplicates from TCATSPS_temp comparing

belnr stokz.

if not TCATSPS_temp[] is initial.

SELECT RUECK

STOKZ

CATSBELNR

FROM AFRU

INTO table T_AFRU

for all entries in tcatsps_temp

WHERE CATSBELNR = TCATSPS_temp-belnr

AND STOKZ = TCATSPS_temp-STOKZ.

free TCATSPS_temp

regards

amole