How to insert a very large number of records into a MySql database as fast as possible

by

There are a number of ways to optimize bulk inserts. Some are:

  • LOAD DATA INFILE. There is a wrapper API for .NET. This is the fastest way, but has some limitations and semantic differences from simple inserts.

  • Multiple-row INSERT statements:

    INSERT INTO temperature (temperature) VALUES (1.0), (2.0), (3.0), ...

    You should not insert 20.000.000 rows at once, but may want to try 1.000-10.000 for a very large speed-up. This is a simple and very unproblematic way to increase speed. A factor of 10 and sometimes way more is often possible.

  • Locking the table (LOCK TABLES).

  • Disabling indexes temporarily.

  • MySQL options tuning.

  • INSERT DELAYED (most likely not that useful here).

The documentation does give you more elaborate detail on the options. Some options depend on the table type (InnoDB vs. MyISAM).

A general suggestion: Always specify the columns that you insert in front of VALUES. This makes for more maintainable code.

More Related Contents:

  1. How can I use a MySql User Defined Variable in a .NET MySqlCommand?
  2. How do I add a reference to the MySQL connector for .NET?
  3. Unable to convert MySQL date/time value to System.DateTime
  4. How do I get an animated gif to work in WPF?
  5. Could not load type ‘System.Runtime.CompilerServices.ExtensionAttribute’ from assembly ‘mscorlib
  6. Image UriSource and Data Binding
  7. How to implement shared memory in .NET?
  8. What are the deficiencies of the built-in BinaryFormatter based .Net serialization?
  9. What’s the difference between .NET Core, .NET Framework, and Xamarin?
  10. Log4Net: set Max backup files on RollingFileAppender with rolling Date
  11. System.Threading.Tasks – Limit the number of concurrent Tasks
  12. .NET How to check if path is a file and not a directory?
  13. How to tell if a .NET application was compiled in DEBUG or RELEASE mode?
  14. .NET – Dictionary locking vs. ConcurrentDictionary
  15. What is the difference between HashSet and List?
  16. SendInput and 64bits
  17. How do the .NET Framework, CLR and Visual Studio version numbers relate to each other?
  18. Retrieve the current view name in ASP.NET MVC?
  19. WPF – How to force a Command to re-evaluate ‘CanExecute’ via its CommandBindings
  20. Display a tooltip over a button using Windows Forms
  21. How can I make a DateTimePicker display an empty string?
  22. Anatomy of a “Memory Leak”
  23. DataGridTextColumn.IsReadOnly seems to be faulty
  24. Why doesn’t Dapper dot net open and close the connection itself?
  25. Dapper and SQL Injections
  26. Can strong naming an assembly be used to verify the assembly author?
  27. Linq to Objects: does GroupBy preserve order of elements?
  28. Drawing Pixels in WPF
  29. Opening XPS document in .Net causes a memory leak
  30. Does Console.WriteLine block?

Leave a Comment