MySQL Forums
Forum List  »  Stored Procedures

Insert many records using prepared insert and C API
Posted by: Morten Kvist
Date: May 01, 2009 04:02AM

I'm making a program in C++ that has to insert up to 10.000 records/second into a table.

I have decided to use the MySQL C API and prepared statement.
I have been following some examples about how to use the prepared statement in C, but when i try to use a for loop to insert data, then i can only insert about 1000 records/second.

I'm pretty sure that i haven't made the code efficient, but i can't figure out what the Bind[] are good for.

Here is what i have made/copied so far.



#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test1"
#define CREATE_SAMPLE_TABLE "CREATE TABLE test1 (Timestamp bigint, NameID integer, Value integer, PRIMARY KEY (Timestamp, NameID))"

#define INSERT_SAMPLE "INSERT INTO test1 (Timestamp, NameID, Value) VALUES(?,?,?)"

int _tmain(int argc, _TCHAR* argv[])
{




printf("MySQL client version: %s\n", mysql_get_client_info());

MYSQL *mysql; //conn;

mysql = mysql_init(NULL);

if (mysql == NULL) {
printf("Error %u: %s\n", mysql_errno(mysql), mysql_error(mysql));
//exit(1);
goto end;
}

if (mysql_real_connect(mysql, "localhost", "root", "pass", "testdb", 0, NULL, 0) == NULL) {
printf("Error %u: %s\n", mysql_errno(mysql), mysql_error(mysql));
//exit(1);
goto end;
}



MYSQL_STMT *stmt;
MYSQL_BIND bind[3];
my_ulonglong affected_rows;
int param_count;
int i;
short small_data, small_data2;
int int_data1, int_data2, int_data3;
char str_data[STRING_SIZE];
unsigned long str_length;
my_bool is_null;




// Prepare an INSERT query with parameters

stmt = mysql_stmt_init(mysql);

mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE));

// Bind the data for all parameters

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

// INTEGER PARAM
// This is a number type, so there is no need to specify buffer_length
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data1;
bind[0].is_null= 0;
bind[0].length= 0;

// Integer PARAM
bind[1].buffer_type= MYSQL_TYPE_LONG;
bind[1].buffer= (char *)&int_data2;
bind[1].is_null= 0;
bind[1].length= 0;

// Integer PARAM
bind[2].buffer_type= MYSQL_TYPE_LONG;
bind[2].buffer= (char *)&int_data3;
bind[2].is_null= 0;
bind[2].length= 0;

// Bind the buffers
mysql_stmt_bind_param(stmt, bind);


for(i = 0; i < 1000; i++)
{
// Specify the data values for the rows
int_data1= 10000 + i; // integer
strncpy(str_data, "MySQL", STRING_SIZE); // string
str_length= strlen(str_data);

// INSERT SMALLINT data as NULL
is_null= 1;

// Execute the INSERT statement - 1
mysql_stmt_execute(stmt);
/* */
}

//mysql_stmt_execute();

// Close the statement
mysql_stmt_close(stmt);

return 0;
}



Edited 1 time(s). Last edit at 05/01/2009 04:03AM by Morten Kvist.

Options: ReplyQuote


Subject
Views
Written By
Posted
Insert many records using prepared insert and C API
12175
May 01, 2009 04:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.