topic Re: Select + Performance in Application Development and Automation Discussions

Select + Performance

Former Member — Fri, 28 Jul 2006 12:00:59 GMT

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

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:05:44 GMT

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.

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:06:56 GMT

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

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:07:21 GMT

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.

Re: Select + Performance

rahulkavuri — Fri, 28 Jul 2006 12:08:34 GMT

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

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:10:10 GMT

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

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:11:17 GMT

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

Re: Select + Performance

shivananddeshmu — Fri, 28 Jul 2006 12:23:26 GMT

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

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:39:02 GMT

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

Re: Select + Performance

Former Member — Fri, 28 Jul 2006 12:41:08 GMT

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