Oracle occi 批量插入数据

于 2019-08-14 16:46:37 发布
阅读量539 收藏 1
点赞数 1
分类专栏： c++
原文链接：https://blog.csdn.net/xiaobaismiley/article/details/22184399
版权

在用OCCI 向Oracle中插入数据时，效率不高，使用自动提交数据的情况（默认）下一秒钟只能插入1000条数据左右。因为插入数据库这块是影响系统中效率最明显的地方，因此很有必要提高数据插入这块的效率。在网上找了一些资料后发现，可以重用statement对象，使用批量插入的方法，先把数据保存在内存中，积累到一定数值之后批量插入Oracle，这样平均下来一秒钟可以插入5000多条数据，性能有所提高，不错，这里做一个记录。

    #include <iostream>
    #include <string.h>
    #include <time.h>
    #include <sys/time.h>
     
    #define WIN32COMMON //避免函数重定义错误
    #include <occi.h>
    #include <cstdlib>
    #include <map>
    #define ArraySize 10000  //内存中数据满1000条批量插入到oracle中
     
    using namespace oracle::occi;
    using namespace std;
     
    /*
     *返回当前时间，用于计算两个操作的时间差
     */
    long getCurrentTime()
    {
       struct timeval tv;
       gettimeofday(&tv,NULL);
       return tv.tv_sec * 1000 + tv.tv_usec / 1000;
    }
     
    /*
     * @author: roger
     */
    int main(void)
    {
     
        string username = "XX";
        string pass = "XXX";
        string srvName = "XXX";
        Environment *env ;
        Connection *conn;
        Statement *stmt;
        try
        {
            env = Environment::createEnvironment(Environment::THREADED_MUTEXED);
            conn = env->createConnection(username, pass, srvName);
     
            string sql = "insert into instant_infor (motor_id, lat, lon, uploadTime, receivetime, state_id, sys_state_id) values(:fld1,:fld2,:fld3,to_timestamp(:fld4,'yyyy-mm-dd hh24:mi:ss'),to_timestamp(:fld5,'yyyy-mm-dd hh24:mi:ss'),:fld6,:fld7)";
         stmt = conn->createStatement(sql);
     
        } catch(SQLException e)
        {
            env = NULL;
            conn = NULL;
            cout<<e.what()<<endl;
        }
     
          char motorid[ArraySize][12];
          char lat[ArraySize][20];
          char lon[ArraySize][20];
          char uploadTime[ArraySize][20];
          char createTime[ArraySize][20];
          char state_id[ArraySize][50];
          char sys_state_id[ArraySize][50];
     
        ub2  motor_idLen[ArraySize] ;
        ub2  uploadTimeLen[ArraySize] ;
        ub2  createTimeLen[ArraySize];
        ub2  state_idLen[ArraySize];
        ub2  sys_state_idLen[ArraySize];
        ub2  latLen[ArraySize] ;
        ub2  lonLen[ArraySize] ;
     
        long a1 = getCurrentTime();
        for(int i=0;i<ArraySize;i++){
     
          strcpy(motorid[i],"10000100000");
          strcpy(lat[i] , "30.123");
          strcpy(lon[i] , "120.123");
          strcpy(uploadTime[i] , "2015-11-11 11:11:11");
          strcpy(createTime[i] , "2015-11-11 11:11:11");
          strcpy(state_id[i] ,"1");
          strcpy(sys_state_id[i],"1");
     
          motor_idLen[i] = strlen( motorid[i] ) + 1;
          uploadTimeLen[i] = strlen( uploadTime[i] ) + 1;
          createTimeLen[i] = strlen( createTime[i] ) + 1;
          state_idLen[i] = strlen( state_id[i] ) + 1;
          sys_state_idLen[i] = strlen( sys_state_id[i] ) + 1;
          latLen[i] = strlen( lat[i] ) + 1;
          lonLen[i] = strlen( lon[i] ) + 1;
        }
     
        stmt->setDataBuffer(1, (dvoid*)motorid, OCCI_SQLT_STR,sizeof( motorid[0] ), motor_idLen);
        stmt->setDataBuffer(2, (dvoid*)lat, OCCI_SQLT_STR, sizeof( lat[0] ), latLen);
        stmt->setDataBuffer(3, (dvoid*)lon, OCCI_SQLT_STR, sizeof( lon[0] ), lonLen);
        stmt->setDataBuffer(4, (dvoid*)uploadTime, OCCI_SQLT_STR, sizeof( uploadTime[0] ), uploadTimeLen);
        stmt->setDataBuffer(5, (dvoid*)createTime, OCCI_SQLT_STR, sizeof( createTime[0] ), createTimeLen);
        stmt->setDataBuffer(6, (dvoid*)state_id, OCCI_SQLT_STR,sizeof( state_id[0] ), state_idLen);
        stmt->setDataBuffer(7, (dvoid*)sys_state_id, OCCI_SQLT_STR, sizeof( sys_state_id[0] ), sys_state_idLen);
     
        stmt->executeArrayUpdate(ArraySize);
        conn->terminateStatement(stmt);
        conn->commit();
     
        long a2= getCurrentTime();
     
        cout<<"插入"<<ArraySize<<"条数据完成"<<endl;
        cout<<"花费时间： "<<(a2-a1)<<endl;
     
    }


