| Bug #12115 | mysql_more_results needs a lot of time | ||
|---|---|---|---|
| Submitted: | 22 Jul 2005 14:40 | Modified: | 8 Nov 2005 14:13 |
| Reporter: | MySQL-Front Team | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S4 (Feature request) |
| Version: | 5.0.9-beta | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[22 Jul 2005 14:40]
MySQL-Front Team
[22 Jul 2005 15:06]
MySQL-Front Team
Sorry, my mistake: The problem based on the function mysql_next_result and not on mysql_more_results
[22 Jul 2005 20:14]
Jorge del Conde
Hi! Can you please send me a reproducible test-case that shows this behaviour ? I was unable to notice slowdowns while executing 100 queries. BTW, I tested this using our latest bk tree of 5.0 Thanks !
[22 Jul 2005 20:19]
MySQL-Front Team
I don't know what you are looking for? My source code of the program? It's written in Delphi. Or do you need SQL code? I've executed a script with 1300 records into different tables. The server is located on the same Windows XP computer. The execution of all of the mysql_next_result calles need together ~ 60 seconds. The execution of the mysql_real_query needs only 2-3 seconds.
[23 Jul 2005 5:07]
MySQL Verification Team
I tried to repeat the piece of code you showed with an C API application at the bottom. Doing 71 inserts and I got the below time: C:\temp>bug12115.exe Connected to the server: 5.0.9-beta-nt Result #: 1 Result #: 2 <cut> Result #: 71 Result #: 72 Time: (0.14 sec) bye C:\temp> #include <my_global.h> #include <m_string.h> #include "mysql.h" #define DB_HOST "localhost" #define DB_USER "root" #define DB_PASSW "" #define DB_NAME "test" #define DB_PORT 0 #define DB_UNIX_SOCKET NULL static void nice_time(double sec,char *buff,bool part_second); static ulong start_timer(void); static void end_timer(ulong start_time,char *buff); static void mysql_end_timer(ulong start_time,char *buff); char time_buff[160]; ulong timer; #define MULTI_QUERY "DROP TABLE IF EXISTS test;\ CREATE TABLE test(id INT);\ INSERT INTO test VALUES(1);\ INSERT INTO test VALUES(2);\ INSERT INTO test VALUES(3);\ INSERT INTO test VALUES(4);\ INSERT INTO test VALUES(5);\ INSERT INTO test VALUES(6);\ INSERT INTO test VALUES(7);\ INSERT INTO test VALUES(8);\ INSERT INTO test VALUES(9);\ INSERT INTO test VALUES(10);\ INSERT INTO test VALUES(11);\ INSERT INTO test VALUES(12);\ INSERT INTO test VALUES(13);\ INSERT INTO test VALUES(14);\ INSERT INTO test VALUES(15);\ INSERT INTO test VALUES(16);\ INSERT INTO test VALUES(17);\ INSERT INTO test VALUES(18);\ INSERT INTO test VALUES(19);\ INSERT INTO test VALUES(20);\ INSERT INTO test VALUES(21);\ INSERT INTO test VALUES(22);\ INSERT INTO test VALUES(23);\ INSERT INTO test VALUES(24);\ INSERT INTO test VALUES(25);\ INSERT INTO test VALUES(26);\ INSERT INTO test VALUES(27);\ INSERT INTO test VALUES(28);\ INSERT INTO test VALUES(29);\ INSERT INTO test VALUES(30);\ INSERT INTO test VALUES(31);\ INSERT INTO test VALUES(32);\ INSERT INTO test VALUES(33);\ INSERT INTO test VALUES(34);\ INSERT INTO test VALUES(35);\ INSERT INTO test VALUES(36);\ INSERT INTO test VALUES(37);\ INSERT INTO test VALUES(38);\ INSERT INTO test VALUES(39);\ INSERT INTO test VALUES(40);\ INSERT INTO test VALUES(41);\ INSERT INTO test VALUES(42);\ INSERT INTO test VALUES(43);\ INSERT INTO test VALUES(44);\ INSERT INTO test VALUES(45);\ INSERT INTO test VALUES(46);\ INSERT INTO test VALUES(47);\ INSERT INTO test VALUES(48);\ INSERT INTO test VALUES(49);\ INSERT INTO test VALUES(50);\ INSERT INTO test VALUES(51);\ INSERT INTO test VALUES(52);\ INSERT INTO test VALUES(53);\ INSERT INTO test VALUES(54);\ INSERT INTO test VALUES(55);\ INSERT INTO test VALUES(56);\ INSERT INTO test VALUES(57);\ INSERT INTO test VALUES(58);\ INSERT INTO test VALUES(59);\ INSERT INTO test VALUES(60);\ INSERT INTO test VALUES(61);\ INSERT INTO test VALUES(62);\ INSERT INTO test VALUES(63);\ INSERT INTO test VALUES(64);\ INSERT INTO test VALUES(65);\ INSERT INTO test VALUES(66);\ INSERT INTO test VALUES(67);\ INSERT INTO test VALUES(68);\ INSERT INTO test VALUES(69);\ INSERT INTO test VALUES(70);\ INSERT INTO test VALUES(71);" static void nice_time(double sec,char *buff,bool part_second) { ulong tmp; if (sec >= 3600.0*24) { tmp=(ulong) floor(sec/(3600.0*24)); sec-=3600.0*24*tmp; buff=int10_to_str((long) tmp, buff, 10); buff=strmov(buff,tmp > 1 ? " days " : " day "); } if (sec >= 3600.0) { tmp=(ulong) floor(sec/3600.0); sec-=3600.0*tmp; buff=int10_to_str((long) tmp, buff, 10); buff=strmov(buff,tmp > 1 ? " hours " : " hour "); } if (sec >= 60.0) { tmp=(ulong) floor(sec/60.0); sec-=60.0*tmp; buff=int10_to_str((long) tmp, buff, 10); buff=strmov(buff," min "); } if (part_second) sprintf(buff,"%.2f sec",sec); else sprintf(buff,"%d sec",(int) sec); } static ulong start_timer(void) { return clock(); } static void mysql_end_timer(ulong start_time,char *buff) { buff[0]=' '; buff[1]='('; end_timer(start_time,buff+2); strmov(strend(buff),")"); } static void end_timer(ulong start_time,char *buff) { nice_time((double) (start_timer() - start_time) / CLOCKS_PER_SEC,buff,1); } void main( void ) { MYSQL mysql; int Res = 0, x_loop = 0; mysql_init(&mysql); timer=start_timer(); if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME, DB_PORT,DB_UNIX_SOCKET,CLIENT_MULTI_STATEMENTS)) { printf("Error: %s\n",mysql_error(&mysql)); return; } else printf("Connected to the server: %s\n",mysql_get_server_info(&mysql)); mysql_query(&mysql,MULTI_QUERY); do { Res = mysql_more_results(&mysql); x_loop++; if ( Res ) printf("Result #: %d\n", x_loop); } while (!mysql_next_result(&mysql)); mysql_end_timer(timer,time_buff); printf("Time:%s\n", time_buff); printf("bye"); mysql_close(&mysql); }
[23 Jul 2005 7:46]
MySQL-Front Team
I used this code:
(Please don't hit me - I'm not a C++ developer and I don't tested the code - but I think you will understand the working of the code.)
start_time = clock();
mysql_real_query(&mysql,MULTI_QUERY);
real_query_time = clock() - start_time;
next_result_time = 0;
do
{
Res = mysql_more_results(&mysql);
if (Res) {
start_time = clock();
Res = mysql_next_result(&mysql);
next_result_time = next_result_time + clock() - start_time;
}
} while (! B);
CREATE TABLE test (id int) ENGINE=MyISAM;
-> real_query_time: 0.02 sec
-> next_result_time: 0.11 sec
CREATE TABLE test (id int) ENGINE=InnoDB;
-> real_query_time: 0.02 sec
-> next_result_time: 1.33 sec
I don't understand the difference, since I thought the execution of the query will be handled while mysql_real_query and not while mysql_next_result.
The result of next_result_time should be the same in both cases (MyISAM / InnoDB engine), isn't it?
Description of mysql_real_query:
> EXECUTES the SQL query
Description of mysql_next_result:
> If more query results exist, mysql_next_result() READS the next query
> RESULTs and returns the status back to application.
I think all results should be sent to client at once (if it's possible). Or does each calling of mysql_next_query contact the database to receive the next result?
The flag CLIENT_MULTI_STATEMENTS encloses the CLIENT_MULTI_RESULTS flag. I think this means that multiple results will be transfered from the server to the client at once.
If I'm wrong please take this as a suggestion to change it...
[27 Jul 2005 0:22]
MySQL Verification Team
"I think all results should be sent to client at once (if it's possible). Or does each calling of mysql_next_query contact the database to receive the next result?" For you understand well I quote the sample from our Manual: http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html do { /* Process all results */ ... printf("total affected rows: %lld", mysql_affected_rows(mysql)); ... if (!(result= mysql_store_result(mysql))) { printf(stderr, "Got fatal error processing query\n"); exit(1); } process_result_set(result); /* client function */ mysql_free_result(result); } while (!mysql_next_result(mysql)); In the above sample you use process_result_set(result); which is a client function you will create for to process every query processed in the while loop. That is logical because if you did several select queries you need to process the result sets one by one.
[27 Jul 2005 6:24]
MySQL-Front Team
Sorry, but you did not understand my suggestion. To reduce all misunderstandings please allow me very direct words: I think you API is designed bad and I suggest to you how you could it it make better and faster. If you are not interessed about suggestions like this please don't answer further more. Otherwise please try to understand the suggestion. > In the above sample you use process_result_set(result); > which is a client function you will create for to process > every query processed in the while loop. Your example doesn't solve the problem. The problem is that mysql_next_result contacts the database. My suggestion based on a change not to contact the database inside mysql_next_result. > That is logical because if you did several select > queries you need to process the result sets one > by one. Once again: Please don't try to explain the current working of the API to me. I suggest how you should make it better. To talk about this suggestion it doesn't help to explain the current state. I talked about several INSERT queries - not about SELECT queries. The database is able to execute all INSERT queries without needing any further information from the client. Since the database doesn't need further informations from the client the database is able to execute all queries without any further handshaking with the client. While executing each queries - one by one - the database can (and should) write each result of the query into a "Result Buffer". After all INSERT queries has been executed the "Result Buffer" can be send to the database at once. The database does not need any information from the client before send all results. Because of this there is no reason not to send all queries at once. Now lets talk about the SELECT queries. (To do this please lets talk about very small result sets first.) The database can execute several SELECT queries without any response from the client too: A SELECT query is nothing else than a INSERT query: The database has to execute the query and the result has to be sent to the client - without any possibility of the interact from the client. Since the result set are very small the database can send several result set at once - without any handshaking from the client. It's important that you understand: For INSERT queries and SELECT queries with a small result set all queries can be executed before the database send any information back to the client! The client (library) has to be separate all of this results to give it back to the application for each mysql_store_result / mysql_use_result / mysql_more_results / mysql_next_result. Ok, now lets talk about large result sets: Example: INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); INSERT INTO test VALUES (5); INSERT INTO test VALUES (123); SELECT 'Hello World!'; SELECT 'Test'; SELECT 'This is an other small result set'; SELECT * FROM VeryBigTable; Also in this example the results of the INSERT queries can be send to the client at once - together with the result sets of the first three SELECT queries. All together we're talking about lesser than 1000 bytes I think. Until now (without the result of the last SELECT query) there is no reason to split the results into separated handshaking between the client (library) and the database! (It's very important that you understand this!!!) But not only the result (sets) of the INSERT queries and the first three SELECT queries can be send at once. Also it's possible to send the first part of the big result set from the last SELECT query can be send to the client together with the first 6 results. After the client application fetched this first part the client has to contact the database to get the next part. We know this mechanism inside the mysql_store_result / mysql_use_result functions. But there is no reason why this mechanism is limited only to one result set. Indeed my suggests are maybe a revolution of the client / server communication. Maybe you forgot this while implementing the Multi Query Support. Maybe you wanted to reduce the work of changes while implementing the Multi Query Support. But in the moment we have a Multi Query Support - but we don't have a real Multi Result Support: There is still a handshaking between the client and the database after each separated query. This slow downs the client execution VERY MUCH!!! Please don't tell me the internal structure of the existing code doesn't allow a changing like described by me: I suggested how to implement a real Multi Result Support (sorry, I thought this were be a part of your Multi Query Support). Also I know this could force a lot of work on your end. But like shown in my InnoDB example we saw there is a requirement of this Multi Result Support: The execution time to insert 71 records into a local database of more than a second is very bad!!! I would be happy if you would try to understand me and my suggestion with a discussion inside the MySQL developer team. Sorry about the long comments from me - but I think you need this to understand my suggestion: The only reason why the libMySQL.dll contacts the database while executing mysql_next_result is not optimized implementation. There is NO real reason for this!
[29 Jul 2005 14:06]
MySQL Verification Team
I understand your point as feature request for to be analyzed by the development team. Thanks for the bug report.
[8 Nov 2005 14:13]
MySQL-Front Team
The developers are not interessed about our bug messages.
