Before answering DELETING rows of the internal table within the LOOP. Is it a Taboo? Know that Such an extremely long time, I was informed that Erase proclamation inside a Circle is constantly disallowed. Assuming you erase any columns inside a Circle, it is ensured that the Quality Commentator would give you a point and request that you change your code to erase the sections outside the Circle.
Subsequent to going through Naimesh’s post on execution on Erase, I considered how might erasing the passages of the table on which we are circling would act in two situations. This is an extremely pragmatic situation where we want to Erase passages of the Circling table in view of certain checks (For effortlessness, I’m checking a field of similar table, however in genuine ventures, you could check another table fields and afterward erase the driver Circling table). DELETING rows of the internal table within the LOOP. Is it a Taboo?
Since the vast majority of the designers utilize STANDARD Inward tables, I’m investigating just STANDARD inner tables (Arranged/HASHED Tables should sit tight for another day)..
The two scenario DELETEs we are talking today are:
1) Parallel Cursor Technique with DELETE w.r.t INDEX within the LOOP.
2) Parallel Cursor Technique with marking the row to be DELETED and then DELETING all at one shot outside the LOOP.
What does your legitimate psyche say? (DELETING rows of the internal table within the LOOP. Is it a Taboo?)
Allow me to tell the truth. Since my preparation days, very nearly 10 years prior, we were informed that Erase inside Circle is rarely adequate. Thus, I generally required the subsequent course. Mark by Equal Cursor procedure and Erase outside the Circle.
Later on, as I began understanding the idea of File and Table sweep, I began trusting that, Erasing inside Circle in equal cursor with the objective Record known, would be better in the perspective on Execution. Likewise, that’s what I felt assuming we mark and Erase outside circle with WHERE proviso, the entire table should be filtered for the thing to be erased and it would influence the presentation adversely. So I started to accept, Erasing inside Circle ought to be OK as well.
In any case, never before did I attempt to do the quantitative examination. At last yesterday, following quite a while of lingering, I considered wearing the analyst’s cap. I went through very nearly a day playing with the numbers. I attempted to build my example size however much I could. I ran the information on various occasions in numerous hours of the day (as framework can have variable burden in various hours of the day) to get the best typical figure for the examination.
Here is the code bit which I utilized for doing this examination. I involved similar arrangement of information for both Erase articulations so I could look at the two in unbiased scene.
PARAMETERS p_row TYPE i DEFAULT 1000. DATA: i_cdpos TYPE STANDARD TABLE OF cdpos INITIAL SIZE 0, i_cdpos1 TYPE STANDARD TABLE OF cdpos INITIAL SIZE 0, ls_cdpos TYPE cdpos, ls_cdpos1 TYPE cdpos, lv_sta_time TYPE timestampl, lv_end_time TYPE timestampl, lv_diff_w TYPE p DECIMALS 5, lv_no TYPE i. FIELD-SYMBOLS: <lfs_cdpos> TYPE cdpos. * Get entries from Change Log Item table SELECT * UP TO p_row ROWS FROM cdpos INTO TABLE i_cdpos. * Sorting for BINARY Search SORT i_cdpos BY objectclas. * Keeping a copy in another table i_cdpos1[] = i_cdpos[].
1) Parallel Cursor Technique with DELETE w.r.t INDEX within the LOOP.
* Start time
GET TIME STAMP FIELD lv_sta_time.
* Scenario1: Parallel Cursor with DELETE within LOOP (The driver table rows are deleted)
LOOP AT i_cdpos INTO ls_cdpos.
READ TABLE i_cdpos TRANSPORTING NO FIELDS
WITH KEY objectclas = ls_cdpos-objectclas
BINARY SEARCH.
IF sy-subrc EQ 0.
LOOP AT i_cdpos INTO ls_cdpos1 FROM sy-tabix.
IF ls_cdpos-objectclas NE ls_cdpos1-objectclas.
EXIT.
ELSEIF ls_cdpos1-chngind EQ 'I'.
* DELETING with INDEX
DELETE i_cdpos INDEX sy-tabix.
ENDIF.
ENDLOOP.
ENDIF.
ENDLOOP.
* End time
GET TIME STAMP FIELD lv_end_time.
* Time taken for LOOP and DELETE
lv_diff_w = lv_end_time - lv_sta_time.
WRITE: /(25) 'DELETE Inside Loop', lv_diff_w.
* Entries left in table
lv_no = lines( i_cdpos ).
* Number of entries DELETED
lv_no = p_row - lv_no.
WRITE:/(25) 'No of entries deleted', lv_no2) Parallel Cursor Technique with marking the row to be DELETED and then DELETING outside the LOOP.
CLEAR: lv_no, lv_sta_time, lv_end_time, lv_diff_w, ls_cdpos. * Start time GET TIME STAMP FIELD lv_sta_time. * Parallel Cursor with MARKING of rows to be DELETED * Actual DELETE outside of the LOOP LOOP AT i_cdpos1 INTO ls_cdpos. READ TABLE i_cdpos1 TRANSPORTING NO FIELDS WITH KEY objectclas = ls_cdpos-objectclas BINARY SEARCH. IF sy-subrc EQ 0. LOOP AT i_cdpos1 ASSIGNING <lfs_cdpos> FROM sy-tabix. IF ls_cdpos-objectclas NE <lfs_cdpos>-objectclas. EXIT. ELSEIF <lfs_cdpos>-chngind EQ 'I'. * MARKING the ROW to be DELETED. * Outside the LOOP, objectclas field would be used to identify CLEAR <lfs_cdpos>-objectclas. ENDIF. ENDLOOP. ENDIF. ENDLOOP. * DELETE the MARKED rows at one shot DELETE i_cdpos1 WHERE objectclas IS INITIAL. * End time GET TIME STAMP FIELD lv_end_time. * Time taken for LOOP and DELETE lv_diff_w = lv_end_time - lv_sta_time. WRITE: /(25) 'DELETE Outside Loop', lv_diff_w. * Entries left in table (this should be same as above) lv_no = lines( i_cdpos1 ). * Number of entries DELETED lv_no = p_row - lv_no. WRITE:/(25) 'No of entries deleted', lv_no.
The time taken is for the complete activity set i.e. complete LOOP and DELETE in both scenarios.
The following is the result information which I gathered after various runs.
The principal look of the information shows; for table lines with sections under 10,000; Erase outside Circle works better. Take a gander at section 6 (% change). This section is the rate change between Erase outside Circle and Erase inside Circle. Reference is Erase outside Circle.
How to interpret column 6.
Negative number means, DELETE outside LOOP is better.
Assuming you go through the above table 1 or diagram 1, it plainly portrays that Erase inside Circle utilizing List and Equal Cursor is clear champ from 13,000 to 30,000 records in interior table. In any case, again after 35,000 columns in inner table, Erase outside Circle is the victor.
So, it made me conclude (wrongly) that:
i) For small internal tables, less than 10,000 rows, one DELETE outside LOOP was good.
ii) For medium tables 10K to 30K row entries, DELETING using index within LOOP in PARALLEL cursor was good.
iii) For large tables 35K and greater, one DELETE statement outside LOOP has better performance.
Kindly note, I have referenced I wrongly closed. The over three assertions are NOT totally right.
I was as yet not persuaded with above outcome. I was pondering, on the off chance that there is whatever other element which is affecting the outcome other than the quantity of sections.
Then, at that point, I chose to run the program in an alternate client. I ran for similar quantities of records beginning from 1000 till 40,000.
Table 2 shows the aftereffect of running a similar program in various client. The result doesn’t count with Table 1 outcomes by any means. Here one Erase proclamation outside the Circle is clear champ. The deep rooted funda passed on by seniors/coaches holds great here. Erase inside Circle is rarely prudent.
Take a gander at the chart, the presentation of Erase inside Circle is under nothing (i.e worse than Erase outside Circle)
However, I needed to know the explanation. WHY? For what reason is that in the other client, there was different example and in this client Erase outside Circle is better?
Then, at that point, I ended up seeing the ‘No of Lines Erased’ section 2. I started to foster my hypothesis and began questioning, is this one of the few variables which is affecting the general execution.
Referring to Table 1
Out of 1000 enteries in table, 430 were deleted. So by the time the last entry was deleted, i.e. 430th entry, there were 1000-429 = 571 rows in the table. So, when 430th was deleted, indexes were regenerated for 570 rows in the internal table.
Which method impacts the performance more?
a) Regenerating the indexes every time the internal table row is deleted in LOOP; or
b) Regenerating the indexes when it encounters the DELETE statement only once outside the LOOP.
Depending on the cost of the regeneration of indexes, probably the performance depends.
SAP Assist with reporting says: While erasing lines in an inside table, costs are caused for refreshing all current table keys and table records.
Something worth mulling over.
In Table 2, line with 30,000 records in inward table. Which technique would have better execution.
a) Erasing 1931 columns in Circle and recovering the file in each circle for limit of 29999 lines (first erase) and least of 28069 records (last erase)?
At the point when the principal column is erased, there are 29,999 lines in inside table to be re-filed. At the point when the last column for example 1931th is erased, there are 28,069 columns in interior table to be re-ordered. Altogether, the inward table is re-ordered multiple times in Circle.
b) Deleting 1931 rows at one shot outside the LOOP and re-indexing?
Erasing outside Circle appears to be unrivaled despite the fact that in the Equal cursor we are Erasing utilizing Record. Be that as it may, might be the expense of refreshing the current table keys and table lists downsize the upside of Equal CURSOR Erase utilizing Record.
I don’t have any idea, how right the above assessment is. Be that as it may, one thing is clear, internal table re-requesting cost should be seen as preceding closing which course to go.
We can safely conclude that performance of the two scenarios is dependent on:
i) The number of entries in the table
ii) The number of entries to be deleted in the table
iii) The total number of entries left after delete statement
iv) Total number of times the index of the internal table need to be regenerated
What’s more, until further notice, Erase outside Circle appears to have an Advantage.. (I’m expressing something which could draw in loads of analysis/banter from Equal Cursor Strategy sweethearts).
YOU MAY LIKE THIS
What Does a DBA Course Cover? Skills, Tools, and Career Path.
Unleashing the Power of Performance Tuning in Oracle
WhatsApp us