1 Oracle Pro * C / C Cursor and Storage Process Performance Test Report
1.1 purpose description
Perform Oracle Database Pro * C / C programming often reads records in the database, and Pro * C / C development tools provide two ways to read and write databases: a way I call it a cursor (Cursor) The other is the storage process of C language calling Oracle PL / SQL programming; these two methods generally do not have much value comparison instructions in programming selection, this article will actually write test code and actually actually in these two coding methods Speed test provides experience in coding selection in the future work.
One of the two PCs in the test environment is a database server, and the other is the client, this article test code running machine.
hardware
CPU P4 2.6
Memory 1G
Hard disk 80g
software
Windows2000
Oracle 8.1.7
VC6
1.2 test record
Directly use the cursor and the test result of the test results in the stored procedure
Table 1 120000 Record test results
Number of tests using Cursor mode (TD) Use PL / SQL mode (WCDMA) 1 5'22 "7" 2 1'23 "8" 3 1'15 "7" 4 1'22 "7" 5 2'20 "6 "6 1'19" 8 "7 2'32" 8 "average 2'13.3" 7.3 "
Table 2 3000 record test results
Test number uses Cursor mode (TD) Use PL / SQL mode (WCDMA) 1 3 "0" 2 2 "0" 3 2 "0" 4 2 "0" 5 2 "0" average 2.2 "0"
Table 3 9000 record test results
Number of tests using Cursor mode (TD) using PL / SQL mode (WCDMA) 1 0 "0" 2 14 "1" 3 9 "0" 4 13 "1" 5 10 "0" average 9.2 "0.4" according to test results It is seen that the stored procedure is extremely advantageous, especially in the case of large data, and the performance of the stored procedure is extremely stable, due to time, it feels large, time The length is uncertain. It can be concluded that the use of the storage process to increase the data query reading speed by 18 times.
1.3 Partial test code
1.3.1 Test table script:
Create a table for query
DROP TABLE TB_BOARDT;
Create Table TB_Boardt (
ByboardType Number (3),
Abyboardname varcha2 (15)
TABLESPACE CM_SPACE;
Insert the 120,000 record that needs to be queried to the table with the storage process :)
Declare
I integer: = 0;
Begin
For i in 1.. 20000 loop
INSERT INTO TB_BOARDT VALUES (1, 'Allen_Board');
INSERT INTO TB_BOARDT VALUES (2, 'LILY_BOARD');
INSERT INTO TB_BOARDT VALUES (4, 'Lil_Board');
INSERT INTO TB_BOARDT VALUES (5, 'Allen_Board');
INSERT INTO TB_BOARDT VALUES (6, 'BEMC_BOARD');
INSERT INTO TB_BOARDT VALUES (7, 'TWIM_BOARD');
COMMIT;
End loop;
END;
/
Bummage process for reading records
Create or Replace Package TesteptTBL AS
Type CharaRrayType Is Table of Varchar2 (2048)
INDEX by binary_integer;
TYPE LCHARRAYTYPE IS TABLE OF VARCHAR2 (2048)
INDEX by binary_integer;
TYPE NUMARRAYTYPE IS TABLE OF INT
INDEX by binary_integer;
Num integer;
M_RNCID Integer;
Procedure get_r0brdlib
Batch_size in integer,
Found in out Integer,
Done_Fetch out Integer,
BoardType1 Out NumArrayType, / * Mark Type Board Type * /
BoardName1 Out ChararrayType / * Subrid Number Per Type * /) of each sub-unit type.
END TESTEPTTBL;
/
SHOW ERR is used to record stored procedures
Create or Replace Package Body TesteptTBL AS
/ * 3 * /
Cursor Cur_R0Brdlib IS
Select byboardType, AbyboardName from Tb_Boardt;
Procedure get_r0brdlib
Batch_size in integer,
Found in out Integer,
Done_Fetch out Integer,
BoardType1 Out NumArrayType, / * Mark Type Board Type * /
BoardName1 Out CharaRayType / * Subrid Number Per Type * /) IS
Begin
IF not cur_r0brdlib% isopen then
Open cur_r0brdlib;
END IF;
DONE_FETCH: = 0;
Found: = 0;
For i in 1....batch_size loop
Fetch Cur_r0Brdlib
INTO BOARDTYPE1 (I), BOARDNAME1 (I);
IF CUR_R0BRDLIB% NOTFOOND THEN
Close Cur_R0Brdlib;
DONE_FETCH: = 1;
EXIT;
Else
Found: = Found 1;
END IF;
End loop;
END GET_R0BRDLIB;
END TESTEPTTBL;
/
Show ERR
1.3.2 Cursor method:
// Pour out the data to ZDB
Void CBoardt :: Writezdb (int SubnetID, int Nodebid)
{
EXEC SQL Begin Declare Section;
Int isubnetid = SubnetId;
INT inodebid = nodebid;
Exec SQL End Declare Section;
Char TmpBuf [128], StartTime [256], EndTime [256];
/ * SET TIME ZONE from TZ Environment Variable. If Tz Is Not Set,
* The Operating System Is Queried to Obtain The Default Value
* for the variable.
* /
_tzset ();
/ * Display Operating System-Style Date and Time. * /
_STRTIME (TMPBUF);
Sprintf (StartTime, "OS TIME: / T / T / T% S / N", TMPBUF);
/ * DECLARE A CURSOR for the fetch. * /
EXEC SQL DECLARE CUR_R_BOARDT CURSOR for
Select byboardType, AbyboardName from Tb_Boardt;
EXEC SQL OPEN CUR_R_BOARDT;
/ * Initialize the number of rows. * /
NUM_RET = 0;
INT rows_ret = Batchsize;
While (BatchSize == ROWS_RET)
{
// fetch from the cursor, catching all ora error conputions
Exec SQL WHENEVER SQLEC DO SQLERROR (M_DbHeader);
EXEC SQL FETCH CUR_R_BOARDT INTO: BHOSTVAR1,: ChHOSTVAR1;
/ * WRITE HOWEVER MANY ROWS WERE RERE RERE RETURNED. * /
Rows_ret = Sqlca.sqlerrd [2] - Num_Ret;
WritebatchTozdb (rows_ret, "b01c01");
Num_ret = sqlca.sqlerrd [2]; / * reset the number. * /
}
/ * Write Remaining Rows from Last Fetch, IF ANY. * /
IF ((Sqlca.sqlerrd [2] - Num_Ret)> 0)
WritebatchTozdb (Sqlca.sqlerrd [2] - Num_Ret, "B01C01");
m_dbheader.dbtuplenum = sqlca.sqlerrd [2];
EXEC SQL Close Cur_R_Boardt;
_STRTIME (TMPBUF);
Sprintf (EndTime, "OS Time: / T / T / T / T% S / N", TMPBUF);
Printf ("Begin Time:% S / N", STATTIME);
Printf ("End Time:% S / N", EndTime);
Writezdbheader ();
}
1.3.3 Store procedure:
// Pour out the data to ZDB
Void CBRDLIB :: Writezdb (int SubnetID, INT Nodebid)
{
DWORD TUPLENUM = 0;
Char TmpBuf [128], StartTime [256], EndTime [256];
/ * SET TIME ZONE from TZ Environment Variable. If Tz Is Not Set,
* The Operating System Is Queried to Obtain The Default Value
* for the variable.
* /
_tzset ();
/ * Display Operating System-Style Date and Time. * /
_STRTIME (TMPBUF);
Sprintf (StartTime, "OS TIME: / T / T / T% S / N", TMPBUF);
DO
{
EXEC SQL EXECUTE
Begin TesteptTBL.GET_R0BRDLIB
(: array_size,: num_ret,: done_flag,: bhostvar1,: chovetvar1);
END;
End-exec;
Tuplenum = tuplenum num_ret;
// Print_Rows (NUM_RET);
WritebatchTozdb (Num_Ret, "B01C01");
} while (! ";
m_dbheader.dbtuplenum = tuplenum;
_STRTIME (TMPBUF);
Sprintf (EndTime, "OS Time: / T / T / T / T% S / N", TMPBUF);
Printf ("BRDLIB Begin Time:% S / N", StartTime);
Printf ("End Time:% S / N", EndTime);
Writezdbheader ();
}
/ * ------------- CTable Factory ----------------- * /
CTable * CTable :: Factory (int Type)
{
Switch (Type)
{
// r0
Case 2:
Return New CBoardt;
Break;
Case 1:
Return New CBRDLIB;
Break;
}
Return NULL;
}
