List:MySQL++« Previous MessageNext Message »
From:Mr.Dragomir Stanchev Date:April 4 2007 6:47am
Subject:Re: bulk insert optimization required
View as plain text  
Hi,
I am not familiar with the mySQL++ API but only with the C API. I suggest
you use a feature that mySQL (but not PostgreSQL) has: the bulk insert
option. You could try to bypass the query.insert() function and do it more
or less that:

(you will need to use a prepared query)

You just have to execute the insert query by yourself -

Instead of "insert into table values(?,?,?)" you can do "insert into table
values(?,?,?),(?,?,?),(?,?,?),(?,?,?).....". Note, however that you will
have to bind more parameters to achieve that. Furthermore, you will have to
take care by yourself when you reach your vector#s end and there are NOT
enough rows to be inserted on the last loop.

I have tested that on a remote machine with 300 000 rows and it turned to be
~15seconds. It even outperformed Oracle :)

Here is a sample (no time to compile and remove any bugs, sorry)


 *#define INSERT_SAMPLE "INSERT INTO \ *

*test_table(col1,col2,col3) \*

*VALUES(?,?,?),(?,?,?),(?,?,?)"*

*MYSQL_STMT *stmt;*

*MYSQL_BIND bind[9];*

*my_ulonglong affected_rows;*

*int param_count;*

*short small_data;*

*int int_data;*

*char str_data[STRING_SIZE];*

*unsigned long str_length;*

*my_bool is_null;*

* *

*/* Prepare an INSERT query with 9 parameters */*

*/* (the TIMESTAMP column is not named; the server */*

*/* sets it to the current date and time) */*

*stmt = mysql_stmt_init(mysql);*

*if (!stmt)*

*{*

*fprintf(stderr, " mysql_stmt_init(), out of memory\n");*

*exit(0);*

*}*

*if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE)))*

*{*

*fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");*

*fprintf(stderr, " %s\n", mysql_stmt_error(stmt));*

*exit(0);*

*}*

*fprintf(stdout, " prepare, INSERT successful\n");*

*/* Get the parameter count from the statement */*

*param_count= mysql_stmt_param_count(stmt);*

*fprintf(stdout, " total parameters in INSERT: %d\n", param_count);*

*if (param_count != 9) /* validate parameter count */*

*{*

*fprintf(stderr, " invalid parameter count returned by MySQL\n");*

*exit(0);*

*}*

*/* Bind the data for all 9 parameters */*

*memset(bind, 0, sizeof(bind));*

*/* DATE PARAM */*

*vector<t2>::iterator i=col.begin();*

*for (int g=0;g<col.length();++g) {*

* *

*bind[g].buffer_type= **MYSQL_TYPE_DATETIME**;*

*bind[g].buffer= (char *)*i;*

*bind[g].is_null= 0;*

*//bind[g].length= 0;*

*}*

* *

*/* Bind the buffers */*

*if (mysql_stmt_bind_param(stmt, bind))*

*{*

*fprintf(stderr, " mysql_stmt_bind_param() failed\n");*

*fprintf(stderr, " %s\n", mysql_stmt_error(stmt));*

*exit(0);*

*}*

*/* Execute the INSERT statement - 1*/*

*if (mysql_stmt_execute(stmt))*

*{*

*fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");*

*fprintf(stderr, " %s\n", mysql_stmt_error(stmt));*

*exit(0);*

*}*

*/* Close the statement */*

*if (mysql_stmt_close(stmt))*

*{*

*fprintf(stderr, " failed while closing the statement\n");*

*fprintf(stderr, " %s\n", mysql_stmt_error(stmt));*

*exit(0);*

*}*

Hope it helps.

Regards,
Dragomir Stanchev


On 4/4/07, a b <imran.imran0@stripped> wrote:
>
> Hello,
>
> Currently i am using mysql++ for bulk insert.
> mysql++ only let me insert 2,000,000 records in approximately 2
> minutes with the help of mysqlpp::query::insert(iterator,iterator)
> method.
> But due to performance reasons, i want to insert these records in no
> more than 6-10 seconds.
>
> Can anyone tell me the alternative approaches for bulk insert?
>
>
> My source code is as follow:
>
> outer=400,inner=5000;
>
> mysqlpp::DateTime dt;
> time_t t=time(NULL);
> for(int i=0;i<outer;i++)
> {
> vector<t2> col;
> convert(t,dt);
> mysqlpp::DateTime dt1(dt);
> for(int j=0;j<inner;j++)
> {
> t2 e(j,dt1,1,1);
> col.push_back (e);
>
> }
> query.insert (col.begin (),col.end ());
> query.execute ();
> t=t+1;
> query.reset ();
>
>
> }
>
> regards,
> imran siddiqui
>
> --
> MySQL++ Mailing List
> For list archives: http://lists.mysql.com/plusplus
> To unsubscribe:
> http://lists.mysql.com/plusplus?unsub=1
>
>

Thread
bulk insert optimization requireda b4 Apr
  • Re: bulk insert optimization requiredMr.Dragomir Stanchev4 Apr
    • Re: bulk insert optimization requiredWarren Young5 Apr
      • Re: bulk insert optimization requireda b5 Apr
        • Re: bulk insert optimization requiredWarren Young5 Apr
          • Re: bulk insert optimization requiredM Imran Siddiqui5 Apr
            • Re: bulk insert optimization requiredWarren Young6 Apr