mysql c api: inserting numbers without converting them to a string

Question

I have to put tons of integers and floats into a mysql database from a c application.

Currently I am doing this by converting all those values into a string and then transfering them to the database:

char * sql_query[2048];
sprintf(sql_query, "INSERT INTO my_table VALUES (%u, %u, %d, %.7f, %u, .....");
if (mysql_real_query(&mysql, sql_query, strlen(sql_query)) {
    fprintf(stderr, "SQL Request failed: Error: %s\n", mysql_error(&mysql));
}

As this way is quite time consuming: Isn't there a way to avoid a conversion to string which needs to be interpreted and converted back by mysql. I'm looking for a binary API, which directly swallows my variables. Does that exist?

kfmfe04 · Accepted Answer · 2012-01-26 14:17:45Z

The overhead caused your sprintf() will be eclipsed by the gazillions of individual INSERTs that you are sending to Mysql (if you do not believe me, try profiling your application). In other words, you are very likely optimizing in the wrong place, if you are looking for higher throughput.

A much better approach is to write all your records out to a CSV file (I use pipe-delimited myself) and then load the entire batch via LOAD DATA INFILE (which has been heavily optimized for speed). These types of tools have been used for decades, starting all the way back from Sybase's bcp (bulk-copy) utility.

At first, it would appear that taking two steps (writing to file and then loading) would be slower, but the low overhead of one giant load handily beats the cost of hitting the database with a flood of individual INSERT transactions.

I have loaded millions of records quickly by using CSV and LOAD DATA INFILE, equally fast from C or from a scripting language like Ruby, since the server will be doing most of the heavy lifting.

Otoh, if you only have a handful of records, your current approach is perfectly fine: it's easily maintainable in case the schema changes or you decide to port to a server other than Mysql.

jackdoe · Accepted Answer · 2012-01-26 12:41:17Z

1

you can use statements:

http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-type-codes.html

http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-data-structures.html

http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-function-overview.html

but you will probably gain more performance by combining the insert queries into one big query

http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

because the overhead from snprintf can not even begin to compare to multiple mysql_real_query() calls

edit:

also noticed you are not using snprintf(), i strongly recommend using it :

snprintf(query,sizeof(query),...)

edited Jan 26, 2012 at 12:41

answered Jan 26, 2012 at 12:35

jackdoe

1,8761 gold badge15 silver badges13 bronze badges

Collectives™ on Stack Overflow

mysql c api: inserting numbers without converting them to a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related