Insert multiple lines in SQL

Question

Performance related:

Is it better to make 1000 queries to insert 1000 rows:

    for($i=1;$i<=1000;++$i){
         $query = 'insert into table1 (First,Last) values ("Fred","Smith")';
         mysql_query($query);
    }

Or to create a query using PHP (by concatenating values in a for loop) like this:

   $query = ' insert into table1 (First,Last) values ("Fred","Smith"),
      ("John","Smith"),
      ...
      ("Michael","Smith"),
      ("Robert","Smith")';
   mysql_query($query);

Given that there may be lots of values I chose to insert 10 at a time. I don't know if that's ok, what's the best practice in this case?

John Conde · Accepted Answer · 2013-02-02 20:47:23Z

6

The second is better. Connecting to the database is expensive.

Another way to say it, what's faster? Making 1,000 trips to drop off 1,000 boxes or one trip to drop off 1,000 boxes?

answered Feb 2, 2013 at 20:47

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

XCS Over a year ago

But what if the truck is not large enough to fit all those boxes? :) (query size limit? )

Michael Berkowski Over a year ago

@Cristy Then split it into the minimum number of trips containing the maximum number of boxes each. Still way faster than 1000 trips.

raina77ow · Accepted Answer · 2013-02-02 20:49:47Z

This question is, I suppose, answered well enough at the official site:

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.

I'd suggesting the whole page, though, as it lists several strategies for optimizing multi-inserts (for different types of engines). Yet at the very end it mentions that...

INSERT is still much slower for loading data than LOAD DATA INFILE, even when using the strategies just outlined.

... and that's quite true: if you need some huge (but static) set of data to be loaded into DB, use LOAD DATA INFILE statement instead.

echo_Me · Accepted Answer · 2013-02-02 20:48:14Z

0

the second is better one and you have much control over it , the first u dont have much control about to insert different values

answered Feb 2, 2013 at 20:48

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Comments

user1646111 · Accepted Answer · 2013-02-02 20:51:28Z

0

Its simple, cost for batch processing is less than one by one processing. its all about cost over-head, here it's a resource in terms of performance.

answered Feb 2, 2013 at 20:51

user1646111

Collectives™ on Stack Overflow

Insert multiple lines in SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related