topic Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write in Application Development and Automation Discussions

Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 04:23:47 GMT

Hi Experts,

I got a code review problem & we are in a argument.

I need the best performance code out of this two codes. I have tested this both on 5 & 1000 & 3000 & 100,000 & 180,000 records.

But still, I just need a second opinion of experts.

TYPES : BEGIN OF ty_account,

saknr TYPE skat-saknr,

END OF ty_account.

DATA : g_txt50 TYPE skat-txt50.

DATA : g_it_skat TYPE TABLE OF skat, g_wa_skat LIKE LINE OF g_it_skat.

DATA : g_it_account TYPE TABLE OF ty_account, g_wa_account LIKE LINE OF g_it_account.

Code 1.


SELECT saknr INTO TABLE g_it_account FROM skat.

LOOP AT g_it_account INTO g_wa_account.
  SELECT SINGLE txt50 INTO g_txt50 FROM skat
    WHERE spras = 'E'
      AND ktopl = 'XXXX'
      AND saknr = g_wa_account-saknr.
  WRITE :/ g_wa_account-saknr, g_txt50.
  CLEAR : g_wa_account, g_txt50.
ENDLOOP.

Code 2.


SELECT saknr INTO TABLE g_it_account FROM skat.
SELECT * INTO TABLE g_it_skat FROM skat 
  FOR ALL ENTRIES IN g_it_account
      WHERE spras = 'E'
        AND ktopl = 'XXXX'
        AND saknr = g_it_account-saknr.

LOOP AT g_it_account INTO g_wa_account.
  READ TABLE g_it_skat INTO g_wa_skat WITH KEY saknr = g_wa_account-saknr.
  WRITE :/ g_wa_account-saknr, g_wa_skat-txt50.
  CLEAR : g_wa_account, g_wa_skat.
ENDLOOP.

Thanks & Regards,

Dileep .C

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 04:32:09 GMT

Hi,

As per my knoweldge second code is better option. You need to validate the internal table g_it_account before select for all entries.

SELECT saknr INTO TABLE g_it_account FROM skat.
IF  g_it_account[] is not initial.  "This is mandatory for select all entries
SELECT * INTO TABLE g_it_skat FROM skat 
  FOR ALL ENTRIES IN g_it_account
      WHERE spras = 'E'
        AND ktopl = 'XXXX'
        AND saknr = g_it_account-saknr.
ENDIF.   
 
LOOP AT g_it_account INTO g_wa_account.
  READ TABLE g_it_skat INTO g_wa_skat WITH KEY saknr = g_wa_account-saknr.
  WRITE :/ g_wa_account-saknr, g_wa_skat-txt50.
  CLEAR : g_wa_account, g_wa_skat.
ENDLOOP.

Regards,

Chandra

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 04:35:54 GMT

Hi Chandra,

Nice to see reply from you,

Try like this, create two programs, ZTEST1, ZTEST2, Copy the two codes in two programs,

& check results as I mentioned for the Database records, up to 10 rows, 3000 rows, 100K rows, & Select all.

When we execute, I noticed a huge diff in time.

I observed, Select Single is 60-80% Faster than For All Entries.

I need this SE30 Confirmation from you people.

Thanks,

Dileep .C

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 04:39:16 GMT

Hi,

1. It is not advisable to use SELECT in loop.

2. Before using FOR ALL ENTRIES you must always check the internal table against which you are checking.


    SELECT saknr INTO TABLE g_it_account FROM skat.
    If g_it_account IS NOT INITIAL.
        SELECT * INTO TABLE g_it_skat FROM skat 
        FOR ALL ENTRIES IN g_it_account
        WHERE spras = 'E'
        AND ktopl = 'XXXX'
        AND saknr = g_it_account-saknr.
   ENDIF.
   LOOP AT g_it_account INTO g_wa_account.
       READ TABLE g_it_skat INTO g_wa_skat WITH KEY saknr = g_wa_account-saknr.
       WRITE :/ g_wa_account-saknr, g_wa_skat-txt50.
      CLEAR : g_wa_account, g_wa_skat.
  ENDLOOP.

I think code 2 is a better performance code.

Regards,

Shraddha

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

former_member186741 — Fri, 11 Feb 2011 04:51:42 GMT

but both selects target the same table! Why can't you just code:

SELECT * INTO TABLE g_it_skat FROM skat

WHERE spras = 'E'

AND ktopl = 'XXXX'.

*sort table

sort gt_it_skat by saknr.

LOOP AT g_it_account INTO g_wa_account.

READ TABLE g_it_skat INTO g_wa_skat WITH KEY saknr = g_wa_account-saknr binary search.

WRITE 😕 g_wa_account-saknr, g_wa_skat-txt50.

CLEAR : g_wa_account, g_wa_skat.

