Using the C API and MySQL Server 4.1 i'm finding the multiple statement approach very slow compared to processing single statement queries.
Using some code based on the documentation example of how to handle multiple statement queries, the first call to mysql_next_result() can take anywhere from 40 to 200 milliseconds. Further calls are being timed at 0 milliseconds but if there are enough statements in the query, periodically they will take as long as the first.
Using the same server setup, equivalent calls to mysql_query() are being timed at between 0 and 30 milliseconds. With some further testing, the conclusion I've reached is that it is almost always quicker to send a query as separate statements rather than all at once.
Is this known to be the case in general or does it sound like I might be missing something? I hope this is the relevant forum and any comments would be much appreciated.
The only other post that seems to indicate someone having a simlar problem is here. [
http://forums.mysql.com/read.php?25,7737,8024#msg-8024 ]
--------------------------
my code:
...
mysql_real_connect( ..., CLIENT_MULTI_STATEMENTS );
/* Single Query Execution */
printf("\n\nNOT Using mysql_next_result().....\n");
startTicker = ticker = GetTickCount();
for( int i = 0; i < 6; i++ )
{
mysql_query(mysql, SingleQuery
);
results = mysql_store_result(mysql);
/* Process all results */
while( results && NULL != mysql_fetch_row(results) ) {}
mysql_free_result(results);
printf("%3dms - for SQL: %s\n", GetTickCount() - ticker, SingleQuery);
ticker = GetTickCount();
}
printf("%3dms - Total\n", GetTickCount() - startTicker);
/* Handling of Multiple Query Execution */
printf("\n\nUsing mysql_next_result().....\n");
printf("SQL:\n%s\n", MultipleQuery);
startTicker = ticker = GetTickCount();
mysql_query(mysql, MultipleQuery);
statement = 0;
do
{
results = mysql_store_result(mysql);
/* Process all results */
while( results && NULL != mysql_fetch_row(results) ) {}
printf("%3dms - Statement %d\n", GetTickCount() - ticker, statement++);
ticker = GetTickCount();
} while (!mysql_next_result(mysql));
printf("%3dms - Total\n", GetTickCount() - startTicker);
...
--------------------------
sample output:
NOT Using mysql_next_result().....
0ms - for SQL: DROP TABLE IF EXISTS test_table;
16ms - for SQL: CREATE TABLE test_table(id INT);
0ms - for SQL: INSERT INTO test_table (id) VALUES(10);
0ms - for SQL: UPDATE test_table SET id=20 WHERE id=10;
15ms - for SQL: SELECT * FROM test_table;
16ms - for SQL: DROP TABLE test_table;
47ms - Total
Using mysql_next_result().....
SQL:
DROP TABLE IF EXISTS test_table;CREATE TABLE test_table(id INT);INSERT INTO test_table (id) VALUES(10);UPDATE test_table SET id=20 WHERE id=10;SELECT * FROM test_table;DROP TABLE test_table;
0ms - Statement 0
156ms - Statement 1
0ms - Statement 2
0ms - Statement 3
0ms - Statement 4
0ms - Statement 5
156ms - Total