Oracle occi 批量插入資料__Oracle

Oracle occi 批量插入資料

在用OCCI 向Oracle中插入資料時，效率不高，使用自動認可資料的情況（預設）下一秒鐘只能插入1000條資料左右。因為插入資料庫這塊是影響系統中效率最明顯的地方，因此很有必要提高資料插入這塊的效率。在網上找了一些資料後發現，可以重用statement對象，使用批量插入的方法，先把資料儲存在記憶體中，積累到一定數值之後批量插入Oracle，這樣平均下來一秒鐘可以插入5000多條資料，效能有所提高，不錯，這裡做一個記錄。 [cpp] view plain copy #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檔案如下： [cpp] view plain copy 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方法進行重用，例如： [cpp] view plain copy 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條，然後設定參數，再執行。注意輸入參數的類型變化，因為，每次改變參數類型都會觸發重綁定。參數化樣本如下：

[cpp] view plain copy 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的最佳化：

[cpp] view plain copy //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之前不可以修改緩衝區。例如：

[cpp] view plain copy // 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方法批量處理。這可以節省網路通訊，提高輸送量。樣本如下：

[cpp] view plain copy 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方法。具體如下：

[cpp] view plain copy 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”方法可以用來確認其後的每條語句。使用該方法可節省網路傳輸時間。

[cpp] view plain copy //code with AutoCommit    //transaction 1    stmt->executeUpdate(“INSERT INTO fruit_basket_tab VALUES(“Apples”,3));    stmt->executeUpdate(“INSERT&n