I have a program (VB.net) that inserts a large amount of records (10,000 records +) into a MySQL database. It takes forever to import these records. In the MySQL admin under server connections and threads i see the queying coming through and the state was "Freeing items" after every insert.
Anyone got some ideas on how to speed this bulk insert up?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi,
Firstly i would disable the keys if possible before inserting - then activate them again after you have finished.
If you are doing this in one large insert I would advise against it, on my servers the optimum number of records to insert in one statement seems to be around 500 - so if I loop and do 20 insert statements rather than 1 (for 10 000 records) it seems to perform better.
try not do to it one record at a time,
You can use syntax like
inert into tableName (field1, field2, field3) values (1,2,3), (4,5,6), (7,8,9);
which would insert 3 rows at a time.
If you can get a statement that is approx 500 rows at a time, then insert it - less freeing of resources needs to be done.
Additionally - I'd suggest that you use connection pooling and do not close the database connection - this will also be very slow
for more about the syntax use : http://dev.mysql.com/doc/r
Hi Ronayers,
The reason why it is taking a long period of time is because you are basically reconnecting to the server after each row. It associates a lot of overhead for each sql insert. You should open the connection once, perform all operations (you should be able to do this as you are simply looping over a text file) and then close the connection.
You should also follow Raynard7's advise and see if you can perhaps send inserts in the bulk of 500 rows or something. That will also cut down on time.
You also can dramatically reduce insertion time (from hours to milliseconds), if you build a text file of the inserting data, and use a LOAD DATA INFILE statement to load it. This statement performs thousands times faster than regular inserts.
See the syntax here: http://dev.mysql.com/doc/r
Pay attention to permissions.
>>>Do you suppose to disable keys after each query or do it just once?
YOu should disable keys before the bulk insert and enable after all the insert are completed. However, please keep in mind that if other programs or clients are trying to access records from the same table, it will take them a long time if the keys are disabled. Also, if the ratio of new addition of rows to existing rows is very high, disabling & enabling keys could actually take longer.
Business Accounts
Answer for Membership
by: akshah123Posted on 2007-03-01 at 13:28:14ID: 18635971
Two questions to understand your current situation.
1. Are you providing one inserting 1 row at a time or multiple rows?
2. Are you opening and closing the connection after each insert?