测试中是访问本地的Oracle数据库，用到的Makefile文件如下：

    CC=g++
    OBJS=TestOracle.o
    LIB=-L/opt/oracle/oracle11g/product/11.2.0/dbhome_1/lib -L/opt/oracle/oracle11g/product/11.2.0/dbhome_1/rdbms/lib/
    INCLUDE=-I/opt/oracle/oracle11g/product/11.2.0/dbhome_1/precomp/public -I/opt/oracle/oracle11g/product/11.2.0/dbhome_1/rdbms/public
    Test: $(OBJS)
        $(CC) -o Test $(OBJS) $(LIB) -locci -lclntsh
    TestOracle.o: TestOracle.cpp  
        $(CC) -c  TestOracle.cpp $(INCLUDE)
    clean:
        rm -rf *.o  & rm Test


程序的编写是参考网上的一篇文章，写的很好，这里作为参考：http://rgyq.blog.163.com/blog/static/3161253820131695957501/

    重用statement对象

每次创建statement对象时，需要在客户端和服务端分配资源，如内存和游标(cursor)，用于存储对象及数据。为了不必要的内存重分配，应重用statement对象。statement对象创建后，可以使用setSQL方法进行重用，例如：

    Connection* conn = env->createConnection();
    Statement* stmt = conn->createStatement();
    stmt->setSQL(“INSERT INTO fruit_basket_tab VALUES(‘Apples’, 3)”);
    stmt->executeUpdate();
    stmt->setSQL(“INSERT INTO fruit_basket_tab VALUES(‘Oranges’, 4)”);
    stmt->executeUpdate();
    stmt->setSQL(“INSERT INTO fruit_basket_tab VALUES(‘Bananas’, 1)”);
    stmt->executeUpdate();'
    stmt->setSQL(“SELECT * FROM fruit_basket_tab WHERE quantity > 2”);
    ResultSet* rs = stmt->executeQuery();

    statement参数化

为了进一步控制内存重新分配，可以通过参数化将前面3条SQL语句变成1条，然后设置参数，再执行。注意输入参数的类型变化，因为，每次改变参数类型都会触发重绑定。参数化示例如下：

    stmt->setSQL(“INSERT INTO fruit_basket_tab VALUES(:1, :2)”);
    stmt->setString( 1, “Apples” );
    stmt->setInt( 2, 3 );
    stmt->executeUpdate();
    stmt->setString( 1, “Oranges” );
    stmt->setInt( 2, 4 );
    stmt->executeUpdate();
    stmt->setString( 1, “Bananas” );
    stmt->setInt( 2, 1 );
    stmt->executeUpdate();

    批量更新

