MySQL Forums
Forum List  »  Performance

mysql_next_result() seems slow
Posted by: Iain Gilfeather
Date: March 18, 2005 06:06AM

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. [,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) ) {}

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;
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().....
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

Options: ReplyQuote

Written By
mysql_next_result() seems slow
March 18, 2005 06:06AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.