Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

A common pattern I find in web applications that store large amounts of data is the practice of using a loop to wrap a sql "INSERT" statement to insert multiple rows. For example consider the following code to insert 5 near identical rows.

Note: While the below code is ColdFusion I have seen this pattern used in many other languages.

<cfloop from="1" to="5" index="i">
<cfquery name="simpleInsert" datasource="mydatasource">
INSERT INTO sometable (col1,col2,col3)
VALUES (#i#,'somename','sometitle')
</cfquery>
</cfloop>

While this code does work and produces the desired results it does have a drawback. The drawback being that each insert is a separate call to the database. This can create some unexpected problems when you start to scale up.

Partial Success

If you run the above code and a single insert fails all the previously inserted rows will still be there. This makes re-running the insert problematic as you need to work out what did and what did not get inserted successfully.

What About Transactions

You can of course use transactions to do a rollback so that if any insert fails they all fail. While this does solve the partial success problem it still does not address the issue of performance.

Scaling And Locking

While it may not be a big problem with 5 rows, if you scale the loop value in the above code up to 10,000 rows (or 100,0000) you suddenly have a different situation. You are now hitting the database with 10,000 individual calls in a very short amount of time.

While databases can handle this number of calls you also have to remember that you are locking the table 10,000 times which can slow performance for other users (unless you allow dirty reads). In addition the database must also continue serving all the other process's.

Network Performance

Another thing which gets overlooked very often is network speed. Many databases are housed on separate machines to the application server (sometimes in different physical locations) and communicate over the network. If your application is sending 10,000 (or more) individual insert statements that is potentially a lot of network traffic in a short amount of time.

Multi Row Inserts

The solution to this problem is a multi-row insert. To do this you write a single insert statement which contains all the values to be inserted in one pass. The implementation of this is slightly different for each database but they all show a significant performance improvement.

Note: I have not determined what the maximum number of inserts you can do at once is but I have run up to a thousand myself with no issues. I suspect that the maximum number is significantly higher.

Multi Row Insert In MySQL

To do a multi row insert in mysql is a simple matter of creating a comma delimited list of value groups.

 INSERT INTO sometable (col1,col2,col3)
VALUES
(1,'somename','sometitle'),
(2,'somename','sometitle'),
(3,'somename','sometitle'),
(4,'somename','sometitle'),
(5,'somename','sometitle')

Appling this principle to the first code block

<cfquery name="simpleInsert" datasource="mydatasource">
INSERT INTO sometable (col1,col2,col3)
VALUES
<cfloop from="1" to="5" index="i">
<cfif i GT 1>,</cfif>
(#i#,'somename','sometitle')
</cfloop>
</cfquery>

Note the "if" condition check to add a separator after the first value group.

Multi Row Insert In SQL Server 2005

To accomplish this in SQL Server 2005 we use "SELECT value,value,value UNION ALL". Otherwise execution is exactly the same.

 INSERT INTO sometable (col1,col2,col3)
SELECT 1,'somename','sometitle' UNION ALL
SELECT 2,'somename','sometitle' UNION ALL
SELECT 3,'somename','sometitle' UNION ALL
SELECT 4,'somename','sometitle' UNION ALL
SELECT 5,'somename','sometitle'

Multi Row Insert In SQL Server 2008

Finally SQL Server 2008 allows you to use either the method used by SQL Server 2005 OR the method used by MySQL. My preference would be to use the MySQL method only because each insert line in the sql would be smaller in size due to the shorter text string.

Comparing

In order to demonstrate the speed difference between the two approaches I put together this simple video. As you can see the single call method is significantly faster.

Note: Best viewed full screen.

Speed Test Summary

 MySQLSQL Server 2005
Multiple Call Method52.573 seconds3.569 seconds
Single Call Method0.191 seconds0.051 seconds

Conclusion

Now when you look at it you could make the point that very few web applications would need to do this many inserts from a single entry point. Normally you would only be inserting one or two records at a time for say a single user. However if you have some type of data sync process being called then this technique would be applicable.

In addition if you were seeing a very high volume of inserts to a particular table and you could allow a small time lag then you could potentially put in place some type of application level queuing. This would do a single multi-line insert whenever a certain time frame had elapsed or the queue had exceeded a specified number of new records.

What Do You Think?

Reader Comments

matt's Gravatar
matt
Tuesday, January 17, 2012 at 8:03:05 PM EST

i was actually trying to do this today and was getting an error when I saw this pop up on twitter. I was only doing a UNION rather than a UNION ALL. You saved me about 2 minutes since I didn't have to find a previous insert to look at ;)

Steven Neiland's Gravatar
Steven Neiland
Tuesday, January 17, 2012 at 10:31:05 PM EST

@mrbusche Glad I could help :-)

Steve Bryant's Gravatar
Steve Bryant
Thursday, January 19, 2012 at 10:05:05 AM EST

Great stuff!

I would also add that if your data is coming from the database then you could also write an INSERT INTO SELECT statement which could perform even better.
http://www.1keydata.com/sql/sqlinsert.html

Your solution is ideal, of course, for data coming from outside the database, but I thought I would add that for anyone that has data coming from elsewhere in the database.

Steven Neiland's Gravatar
Steven Neiland
Thursday, January 19, 2012 at 10:38:13 AM EST

Good point. That is actually a natural progression from my example above but it's good to point it out. Thanks :-)

softies's Gravatar
softies
Wednesday, January 2, 2013 at 7:57:43 AM EST

for this you have to use while loop.
$size = count($_POST['address']);

$i = 0;
while ($i < $size) {
$address= $_POST['address'][$i];
$id = $_POST['id'][$i];

$query = "UPDATE students SET address = '$address' WHERE id = '$id' LIMIT 1";
mysql_query($query) or die ("Error in query: $query");
echo "$address<br /><br /><em>Updated!</em><br /><br />";
++$i;
}
?>

Steven Neiland's Gravatar
Steven Neiland
Wednesday, January 2, 2013 at 6:34:13 PM EST

Not sure what you are trying to say there @softies?

bhargav's Gravatar
bhargav
Tuesday, July 16, 2013 at 6:51:52 AM EDT

I want to insert 2 or 3 rows using single insert query

ex:i have 2 text box contact1 & contact 2 when I press inseret button then values from both text box inserted in data base.

my data base have only 2 columns id and contact

Greg Davis's Gravatar
Greg Davis
Monday, July 22, 2013 at 11:39:35 AM EDT

Just as a note to future folks looking into this and your using Oracle. Unfortunately Oracle puts an insert limit of 1000 cumulative columns with an INSERT ALL. So if your table has 10 columns you are inserting and you insert 99 rows, all is good (10*99=990). If you insert 100 rows in the same 10 column table (10*100=1000), oracle throws the following: 'ORA-24335 - cannot support more than 1000 columns'. This is a hard limit set by Oracle.

Steven Neiland's Gravatar
Steven Neiland
Monday, July 22, 2013 at 11:43:05 AM EDT

Thanks for the info Greg.

jlig's Gravatar
jlig
Wednesday, November 20, 2013 at 9:20:41 AM EST

Steven, great post.. have a CF time tracking form with three row matrix-type page.. need to insert the column/row/intersect values (when filled in by user)

- Not sure how to create the INSERT statement?

- Here is my post on Stack Overflow: http://stackoverflow.com/questions/20011661/how-to-save-3-rows-of-data-into-a-mysql-database

nadiah's Gravatar
nadiah
Saturday, November 23, 2013 at 1:34:58 PM EST

Hi there. I'm currently doing my school project. I have a form that allow users to add more and more form by clicking "add row" button.but the problem now, the form only has one submit button. then when user click on submit button, only one form can be add into the database. the other form that added through "add row" button cannot be insert into database.
this is my action code for my form:

<?php
//TO ADD DATA
if (isset($_POST['add'])) {
$perName = addslashes($_POST['perName']);
$perIc = addslashes($_POST['perIc']);
$accId = addslashes($_POST['accId']);
$perAddress = addslashes($_POST['perAddress']);
$perPoscode = addslashes($_POST['perPoscode']);
$perCity = addslashes($_POST['perCity']);
$perState = addslashes($_POST['perState']);
$perPhone2 = addslashes($_POST['perPhone2']);
$perPhone3 = addslashes($_POST['perPhone3']);
$perPhone4 = addslashes($_POST['perPhone4']);

//$caseId = addslashes($_POST['caseId']);
//$guarantorIc = addslashes($_POST['guarantorIc']);
//$guarantorName = addslashes($_POST['guarantorName']);

//$defIc = $defIc++;


include 'dbconnect.php';

$query = "INSERT INTO person (perName, perIc, accId, perAddress, perPoscode, perCity, perState, perPhone2, perPhone3, perPhone4) VALUES
('$perName', '$perIc', '$accId', '$perAddress', '$perPoscode', '$perCity', '$perState', '$perPhone2' , '$perPhone3' , '$perPhone4')";


echo $result = mysql_query($query);

if ($result)
echo '<script type="text/javascript">
alert("Add Success!");
document.location = "guarantorAdd.php";
</script>';

else
echo 'Add failed';

}
?>

Steven Neiland's Gravatar
Steven Neiland
Tuesday, November 26, 2013 at 8:53:31 PM EST

Jlig,
If i understand you correctly you have a form with a grid of fields setup in a 3 row table.

In that case its a simple matter of using a simple naming convention where each field has a row identifier in its name. I.e. username_1 username_2 username_3 and then build your sql accordingly when the form gets submitted.

Steven Neiland's Gravatar
Steven Neiland
Tuesday, November 26, 2013 at 8:59:14 PM EST

Nadiah,
Im not a php person but even if i was its not exactly clear what your problem is.

What i would suggest though is looking into checking the name or value of the submit button that is clicked as you can have 2 submit buttons. The one that gets clicked is the one that sends its name/value to the server. Hope this helps.

Jlig's Gravatar
Jlig
Monday, April 21, 2014 at 5:39:22 PM EDT

Steven, I have already named my fields uniquely but need to tell the code to "not insert" blank values?

- Here is my post on Stack Overflow: http://stackoverflow.com/questions/20011661/how-to-save-3-rows-of-data-into-a-mysql-database
Here is a part of the code..
<td><input type="number" pattern="[0-9]*" name="mon1" id="mon1" value="8" size="10" maxlength="5" data-mini="true"></td>
<td><input type="number" pattern="[0-9]*" name="tue1" id="tue1" value="8" size="10" maxlength="5" data-mini="true"></td>
<td><input type="number" pattern="[0-9]*" name="wed1" id="wed1" value="8" size="10" maxlength="5" data-mini="true"></td>
<td><input type="number" pattern="[0-9]*" name="thu1" id="thu1" value="" size="10" maxlength="5" data-mini="true"></td>
<td><input type="number" pattern="[0-9]*" name="fri1" id="fri1" value="" size="10" maxlength="5" data-mini="true"></td>
<td><input type="number" pattern="[0-9]*" name="sat1" id="sat1" value="" size="10" maxlength="5" data-mini="true"></td>
<td><input type="number" pattern="[0-9]*" name="sun1" id="sun1" value="" size="10" maxlength="5" data-mini="true"></td>
<td><input type="text" pattern="[0-9]*" name="total1" id="total1" value="" size="10" maxlength="5" data-mini="true"></td>
<td>

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub