topic Re: performance issue in Application Development and Automation Discussions

performance issue

Former Member — Tue, 07 Aug 2007 13:51:55 GMT

Hi all,

I have a performance issue with the following query.

SELECT * FROM z2rlresurrect

INTO TABLE ts_z2rlresurrect

FOR ALL ENTRIES IN ts_zzumicur_copy

WHERE ( zzumicur = ts_zzumicur_copy-zzumicur OR

resurrected_umi = ts_zzumicur_copy-zzumicur ).

In the table z2rlresurrect zzumicur is a key and resurrected_umi is an indexed field.When i did the trace its not using the index while selecting.

Problem is it taking too much time when there are no records exists for a ts_zzumicur_copy.

Any help will be appreciated.

Thanks

Sukumar.

Re: performance issue

Former Member — Tue, 07 Aug 2007 13:56:50 GMT

Hi,

before using this query ,put an if condition i.e.

if sy-subrc = 0.

select * ...........................

endif.

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: performance issue

RaymondGiuseppi — Tue, 07 Aug 2007 13:57:52 GMT

Before each and every use of a "FOR ALL ENTRIES" you should (in fact MUST) check whether the internal table is empty or not. Else the select will read the whole table. (like a select-options without criteria)

Regards

Re: performance issue

former_member194669 — Tue, 07 Aug 2007 13:59:27 GMT

Hi,

Change this way


if not ts_zzumicur_copy[] is initial. << Check for entries
   SELECT * FROM z2rlresurrect
   INTO TABLE ts_z2rlresurrect
   FOR ALL ENTRIES IN ts_zzumicur_copy 
   WHERE ( zzumicur = ts_zzumicur_copy-zzumicur OR
   resurrected_umi = ts_zzumicur_copy-zzumicur ).
endif.

aRs

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:00:18 GMT

Hi sukumar

if ts_zzumicur_copy[] is not initial.

SELECT * FROM z2rlresurrect

INTO TABLE ts_z2rlresurrect

FOR ALL ENTRIES IN ts_zzumicur_copy

WHERE ( zzumicur = ts_zzumicur_copy-zzumicur OR

resurrected_umi = ts_zzumicur_copy-zzumicur ).

endif.

u cannot use if the internal table is empty in for all entries

if u use there is no use that selects all the records .

Rewards points for helpful answers,

kiran.M

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:00:50 GMT

Hi,

My problem is if no records exists for the internal table ts_zzumicur_copy entries in the database table z2rlresurrect.

for ex if internal table has 200 records if it doesnt fina an entry in z2rlresurrect...it taking too much time.

I hope now its clear

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:01:08 GMT

I wonder if you may be better to split the select as the 'OR' statement may be causing the performance issue.

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:05:14 GMT

Iam checking as you guys said infact my code is.

CHECK NOT ts_zzumicur[] IS INITIAL AND s_zzumi[] IS INITIAL.

ts_zzumicur_copy[] = ts_zzumicur[].

break pulleps.

SELECT * FROM z2rlresurrect

INTO TABLE ts_z2rlresurrect

FOR ALL ENTRIES IN ts_zzumicur_copy

WHERE ( zzumicur = ts_zzumicur_copy-zzumicur OR

resurrected_umi = ts_zzumicur_copy-zzumicur ).

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:16:36 GMT

CHECK NOT ts_zzumicur[] IS INITIAL .

check s_zzumi[] IS INITIAL.

ts_zzumicur_copy[] = ts_zzumicur[].

SELECT Field1 field2 FROM z2rlresurrect

INTO TABLE ts_z2rlresurrect

FOR ALL ENTRIES IN ts_zzumicur_copy

WHERE ( zzumicur = ts_zzumicur_copy-zzumicur OR

resurrected_umi = ts_zzumicur_copy-zzumicur ).

Thanks

Mahesh

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:17:01 GMT

when ever ur useing for all entries

you have to check the above select query

is it retriving any data or not

if not ts_zzumicur_copy[] is initial

SELECT * FROM z2rlresurrect

INTO TABLE ts_z2rlresurrect

FOR ALL ENTRIES IN ts_zzumicur_copy

WHERE ( zzumicur = ts_zzumicur_copy-zzumicur OR

resurrected_umi = ts_zzumicur_copy-zzumicur ).

endif.

rewrd if usefull

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:18:12 GMT

if use for all entries

if there is no matches for the given condition then it will retrive all the data

if u write like above

then it won't allow that

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:23:54 GMT

Any help?

Re: performance issue

Former Member — Tue, 07 Aug 2007 14:31:18 GMT

Have you tried what I said.

'I wonder if you may be better to split the select as the 'OR' statement may be causing the performance issue'

Have two different SQL statements using a single where condition instead of the 'OR' command'.

Re: performance issue

RaymondGiuseppi — Tue, 07 Aug 2007 14:51:40 GMT

1-If for entries internal table is empty, do not select

2-Break the select with 'OR' in two select. The or on two distinct field don't help the SQL optimizer (at least)

  SELECT * 
    FROM z2rlresurrect
    INTO TABLE ts_z2rlresurrect
    FOR ALL ENTRIES IN ts_zzumicur_copy
    WHERE  zzumicur = ts_zzumicur_copy-zzumicur.
  SELECT * 
    FROM z2rlresurrect
    APPENDING TABLE ts_z2rlresurrect
    FOR ALL ENTRIES IN ts_zzumicur_copy
    WHERE  resurrected_umi = ts_zzumicur_copy-zzumicur
      AND  zzumicur <> ts_zzumicur_copy-zzumicur.

If the performance problem continues, are the fields "zzumicur" and "resurrected_umi" first key (after client) of TWO primary or alternative indexes (ie zzumicur on primary index, and an index with resurrected_umi is active in database)

Regards

Re: performance issue

Former Member — Tue, 07 Aug 2007 15:12:39 GMT

HI,

I do the changes as you suggested and get back to you

Thanks

sai.