Does MySQL's C API support batch updates? I'm writing an application where certain transactions are processed in large batches. It will grossly inefficient if in the logging process I wind up performing a single call DB for each insert.
Does C have anything similar to Java's APIs for batch SQL updates?
Thanks,
N.
You have a few options to help speed up INSERT in MySQL:
You can prepare a query and execute it with different values. This has less overhead than preparing and execute a query for each row inserted. See "C API Prepared Statement Function Overview."
You can insert multiple rows with one INSERT statement. See "INSERT Syntax."
You can execute multiple statements in one call with the API support. See "C API Support for Multiple Statement Execution."
You should definitely use explicit transactions (assuming you use InnoDB) to avoid the overhead of starting and committing a transaction per row.
There are other tips for improving speed of INSERT in "Speed of INSERT Statements."
The greatest performance for bulk data loads is achieved through LOAD DATA INFILE. This can be twenty times faster than using INSERT. See "LOAD DATA INFILE Syntax."
Yes there is, even though there isn't any separate function for it. Take a look at:
http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
You have to specify that mysql_real_query() should parse multiple statements separated by ; in advance, by enabling a flag either when connecting or using mysql_set_server_option().
mysql_set_server_option(&mysql, MYSQL_OPTION_MULTI_STATEMENTS_ON);
This is how php implements mysqli_multi_query():
PHP_FUNCTION(mysqli_multi_query)
{
// ...
MYSQLI_ENABLE_MQ;
if (mysql_real_query(mysql->mysql, query, query_len)) {
// ...
MYSQLI_DISABLE_MQ;
// ...
RETURN_FALSE;
}
RETURN_TRUE;
}
The MYSQLI_ENABLE_MQ macro evaluates to the code I provided above.
