CODEWITHSUNDEEP
c++mysqlsql
yihang hwang
yihang hwang

Reputation: 419

how to accelerate c++ for loop code into query using MySQL

code of c++ sub function

insert data into MySQL

bool mysqlinsert2(int sourceindex,int targetindex,string source,string target)
    {
        MYSQL *conn_ptr;
        conn_ptr = mysql_init(NULL);

        if(!conn_ptr)
        {
            fprintf(stderr,"mysql_init failed\n");
            //return EXIT_FAILURE;
        }

        conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","nlpgroup","testdb",0,NULL,0);

        if(conn_ptr)
        {
            int abc;
            char sql_buffer[500] = {0};
            sprintf(sql_buffer,"insert into EM2 (sourceindex,targetindex,source,target) values(\"%d\",\"%d\",\"%s\",\"%s\")",sourceindex,targetindex,source.c_str(),target.c_str());
            if((abc = mysql_query(conn_ptr,sql_buffer)))
            {
                printf("error code:%s\n",mysql_error(conn_ptr));
            }
            mysql_close(conn_ptr);
            return true;
            //connection succees
        }
        else
        {
            printf("Error code: %d  %s %s\n",mysql_errno(conn_ptr),mysql_sqlstate(conn_ptr),mysql_error(conn_ptr));
            mysql_close(conn_ptr);
            return false;
            //return EXIT_SUCCESS;
        }
    }

code for run loop and input the for loop index and value into MySQL by calling the function mysqlinsert2

void write_token_to_data()
    {
        typedef double* DynamicMatrix[l+m];
        // DynamicMatrix Count;
        typedef double* DynamicMatrix2[l+m];
        //DynamicMatrix2 Prob;
        for(int i=0; i<(l+m); i++)
        {
            for(int j=0; j<(l+m); j++)
            {
                if(mysqlinsert2(i,j,combine[i],combine[j]))
                {
                    cout<<"insert OK!!"<<endl;
                }
                else
                {
                    cout<<"insert failed"<<endl;
                }


            }
        }
        
    }//end of function

when MySQL insert ok the program will return the "insert ok!!" so , here 's my question

these query could be until 30000*30000 times and i want to know how to accelerate this, maybe i could optimize the MySQL setting or change the code in c++ , and i want it have same result

Upvotes: 1

Views: 472

Answers (3)

Keiji
Keiji

Reputation: 1042

Several improvements you can make:

  1. Place those two functions in a class, keep the MYSQL *conn_ptr as a member field on that class. Then your mysqlinsert2 function needs not create and destroy the connection every time.

    class MyInserter {
public:
  MyInserter() {
    conn_ptr = mysql_init(NULL);
    if (!conn_ptr) throw ...;
    conn_ptr = mysql_real_connect(conn_ptr, ...);
    if (!conn_ptr) throw ...;
  }
  ~MyInserter() {
    mysql_close(conn_ptr);
  }
  bool mysqlinsert2(...) { ... }
  void write_token_to_data() { ... }

private:
  MYSQL *conn_ptr;
}

  2. Use prepared statements and bound parameters. This will not only speed up your queries, but will also prevent SQL injection attacks (i.e. someone putting a quote character in the string you're inserting).

  3. Instead of using char sql_buffer[500] and sprintf, consider using std::ostringstream. This avoids buffer overflows and can make for more readable code. Note the below code would change if you were doing (2) above as well:

    std::ostringstream out;
out << "insert into EM2 (sourceindex,targetindex,source,target) values(\"" << sourceindex << "\",\"" << targetindex << "\",\"" << source << "\",\"" << target << "\")"";
std::string sql_string = out.str();
... mysql_query(conn_ptr, sql_string.c_str()) ...

Upvotes: 3

krobertson
krobertson

Reputation: 91

You are opening a new mysql connection for every insert which is going to hurt your performance a lot. Open a connection and keep it open until you have completed all of your inserts. Opening a new connection has significant overhead.

There is also overhead associated with transactions so you also most likely want to do many inserts per transaction. See this for details: using transactions

Upvotes: 2

Ashalynd
Ashalynd

Reputation: 12563

A couple of remarks:

  • move opening and closing MySQL connection outside of your mysqlinsert method. Opening a connection is a slow operation, so it makes sense to do it once and only reopen it if the connection is lost before you are finished;
  • using prepared SQL statement is also supposed to speed things up;
  • consider using batch inserts, so that you insert N rows at a time (try N=100, for example).

Upvotes: 2

Related Questions