4

i need to insert 1000-30000 lines at a time (made of 19 elements each) into a mysql table from php using pdo prepared statements. I was asking myself if it would be better to do many different inserts or one big multi insert, like:

INSERT INTO table (a,b,c,...) VALUES (value0a, value0b, value0c,...), (value1a, value1b, value1c,...), ..., (value10000a, value10000b, value10000c,...)

VS exec each insert inside a transaction

INSERT INTO table (a,b,c,...) VALUES (value0a, value0b, value0c,...);
INSERT INTO table (a,b,c,...) VALUES (value1a, value1b, value1c,...);
INSERT INTO table (a,b,c,...) VALUES (value2a, value2b, value2c,...);
...
INSERT INTO table (a,b,c,...) VALUES (value10000a, value10000b, value10000c,...);

looks like a multi-insert is better, so do i have to know how many lines i need to insert and create a (?,?,?,...) placeholders for them and then bind them in a loop? considering that PDOStatement::debugDumpParams() is not showing params values, how do i echo the whole query as it will be inserted?

Gotrekk
  • 494
  • 2
  • 15
  • According to the docs on the mysql site it is faster to use the first method where you have one insert statement – Professor Abronsius Oct 08 '15 at 08:08
  • 2
    I actually did some informal benchmarks on this very topic a couple of weeks ago. As usual, it isn't as clear cut as it first appears. Although, once you have the `multi insert` statement, it is faster by about 30-40% than a single prepared insert in a for each loop with binding. The times were around 2 seconds and 3 seconds respectively on my pc here. The issue was the cost of generating the `multi insert` statement in PHP. That took about 8 seconds. IF you can generate the multi insert statement efficiently then it will be quicker. If you are doing it a lot it is worth the extra complexity. – Ryan Vincent Oct 08 '15 at 08:33
  • Would be nice if this "thread" could dbe merged with http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert- this one includes prepared statements while the other does not. – VolkerK Oct 08 '15 at 14:01

5 Answers5

2

When you prepare a statement, it is lexed once and execution plan is ready. What's left is to fill in the data. This is much, much better for several reasons

  • Lexing done once
  • Execution plan is ready
  • You won't have issues with max_packet_size because if you send bulk inserts and if the query is large, MySQL can refuse it
  • It's easier to use such a statement in a loop, provide data and execute

The issue of speed is related to your hard disk. Basically, if you start a transaction, issue 100 (or 200) inserts and then commmit the transaction - you will see a huge increase in speed. That's how we achieve fast insert rates, by spending 1 I/O and using a lot of disks' bandwith.

Mjh
  • 2,904
  • 1
  • 17
  • 16
  • 2
    This is a good compromise between code complexity and performance. I use this approach first - it is normally fast enough. Even on pc it will insert many hundreds of records per second. – Ryan Vincent Oct 08 '15 at 08:42
1

How often do you do this? In case, you will do this often (once a day, more times over a week), try to get a mix of "many-inserts-per-line" and "many-line-inserts", so you have 5 to 10 inserts in a row.

Paladin
  • 1,637
  • 13
  • 28
0

just from here: Which is faster: multiple single INSERTs or one multiple-row INSERT?

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.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.

Community
  • 1
  • 1
chris
  • 125
  • 13
  • 1
    How do you arrive at "overhead of 7 per insert statement"? And how does using prepapred statements count? – VolkerK Oct 08 '15 at 08:02
  • That part is copied from a previous answer I think – Professor Abronsius Oct 08 '15 at 08:07
  • first of all this is right, this is copied, but I also say this in the first line. I knew this theme and this was the first answere as an instance – chris Oct 08 '15 at 08:12
  • In most cases, when you can copy-paste an answer from another question means the new question is a duplicate -> flag it as duplicate instead of copying the answer. – Pred Oct 08 '15 at 08:28
  • False conclusion. Naturally, it is faster to parse the query once - and that's **exactly** what prepared statements are for - MySQL parses it once and then you just feed the data. You avoid many problems that way and if you want really fast insertion, you wrap inserts with a transaction block. – Mjh Oct 08 '15 at 08:40
-1

It is always good to have as much less SQL queries as possible. So doing single big insert is better as it will reduce database interaction and save lots of your processing time.

Vivek Srivastava
  • 569
  • 4
  • 13
-1

Faster way two insert data in table

   INSERT INTO your_tbl
        (a,b,c)
    VALUES
        (value0a,value0b,value0c),
        (value1a,value1a,value1a)
Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20