对于那些经常发生的操作，很多时间都浪费在与服务器网络通信中。OCCI提供了有效的机制用于在单次网络通信中发送多行信息。该优化可用于INSERTs,UPDATEs和DELETEs。首先，设置最大迭代次数，然后设置可变长度参数的最大长度。在迭代过程中参数类型不可变。具体细节参考OCCI Programmers Guide，第二章。下例是上面的INSERTs的优化：

    //prepare the batching process
    stmt->setMaxIterations( 3 );
    stmt->setMaxParamSize( 1, 8 ); //”Bananas” is longest param
    //batch the statements
    stmt->setSQL(“INSERT INTO fruit_basket_tab VALUES(:1, :2)”);
    stmt->setString( 1, “Apples” );
    stmt->setInt( 2, 3 );
    stmt->addIteration();
    stmt->setString( 1, “Oranges” );
    stmt->setInt( 2, 4 );
    stmt->addIteration();
    stmt->setString( 1, “Bananas” );
    stmt->setInt( 2, 1 );
    //execute the statements
    stmt->executeUpdate();

    Statement::setDataBuffer方法

绑定值到参数化statements的参数时需要内存拷贝，因为为了避免信息在中间执行过程中被覆盖，所以必须拷贝到内部的缓冲区中。拷贝的代价对于大字符串尤其明显，内存的消耗以及拷贝所花的时间。如果应用可以自己管理内存，就可以通过OCCI提供的方法最小化上述开销。

虽然许多OCI开发者使用OCCI简明的创建environments和statement对象，但仍然使用许多OCI中的类型。setDataBuffer方法允许OCI开发者执行数组更新，最小化网络通信次数。setDataBuffer方法与setXXX方法工作方法不同。一般说来，setXXX方法会将传过来的数据拷贝到内部缓冲区中，只要setXXX返回后参数值就可以被改变。然而，使用setDataBuffer方法可以避免将数据拷贝到内部缓冲区中。代价是应用程序在执行完statement之前不可以修改缓冲区。例如：

    // insert Bananas
    char buf[BUF_SIZE] = "Bananas";
    int quantity = 1;
    ub2 buflen = strlen( buf ) + 1;
    ub2 quantlen = sizeof(int);
    stmt->setDataBuffer(1, (dvoid*)buf, OCCI_SQLT_STR, buflen, &buflen);
    stmt->setDataBuffer(2, (dvoid*)&quantity, OCCIINT, quantlen,
    &quantlen);
    stmt->executeUpdate(); // executeArrayUpdate(1) also would work.
    // insert Apples
    strcpy( buf, “Apples” );
    quantity = 3;
    buflen = strlen( buf ) + 1;
    quantlen = sizeof( int );
    stmt->setDataBuffer(1, (dvoid*)buf, OCCI_SQLT_STR, buflen, &buflen);
    stmt->setDataBuffer(2, (dvoid*)&quantity, OCCIINT, quantlen,
    &quantlen);
    stmt->executeUpdate(); // executeArrayUpdate(1) also would work.
    //commit the transaction
    conn->commit();

setDataBuffer方法可以与迭代执行(iterative executes)和executeArrayUpdate方法结合使用。

    executeArrayUpdate方法

当进行大量INSERTs和UPDATEs操作时，可以通过executeArrayUpdate方法和setDataBuffer方法批量处理。这可以节省网络通信，提高吞吐量。示例如下：

    char fruit[][BUF_SIZE] = { "Apples","Oranges","Bananas","Grapes" };
    int int_arr[]={ 3,4,1,5 };
    ub2 fruitlen[4]; // array of size of individual elements
    ub2 intsize[4];
    for(int i=0 ; i<4 ; i++)
    {
    intsize[i] = sizeof(int);
    fruitlen[i] = strlen( fruit[i] ) + 1 ; // include the null
    }
    stmt->setDataBuffer(1, (dvoid*)fruit, OCCI_SQLT_STR, BUF_SIZE,
    fruitlen);
    stmt->setDataBuffer(2, (dvoid*)int_arr, OCCIINT, sizeof(int), intsize);
    stmt->executeArrayUpdate(4);
    conn->commit();


