Which is faster multiple single INSERTs or one multiple-row INSERT

Question

I am trying to optimize one part of my code that inserts data into MySQL  Should I chain INSERTs to make one huge multiple-row INSERT or are multiple separate INSERTs faster

User · Answer

I know I m answering this question almost two and a half years after it was asked  but I just wanted to provide some hard data from a project I m working on right now that shows that indeed doing multiple VALUE blocks per insert is MUCH faster than sequential single VALUE block INSERT statements   The code I wrote for this benchmark in C  uses ODBC to read data into memory from an MSSQL data source   19 000 rows  all are read before any writing commences   and the MySql  NET connector  Mysql Data    stuff to INSERT the data from memory into a table on a MySQL server via prepared statements  It was written in such a way as to allow me to dynamically adjust the number of VALUE blocks per prepared INSERT  ie  insert n rows at a time  where I could adjust the value of n before a run   I also ran the test multiple times for each n   Doing single VALUE blocks  eg  1 row at a time  took 5 7 - 5 9 seconds to run  The other values are as follows   2 rows at a time  3 5 - 3 5 seconds 5 rows at a time  2 2 - 2 2 seconds 10 rows at a time  1 7 - 1 7 seconds 50 rows at a time  1 17 - 1 18 seconds 100 rows at a time  1 1 - 1 4 seconds 500 rows at a time  1 1 - 1 2 seconds 1000 rows at a time  1 17 - 1 17 seconds    So yes  even just bundling 2 or 3 writes together provides a dramatic improvement in speed  runtime cut by a factor of n   until you get to somewhere between n   5 and n   10  at which point the improvement drops off markedly  and somewhere in the n   10 to n   50 range the improvement becomes negligible   Hope that helps people decide on  a  whether to use the multiprepare idea  and  b  how many VALUE blocks to create per statement  assuming you want to work with data that may be large enough to push the query past the max query size for MySQL  which I believe is 16MB by default in a lot of places  possibly larger or smaller depending on the value of max allowed packet set on the server

User · Answer

https   dev mysql com doc refman 8 0 en insert-optimization html     The time required for inserting a row is determined by the following factors  where the numbers indicate approximate proportions          Connecting   3    Sending query to server   2    Parsing query   2    Inserting row   1    size of row    Inserting indexes   1    number of indexes    Closing   1       From this it should be obvious  that sending one large statement will save you an overhead of 7 per insert statement  which in further reading the text also says      If you are inserting many rows from the same client at the same time  use INSERT statements with multiple VALUES lists to insert several rows at a time  This is considerably faster  many times faster in some cases  than using separate single-row INSERT statements

User · Answer

Send as many inserts across the wire at one time as possible   The actual insert speed should be the same  but you will see performance gains from the reduction of network overhead

User · Answer

You might want to     Check that auto-commit is off Open Connection Send multiple batches of inserts in a single transaction  size of about 4000-10000 rows   you see  Close connection   Depending on how well your server scales  its definitively ok with PostgreSQl  Oracle and MSSQL   do the thing above with multiple threads and multiple connections

User · Answer

It s ridiculous how bad Mysql and MariaDB are optimized when it comes to inserts  I tested mysql 5 7 and mariadb 10 3  no real difference on those     I ve tested this on a server with NVME disks  70 000 IOPS  1 1 GB sec seq throughput and that s possible full duplex  read and write   The server is a high performance server as well  Gave it 20 GB of ram  The database completely empty     The speed I receive was 5000 inserts per second when doing multi row inserts  tried it with 1MB up to 10MB chunks of data     Now the clue  If I add another thread and insert into the SAME tables I suddenly have 2x5000  sec  One more thread and I have 15000 total  sec  Consider this  When doing ONE thread inserts it means you can sequentially write to the disk  with exceptions to indexes   When using threads you actually degrade the possible performance because it now needs to do a lot more random accesses   But reality check shows mysql is so badly optimized that threads help a lot   The real performance possible with such a server is probably millions per second  the CPU is idle the disk is idle  The reason is quite clearly that mariadb just as mysql has internal delays

User · Answer

In general  multiple inserts will be slower because of the connection overhead  Doing multiple inserts at once will reduce the cost of overhead per insert   Depending on which language you are using  you can possibly create a batch in your programming scripting language before going to the db and add each insert to the batch  Then you would be able to execute a large batch using one connect operation  Here s an example in Java

User · Answer

In general the less number of calls to the database the better  meaning faster  more efficient   so try to code the inserts in such a way that it minimizes database accesses   Remember  unless your using a connection pool  each databse access has to create a connection  execute the sql  and then tear down the connection   Quite a bit of overhead

User · Answer

multiple inserts are faster but it has thredshould  another thrik is disabling constrains checks temprorary make inserts much much faster  It dosn t matter your table has it or not  For example test disabling foreign keys and enjoy the speed   SET FOREIGN KEY CHECKS 0    offcourse you should turn it back on after inserts by   SET FOREIGN KEY CHECKS 1    this is common way to inserting huge data  the data integridity may break so you shoud care of that before disabling foreign key checks

User · Answer

MYSQL 5 5 One sql insert statement took  300 to  450ms  while the below stats is for inline multiple insert statments    25492 row s  affected  Execution Time   00 00 03 343 Transfer Time    00 00 00 000 Total Time       00 00 03 343   I would say inline is way to go

User · Answer

A major factor will be whether you re using a transactional engine and whether you have autocommit on   Autocommit is on by default and you probably want to leave it on  therefore  each insert that you do does its own transaction  This means that if you do one insert per row  you re going to be committing a transaction for each row   Assuming a single thread  that means that the server needs to sync some data to disc for EVERY ROW  It needs to wait for the data to reach a persistent storage location  hopefully the battery-backed ram in your RAID controller   This is inherently rather slow and will probably become the limiting factor in these cases   I m of course assuming that you re using a transactional engine  usually innodb  AND that you haven t tweaked the settings to reduce durability    I m also assuming that you re using a single thread to do these inserts  Using multiple threads muddies things a bit because some versions of MySQL have working group-commit in innodb - this means that multiple threads doing their own commits can share a single write to the transaction log  which is good because it means fewer syncs to persistent storage   On the other hand  the upshot is  that you REALLY WANT TO USE multi-row inserts   There is a limit over which it gets counter-productive  but in most cases it s at least 10 000 rows  So if you batch them up to 1 000 rows  you re probably safe   If you re using MyISAM  there s a whole other load of things  but I ll not bore you with those  Peace

[mysql] Which is faster: multiple single INSERTs or one multiple-row INSERT?

Examples related to mysql

Examples related to insert

Examples related to benchmarking