1.1 目的说明 测试环境 二台PC机其中一台是数据库服务器，另一台是客户端即本文测试代码运行机器 硬件 CPU P4 2.6 内存 1G 硬盘 80G 软件 windows2000 Oracle 8.1.7 VC6 1.2 测试记录 直接使用游标（ｃｕｒｓｏｒ）和采用存储过程进行整表同步的测试结果 测试次数 使用ｃｕｒｓｏｒ方式（ＴＤ） 使用ＰＬ／ＳＱＬ方式（ＷＣＤＭＡ） １ ２ ３ ４ ５ ６ ７ 平均
测试次数 使用ｃｕｒｓｏｒ方式（ＴＤ） 使用ＰＬ／ＳＱＬ方式（ＷＣＤＭＡ） １ ２ ３ ４ ５ 平均
表３ ９０００条记录测试结果 测试次数 使用ｃｕｒｓｏｒ方式（ＴＤ） 使用ＰＬ／ＳＱＬ方式（ＷＣＤＭＡ） １ ２ ３ ４ ５ 平均
根据测试结果可以看出的确用存储过程在大数据量时是极其有优势的，特别是在大数据量的情况下，并且存储过程的性能极其稳定，ｃｕｒｓｏｒ方式由于时间长,感觉受网络状况影响较大，时间长短不定。可以得出结论使用储存过程将18倍的提高数据查询读取速度。 1.3 部分测试代码 1.3.1 测试表脚本： 创建用于查询的表 drop table tb_boardt; create table tb_boardt( BYBOARDTYPE NUMBER(3), ABYBOARDNAME VARCHAR2(15) ) tablespace cm_space;
declare i integer :=0; begin for i in 1..20000 loop insert into tb_boardt values(1,%27Allen_BOARD%27); insert into tb_boardt values(2,%27LILY_BOARD%27); insert into tb_boardt values(4,%27LIL_BOARD%27); insert into tb_boardt values(5,%27ALLEN_BOARD%27); insert into tb_boardt values(6,%27BEMC_BOARD%27); insert into tb_boardt values(7,%27TWIM_BOARD%27); commit; end loop; end;
create or replace package testEptTbl as type charArrayType is table of varchar2(2048) index by binary_integer; type lcharArrayType is table of varchar2(2048) index by binary_integer; type numArrayType is table of int index by binary_integer; num INTEGER; m_RncID INTEGER;
batch_size in integer, found in out integer, done_fetch out integer, BoardType1 out numArrayType,/*单板类型Board Type*/ BoardName1 out charArrayType/*各子单元类型包含的子单元个数Sunit Number Per Type*/);
/ show err
create or replace package body testEptTbl as
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,/*单板类型Board Type*/ BoardName1 out charArrayType/*各子单元类型包含的子单元个数Sunit 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%notfound then close CUR_R0BrdLib; done_fetch := 1; exit; else found := found + 1; end if; end loop; end get_R0BrdLib; end testEptTbl; / show err
void CBOARDT::WriteZdb(int SubNetID,int NodeBID) {
int iSubNetID = SubNetID; int iNodeBID = NodeBID; EXEC SQL END DECLARE SECTION;
/* 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();
_strtime( tmpbuf ); sprintf( StartTime,%22OS time:\t\t\t%s\n%22, tmpbuf );
EXEC SQL DECLARE CUR_R_BOARDT CURSOR FOR SELECT byBoardType, abyBoardName FROM TB_BOARDT;
num_ret = 0;
while (BATCHSIZE == rows_ret) { // Fetch from the Cursor, catching all ORA ERROR condition EXEC SQL WHENEVER SQLERROR DO sqlerror(m_dbheader); EXEC SQL FETCH CUR_R_BOARDT INTO :bHostVar1,:chHostVar1;
rows_ret=sqlca.sqlerrd[2] - num_ret; WriteBatchToZdb(rows_ret,%22b01c01%22); num_ret = sqlca.sqlerrd[2]; /* Reset the number. */ }
if ((sqlca.sqlerrd[2] - num_ret) > 0) WriteBatchToZdb(sqlca.sqlerrd[2] - num_ret,%22b01c01%22);
sprintf(EndTime,%22OS time:\t\t\t\t%s\n%22, tmpbuf ); printf(%22begin time: %s\n%22,StartTime); printf(%22end time: %s\n%22,EndTime);
} 1.3.3 存储过程方式： //倒出数据到zdb void CBRDLIB::WriteZdb(int SubNetID,int NodeBID) {
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();
_strtime( tmpbuf ); sprintf( StartTime,%22OS time:\t\t\t%s\n%22, tmpbuf );
{ EXEC SQL EXECUTE BEGIN testEptTbl.get_R0BrdLib (:array_size, :num_ret, :done_flag,:bHostVar1,:chHostVar1); END; END-EXEC; TupleNum=TupleNum+num_ret; // print_rows(num_ret); WriteBatchToZdb(num_ret,%22b01c01%22); }while(!done_flag); m_dbheader.dbTupleNum=TupleNum;
sprintf(EndTime,%22OS time:\t\t\t\t%s\n%22, tmpbuf ); printf(%22brdlib begin time: %s\n%22,StartTime); printf(%22end time: %s\n%22,EndTime);
} CTable* CTable::Factory(int type) { {
case 2 : return new CBOARDT; break; case 1 : return new CBRDLIB; break; }
|