ENDLOOP.

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 04:55:57 GMT

Hi Dilip.

from you both the code I have found that you are selecting 2 diffrent fields.

In Code 1.

you are selecting SAKNR and then for these SAKNR you are selecting TXT50 from the same table.

and in Code 2 you are selecting all the fields from SAKT table for all the values of SAKNR.

I don't know whats your requirement.

Better you declare a select option on screen and then fetch required fields from SAKT table for the values entered on screen for SAKNR.

you only need TXT50 and SAKNR fields.

so declare two types one for SAKNR and another for TXT50.

Points to be remember.

1. while using for all entries always check the for all entries table should not be blank.

2. you will have to fetch all the key fields in table while applying for all entries,

you can compare key fields with a constant which is greater than initial value.

3. while reading the table sort the table by the field on which you are going to read it.

try this:

TYPES : BEGIN OF ty_account,

saknr TYPE skat-saknr,

END OF ty_account.

TYPES : begin of T_txt50,

saknr type saknr,

txt50 type txt50,

end of t_txt50.

DATA: i_account type table of t_account,

w_account type t_account,

i_txt50 type table t_txt50,

w_txt50 type t_txt50.

select SAKNR from SKAT into table i_account.

if sy-subrc = 0.

sort i_account by saknr.

select saknr txt50 from SKAT into table i_txt50

for all entries in i_account

where SAKNR = i_account-SAKNR

here mention al the primary keys and compare them with their constants.

endif.

Note; here you need to take care that, you will have to fetch all the key fields in table i_txt50.

and compare those fields with there constants which should be greater than initial values.

they should be in proper sequence.

now for writing.

loop at i_account into w_account.

clear w_txt50.

sort i_txt50 by saknr.

read table i_txt50 into w_txt50 with key SAKNR = w_account-saknr

if sy-subrc = 0.

write: w_txt50-saknr, w-txt50-txt50.

clear w_txt50, w_account.

endif.

endloop.

Hope it wil clear your doubts.

Thanks

Lalit

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 05:34:49 GMT

Hi Neil & Lalit,

Its going off topic,

I just changed here select from same table,

Actually In my real scenario, Both are diff tables (Say, RACCT & SAKNR).

So I just need a very straight answer, Code1 or Code2.

Points of Interest,

Code 1 : Every one Says, Select inside Loop is not suggestable, But my output time is 2 Secs Appx Against 180K Records.

Code 2 : Every one Says, Its best performance, But My Code takes 10 Secs Appx against 180K Records.

So Do you people Suggest to go for best performance & do it for 10 secs, & what about when the Records in DB table reaches around some 500K records, & time would be 1-2 mins to see my output.

Please throw some of your expert opinions.

Thanks,

Dileep .C

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

former_member404244 — Fri, 11 Feb 2011 05:38:10 GMT

Hi Dileep,

I will go for with first option. I don't think select single inside a loop slow down the performance.

Regards,

Nagaraj

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

former_member186741 — Fri, 11 Feb 2011 05:48:33 GMT

in that case go for option 2 but ensure that your internal table is either defined as sorted...or is actualy sorted and read with the binary search clause....that should make sure perf is better

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 06:00:53 GMT

Hi Neil,

But option 2 is taking huge time,

Its Multiplying the time.

At last whats the point saying its best performance when its directly increasing the time which a human eye can see.

Option 1 = 2 secs.

Option 2 = 10 secs.

Thanks,

Dileep .C

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 08:27:04 GMT

Hi Deleep,

in my opinion, code 2 is better.

As Neil told, try to use a sorted table or sort your table and then use binary search.

Then look at se30 and see, for case 1 and case 2, the amount of time used for DB selection.

Regards

Andrea

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 08:52:47 GMT

You've already proven that code 1 is much faster, so why the doubt?

And given your code example: i don't see a reason why SELECT SINGLE in a loop would cause any performance problems.

Just go for code 1.

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 08:56:27 GMT

Yes Exactly,

Its already Proven in my system, that there was a huge diff in Code 1 & Code 2.

I just wanted to check if my system is wrong or it is same in other system.

Instead I got a whole lot of Diff Opinions, Anyways thanks for all the Suggestions.

Cheers,

Dileep .C

Re: Performance on Select Single&Write AND Select*(For All Entries)&Read&Write

Former Member — Fri, 11 Feb 2011 16:49:07 GMT

Nothing is proven if you replace one bug by another !

The FAE is of course faster than the SELECT inside a LOOP, there is no doubt about that.

But you end up with 2 internal tables, so you must manage the nested loop!

That is simple with a sorted table, hashed table or standard table with BINARY SEARCH (one of the absolute topsellers in this forum).

The quadratic scaling of your solution will kill the performance of option 2, for large tables even more than option 1.

Siegfried