Select + Performance

[Former Member]

Hi

all

i want to ask which will have good performance.if i will

select then delete the records with LVORM is X or when i will select by setting lvorm eq 'X' in where clause.

1) Select matnr,pstat,lvorm

from mara

where matnr in S_matnr.

if sy-subrc eq 0.

delete t_mara where lvorm is 'X'

endif.

*OR*

1) Select matnr,pstat,lvorm

from mara

where matnr in S_matne.

and lvorm eq 'X'

Thanks

Saurabh

[Former Member]

1) Select matnr,pstat,lvorm

from mara

where matnr in S_matnr.

if sy-subrc eq 0.

delete t_mara where lvorm is 'X'

endif.

[Former Member]

second one should have good performance , becoz selecting data is minimized at the database level.

say eg:

u have all records with lvorm NE 'X'

For the first statement , it will select all the records and then delete

Using the second one it will not select any record at all

[Former Member]

Hi saurabh,

1. This is very subjective

2. BCOS

3. The performance may be good in first case,

and also in 2nd case,

as the scenario may be.

4. it depends upon

a) number of records

b) index of LVORM

5. However, according to me,

2nd option should be feasible, practical and

performance wise good

6. The reason is.

a) directly the database is involved, in ONE SHOT

b) whereas in case of 1,

The there may be database trips,

more than once,

based upon the value of LVORM.

regards,

amit m.

[Former Member]

Hi

if i m using

Select matnr,pstat,lvorm

from mara

where matnr in S_matne.

and lvorm eq 'X'

then it will again nad again go into Database Server to check lvorm.so it may take more time

But i will delete it using If then it will be in Application server so i think it will taka less time,

Plz clarify my doubt.

Thanks

saurabh

[rahulkavuri]

This depends on the number of records u have in the DB table which match the selection, if its a large database <b>the second one will be efficient</b> as u need not select many records and then delete them

[Former Member]

Hi Saurabh

In the first statement you have to add

Select ... Into T_mara

Both will have equally bad performance as you are not collecting required data to internal table.

So use follwoing:

Select matnr,pstat,lvorm

from mara into T_mara

where matnr in S_matnr

if sy-subrc eq 0.

delete t_mara where lvorm is 'X'

endif.

Regards

Inder

[Former Member]

Hi,

Select matnr,pstat,lvorm

from mara

where matnr in S_matne.

and lvorm eq 'X'

This one will perform better than other one as once we read the data and if we again delete then again it will regenerate the index and that will be time taking ,so I think we should use filter of data by putting in where Clause instead of using Delete statement.

Thanks,

Pramod

[Former Member]

Hi

if i m using

Select matnr,pstat,lvorm

from mara

where matnr in S_matne.

and lvorm eq 'X'

then it will again nad again go into Database Server to check lvorm.so it may take more time

But i will delete it using If then it will be in Application server so i think it will taka less time,

Plz clarify my doubt.

Thanks

saurabh

[shivananddeshmu]

Hi,

This depends on whether the number of records with LVORM = 'X' is more than the rest.

but the recommended is

Select matnr,pstat,lvorm

from mara

where matnr in S_matne.

and lvorm eq 'X'

Rgds

Shivanand