Insert many records using prepared insert and C API
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.