Performance comparisons for cursor looping loop,while and for

Performance is important when using cursors to iterate over large amounts of data, and now performs a performance comparison for three loops:

First, test environment configuration:

Hardware: HP notebook Intel Core (TM) 2 main frequency 2.0GHz 3G memory win7 operating system

Tools: Pl/sql

Database: Oracle

-----------------------------------------------------------------------------

The SB_KPXX table in my database contains 103,521,479 data, which has reached billions of levels, so it can be tested. When testing by setting rownum parameters, you can perform different levels of performance testing, due to the time relationship, here only the million, 100,000, millions test.

Second, the test code is as follows:

--The test code tests only the data and does not manipulate the data.

--loop Way

Set serveroutput on;
Declare
V_num varchar2 (100);
Cursor C_num is select Sb_num from Super.sb_kpxx where rownum < 60000;
Begin
Dbms_output.enable (800000);
Open C_num;
Loop
Fetch c_num into V_num;
Exit when c_num%notfound;--exits when data is not found (that is, the end of the record is reached).
End Loop;
Close C_num;
End
/

--while Way

Declare
V_num varchar2 (100);
Cursor C_num is select Sb_num from Super.sb_kpxx where rownum < 60000;
Begin
Open C_num;
Fetch c_num into V_num;
While c_num%found--can find the data, execute the loop inside the statement.
Loop
Fetch c_num into V_num;
End Loop;
Close C_num;
End
/

--for Way

Set serveroutput on;
Declare
V_num varchar2 (100);
Cursor C_num is select Sb_num from Super.sb_kpxx where rownum < 60000;
Begin
Dbms_output. ENABLE (800000);
The for cur in c_num--cur is fixed by itself.
Loop
V_num: = Cur.sb_num;
End Loop;
End
/

Third, test results

There are 5 tests at each level, averaging and then filling in the following table:

Level	Loop	While	For
Million grades (60,000)	0.59	0.646	0.059
Level 100,000 (600,000)	5.519	5.8	0.443
Millions (6 million)	55.047	55.817	4.348
Tens (60 million)	Not measured	Not measured	71.729

Unit: Seconds

Iv. Conclusion:

As you can see from the table, for loops, the efficiency of the For loop is far higher than the loop and while mode, while the loop and while are not the same:

1. From the order of magnitude of the consumption of time, the loop and the while are both at the same data level and for the first order of magnitude.

2. When the amount of data arrives at millions, the loop and the while are already close to the level of the score, and for is still at the second level until the tens is reached.