executeArrayUpdate方法不会执行，直到所有缓冲区均通过setDataBuffer方法设置。如果有参数需要调用setXXX方法赋值，可以调用setMaxIterations和setMaxParamSize方法，以及addIteration方法。具体如下：

    char fruits[][BUF_SIZE] = {“Apples”, “Oranges”, “Bananas”};
    ub2 fruitLen[3];
    for( int j=0; j<3; j++ )
    {
    fruitLen[j] = strlen( fruits[j] ) + 1; //include the null
    }
    stmt->setMaxIterations(3);
    //setDataBuffer only needs to be executed once
    //while all the other variables need to be set for each iteration
    stmt->setDataBuffer( 1, fruits, OCCI_SQLT_STR, sizeof(fruits[0]),
    fruitLen );
    stmt->setInt(2, 3); //Apple’s quantity
    stmt->addIteration();
    stmt->setInt(2, 4); //Orange’s quantity
    stmt->addIteration();
    stmt->setInt(2, 1); //Banana’s quantity
    //execute the iterative update
    stmt->executeUpdate(3);


    使用合适的Accessors和字符集

对操作的列使用合适的setXXX和getXXX方法，而非统一作为string处理，可以省去不必要的转换。
在NLS_LANG环境设置中使用合适的字符集，以避免获取字符串时不必要的字符集转换。

自动提交模式
由于所有的SQL DML都是在事务中执行，所以需要确认所有的DML。可以根据具体情况使用“Connection::commit”和“Connection::rollback”方法。“Statement::setAutoCommit”方法可以用来确认其后的每条语句。使用该方法可节省网络传输时间。

    //code with AutoCommit
    //transaction 1
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Apples”,3));
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Oranges”,4));
    stmt->setAutoCommit( TRUE );
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Bananas”,1));
    stmt->setAutoCommit( FALSE );
    //transaction 2
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Apples”,5));
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Oranges”,6));
    stmt->setAutoCommit( TRUE );
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Bananas”,2));
    stmt->setAutoCommit( FALSE );


这与下面的语句是等价的，但是2次网络传输，每个事务1次

    //code without AutoCommit
    //transaction 1
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Apples”,3));
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Oranges”,4));
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Bananas”,1));
    conn->commit();
    //transaction 2
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Apples”,5));
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Oranges”,6));
    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Bananas”,2));
    conn->commit();


建议AutoCommit只在每个事务的最后一条SQL语句前面使用。

    结果集对象的优化

结果集作为请求的响应返回。可以使用next和status方法处理结果集：

    ResultSet* rs = stmt->executeQuery( “SELECT * FROM fruit_basket_tab” );
    ResultSet::Status stat = rs->status(); //status is DATA_AVAILABLE
    while( rs->next() ) { //process data }
    setPrefetchRowCount and setPrefetchMemorySize


虽然rs->next()每次只能返回一行，但是可以在一次网络中预取多行放到客户端的缓存中。使用类Statement的setPrefetchRowCount方法和setPrefetchMemorySize方法，每次可以取得不止一行。上例优化后如下：

stmt->setPrefetchRowCount( 3 );
ResultSet* rs = stmt->executeQuery( “SELECT * FROM fruit_basket_tab” );
while ( rs->next() ) { //process data }
使用上述代码，在一次网络通信中就能取得3条记录。默认情形中，预取功能是启用的，每次多取一条。要想关闭预取功能，必须同时调用方法setPrefetchRowCount和setPrefetchMemorySize，参数为0。如果两个setPrefetchXXX方法都被调用了，那么实际预取的数目是这两个方法参数中较小的那个。

setMaxColumnSize
当取得的结果中有些列较大时，可以使用ResultSet::setMaxColumnSize方法限制从指定的列可以获取多少数据。当只对部分数据感兴趣或者缓存大小有限时，这就有用了。

ResultSet *rs = stmt->executeQuery( “SELECT description FROM
fruit_basket_tab” );
//want only first 80 characters from the description column
rs->setMaxColumnSize( 1, 80 );

    关闭结果集

当对结果集的处理结束后，调用Statement::closeResultSet方法手动强制关闭，这样数据库和OCCI的使用的资源就不必等待自动释放。

    终止Statement

最后，为了确保没有内存泄漏，并关闭相关的服务器端的游标，需要释放所有的statement对象。

conn->terminateStatement( stmt );
 

shenhua969
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值