MySQL Bugs: #5453: Long inserts take very long time

Bug #5453	Long inserts take very long time
Submitted:	7 Sep 2004 18:38	Modified:	16 Nov 2004 1:57
Reporter:	Bob Dankert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.0	OS:	Windows (XP SP2)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Performing a long insert (160,000 text characters) takes a very long time to insert into the database.  (database located on 10/100 network)

How to repeat:
Perform a large insert

Hi Bob,

Is the same insert statement much faster when executed through the msyql text client?
You are connecting to an instance of mysqld on the same XP machine that your application
is running on?

Could you provide an example insert statement that is slow for you along with the DDL for the 
relevant tables?  This will allow me to determine the cause of the slowness.

Best Regards

Since you didn't receive any answer from the bug's originator, I decided to give you
some specific details myself, since I have the same problem.
I am connected to the mysql server via a 256K line.
I have built a database to hold files. Content is base64 encoded.
I have tried to upload a 70K file. Results :
1. via Connector : 1min48sec
2. via MySQL client : 4sec(!!!) same SQL command;
The tests where made on the same server via the same connection

Also it seems that the content is somhow corrupted if added via Connector.
For files of 2-20K everything seems ok although it seems that uploading ( not downloading !!!) time is a bit slowly. Btw, when selecting the same ammount of data from the mysql server the time is far less...

If you want the SQL command I have tested with, it is here : http://xentra.ro/instest.sql

I'm sorry, I missed the request for the information.  I was having the same results as previously mentioned, where I would encode a file (100k-200k, whatever) and try to insert it into the database with the connector.  I would get times in the range of minutes with the mysql server hosted on a dedicated server on my lan.  The times in the mysql client would be seconds, a fraction of the time the connector would take.  And, I experienced the same problem that large files would be corrupted often times, and frequently would give an error (something about parameters, if I recall, though I didnt use any parameters).  If needed, I can try to reproduce everything, but I am no longer using the .NET connector because it is far from a usable/stable product in its current form.

Thanks

I have also been bitten by this issue. I wrote what was in essence a mysqldump application but designed to convert MS Access databases to MySQL. It generated the DROP and CREATE table statements then would compose large INSERT commands up to a certain buffer size (just like mysqldump).  

When I tried send a 65K insert statement, with only about 300 rows of information per insert, through the .NET library, each INSERT took about 15 to 17 seconds to complete. As a test, I dumped those same INSERT commands to a text file and ran them through the MySQL client. Each of those inserts took less than .03 seconds to complete when using the MySQL client. What would have taken me almost 2 hours to import using just the .NET functions I was able to do in about 3 minutes by going through a text file.

Please upgrade the priority on this issue so that this SEVERE bottleneck can be resolved. -- Thanks!

Upgraded from serious->critical and medium priority to high priority.  This issue is very debilitating to many pieces of software.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html