MySQL Batch Updates in C

Question

Hi,

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.

Answer 1

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."

Answer 2

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.