编程语言

img allen77

Oracle Pro*C/C++游标和存储过程性能测试报告

发表于2004/10/20 16:08:00  1066人阅读

1       Oracle Pro*C/C++游标和存储过程性能测试报告

1.1    目的说明

进行Oracle数据库Pro*C/C++编程往往要对数据库中的记录进行读取,Pro*C/C++开发工具提供了二种对数据库读写的方法:一种我称之为游标(Cursor)的方式;另一种是C语言调用Oracle PL/SQL编程的储存过程方式;这二种方式在编程选择方面一般都没有太多数值比较说明,本文将实际编写测试代码,对这二种编码方式进行实际速度测试为各位读者在以后工作中进行编码选择提供经验。

测试环境 二台PC机其中一台是数据库服务器,另一台是客户端即本文测试代码运行机器

硬件

       CPU P4 2.6

       内存 1G

硬盘 80G

软件

       windows2000

       Oracle 8.1.7

       VC6

1.2    测试记录

直接使用游标(cursor)和采用存储过程进行整表同步的测试结果

 

表1 120000条记录测试结果

测试次数

使用cursor方式(TD)

使用PL/SQL方式(WCDMA)

5'22"

7

1'23"

8

1'15"

7

1'22

7

2'20

6

1'19

8

232

8

平均

2’13.3”

7.3”

 

 

表2 3000条记录测试结果

测试次数

使用cursor方式(TD)

使用PL/SQL方式(WCDMA)

3

0

2

0

2

0

2

0

2

0

平均

2.2”

0”

 

表3 9000条记录测试结果

测试次数

使用cursor方式(TD)

使用PL/SQL方式(WCDMA)

0

0

14

1

9

0

13

1

10

0

平均

9.2”

0.4”

 

根据测试结果可以看出的确用存储过程在大数据量时是极其有优势的,特别是在大数据量的情况下,并且存储过程的性能极其稳定,cursor方式由于时间长,感觉受网络状况影响较大,时间长短不定。可以得出结论使用储存过程将18倍的提高数据查询读取速度。

1.3    部分测试代码

1.3.1   测试表脚本:

创建用于查询的表

drop table tb_boardt;

create table tb_boardt(

 BYBOARDTYPE   NUMBER(3),

 ABYBOARDNAME  VARCHAR2(15)

) tablespace cm_space;

 

用储存过程向表中插入需要查询的120000记录:)

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;

/

 

用于读取记录的存储过程包头

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;

 

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*/); 

      

end testEptTbl;

/

show err

 

用于记录的存储过程包体

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,/*单板类型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

 

1.3.2   游标方式:

//倒出数据到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 condition

              EXEC SQL WHENEVER SQLERROR DO sqlerror(m_dbheader);

        EXEC SQL FETCH CUR_R_BOARDT INTO

                     :bHostVar1,:chHostVar1;

 

              /* Write however many rows were 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",StartTime);

       printf("end time: %s/n",EndTime);

 

    WriteZdbHeader();

}

1.3.3   存储过程方式:

//倒出数据到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,:chHostVar1);

            END;

        END-EXEC;

        TupleNum=TupleNum+num_ret;

//      print_rows(num_ret);

        WriteBatchToZdb(num_ret,"b01c01");

    }while(!done_flag);

    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;

 

}

 

阅读全文
0 0

相关文章推荐

img
取 消
img