Oracle OCCI BULK INSERT Data __oracle

Oracle OCCI BULK INSERT Data

When inserting data into Oracle with OCCI, it is inefficient to use autocommit data (default) to insert only 1000 data in the next second. Because inserting the database is the most efficient part of the system, it is necessary to increase the efficiency of the data insertion. After looking for some information on the Internet, you can reuse the statement object, use the method of bulk INSERT, first save the data in memory, accumulate to a certain value after the bulk inserted into Oracle, so that the average down a second can insert more than 5,000 data, performance improved, good, here to do a record.[CPP] View plain copy #include  <iostream>   #include  <string.h>   #include  <time.h>   #include  <sys/time.h>       #define   win32common //Avoid function redefinition errors    #include  <occi.h>   #include  <cstdlib>    #include  <map>   #define  arraysize 10000  // In-memory data full 1000 inserts into Oracle       using namespace oracle::occi;   using  namespace std;     /*   * Returns the current time used to calculate the two-operation difference    */   long  getcurrenttime ()    {      struct timeval tv;      gettimeofday (&tv,null);      return tv.tv_sec *  1000 + tv.tv_usec / 1000;  }     /*   *  @author : &NBSP;ROGER&NBsp  */   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 ();      &Nbsp;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,&nBsp;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<< "Insert" <<ArraySize<< "Piece of Data Complete" <<endl;       cout<< "Spending time: " << (A2-A1) <<endl;      }  
The test is to access the local Oracle database and the makefile files used are as follows:[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)-lo Cci-lclntsh Testoracle.o:testoracle.cpp $ (CC)-C TestOracle.cpp $ (INCLUDE) CLEAN:RM-RF *.O & RM Test
Program writing is a reference to the online article, write well, here as a reference: HTTP://RGYQ.BLOG.163.COM/BLOG/STATIC/3161253820131695957501/Reuse Statement objects

Each time you create a statement object, you need to allocate resources, such as memory and cursors (cursor), to the client and server side to store objects and data. The statement object should be reused for unnecessary memory redistribution. After the statement object is created, it can be reused using the SetSQL method, for example:[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 parameterization to further control memory reallocation, you can change the preceding 3 SQL statements into 1 by parameterization, then set the parameters, and then execute. Note the type change of the input parameter, because each change of the parameter type triggers the rebind. The parameterized examples are as follows:

[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 (); Bulk updates waste a lot of time in communicating with the server network for those operations that occur frequently. OCCI provides an effective mechanism for sending multiple lines of information in a single network communication. This optimization can be used for inserts,updates and deletes. First, set the maximum number of iterations, and then set the maximum length of the variable length parameters. The parameter type is immutable during the iteration. Specific details refer to OCCI Programmers Guide, chapter II. The following example is the optimization of the inserts above:

[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 method bound value to parameterA memory copy is required to statements parameters, because in order to avoid information being overwritten during intermediate execution, it must be copied into the internal buffer. The cost of copying is especially noticeable for large strings, memory consumption, and the amount of time it takes to copy. If the application can manage memory on its own, the overhead can be minimized by the method provided by Occi.

Although many OCI developers use OCCI to create environments and statement objects concisely, they still use the types in many OCI. The Setdatabuffer method allows OCI developers to perform array updates, minimizing the number of network traffic. The Setdatabuffer method differs from the Setxxx method's working method. In general, the Setxxx method copies the transmitted data into the internal buffer, and the parameter values can be changed as long as the setxxx returns. However, using the Setdatabuffer method avoids copying the data into the internal buffer. The price is that the application cannot modify the buffer until the statement is executed. For example:

[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,& nbsp;(d void*) Buf, occi_sqlt_str, buflen, &buflen);    Stmt->setDataBuffer (2,   (dvoid*) &quantity, OCCIINT, quantlen,    &quantlen);    Stmt->executeupdate ()  // executearrayupdate (1)  also would work.    The commit the transaction    conn->commit ();   Setdatabuffer method can be executed with iterations ( Iterative executes) is used in conjunction with the Executearrayupdate method.
The Executearrayupdate method can be processed in batches by Executearrayupdate method and Setdatabuffer method when a large number of inserts and updates operations are performed. This can save network traffic and improve throughput. Examples are as follows:

[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 ();
The Executearrayupdate method does not execute until all buffers are set by the Setdatabuffer method. If you have parameters that call the Setxxx method assignment, you can call the Setmaxiterations and Setmaxparamsize methods, and the Additeration method. Specifically as follows:

[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);   
Using the appropriate accessors and character sets

You can omit unnecessary conversions by using the appropriate setxxx and GetXXX methods for the columns of the action, rather than as a uniform string processing.
Use the appropriate character set in the Nls_lang environment settings to avoid unnecessary character set conversions when getting strings.

autocommit mode
Because all of the SQL DML is executed in a transaction, all DML needs to be confirmed. You can use the "Connection::commit" and "Connection::rollback" methods depending on your situation. The "Statement::setautocommit" method can be used to confirm each subsequent statement. Use this method to save network transmission time.

[CPP] view plain copy//code with autocommit//transaction 1 stmt->executeupdate (' INSERT into Fruit_bask    Et_tab VALUES ("Apples", 3)); Stmt->executeupdate ("Insert&n