Ree wrote:I often need to insert multiple records into tables. I wonder if there is much of a difference (performance-wise) to use
Code: Select all
INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32),
(2, 'Fabrizio', 'Macaroni', 'IT', 28),
(3, 'Ronald', 'McDonald', 'US', 100)
instead of
Code: Select all
INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32)
INSERT INTO users VALUES (2, 'Fabrizio', 'Macaroni', 'IT', 28)
INSERT INTO users VALUES (3, 'Ronald', 'McDonald', 'US', 100)
?
There is a HUGE performance difference between the two.
Code: Select all
INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32),
(2, 'Fabrizio', 'Macaroni', 'IT', 28),
(3, 'Ronald', 'McDonald', 'US', 100)
Is many times faster than using single queries.
In the game I work on I use stacked queries for inserting records into a link list. I can insert 10,000 records in 1.6 seconds using 100 stacked queries with 100 inserts compared to about 6-7 seconds using 10,000 queries. It reduces the load on the database server.
If you are importing alot of data it's the only way to go if you want speed and low load.