Select Query performance issue

[Former Member]

Dear Gurus,

Pls see the following query....its taking a lot time & giving runtime error TSV_TNEW_PAGE_ALLOC_FAILED as no of records being fetched is around 1950000.

SELECT * FROM bseg

INTO CORRESPONDING FIELDS* OF TABLE it_final

for all entries in t_bkpf where belnr = t_bkpf-belnr

and gjahr in so_gjahr and bukrs = 'NDPL'.

Pls see & tell hw cn i modify this. Also i m unable to find indexes for table bseg.

Thnx.

[Former Member]

don't use INTO CORRESPONDING FIELDS .. instead

use into table .. also choose the fields U want and not all

from bseg ..

fields should be in the same order in where condition as

in the table ...

If not t_bkpf[] is initial.

SELECT * FROM bseg

INTO TABLE it_final

for all entries in t_bkpf

where bukrs = 'NDPL'

and belnr = t_bkpf-belnr

and gjahr in so_gjahr .

endif.

[Former Member]

Add a check statement.

check not t_bkpf[] is initial.

SELECT * FROM bseg

INTO CORRESPONDING FIELDS* OF TABLE it_final

for all entries in t_bkpf where belnr = t_bkpf-belnr

and gjahr in so_gjahr and bukrs = 'NDPL'.

Also dont use Select * into corresponding. Extract the fields you want.

[Former Member]

Hi,

While using the for all entries addition in select statement, the internal table should be sorted in your case t_bkpf should be sorted on belnr and also check t_bkpf has entries or not. Dont use *, instead use the fields of table. If u still get the problem while read data from bseg then try to use the below code.

data:n type i,

lines_from type i,

lines_to type i.

describe table t_bkpf lines v_lines.

n = v_lines / 1000.

if n = 0.

n = 1.

endif.

lines_from = 1.

Lines_to = 1000.

do n times

append LINES OF t_bkpf FROM lines_from TO lines_to to t_bkpf_temp.

SELECT * FROM bseg

INTO TABLE it_final

for all entries in t_bkpf_temp where belnr = t_bkpf_temp-belnr

and gjahr in so_gjahr and bukrs = 'NDPL'.

if sy-subrc = 0.

appending lines of it_final to it_output.

refresh t_bkpf_temp.

lines_from = lines_from + 1000.

lines_to = lines_to + 1000.

endif.

enddo

This will definatly helps you.

Rgds,

Bujji

[Former Member]

Hi,

You need to be very careful enough while retrieving data from Clustered/Pooled tables.

1. You should not use * instead use necessary fields.

2. Don't use into corresponding fields statement, rather you can use into table.

Thanks,

Sriram Ponna.

[Former Member]

Add GJAHR to T_BKPF and use that in the SELECT:

SELECT * FROM bseg
  INTO CORRESPONDING FIELDS OF TABLE it_final
  for all entries in t_bkpf 
  where bukrs = 'NDPL'
    and belnr = t_bkpf-belnr
    and gjahr = t_bkpf-gjahr.

Rob