Bulk Inserting Data

One of the operations that we do often is insert multiple rows into a database.  Your inclination is probably to just loop over a cfquery and do the inserts.  While this will work it creates some excess overhead.  

Your code may look something like this:

<cfloop array="#users#" index="u"> 
<cfquery name="insertData">
insert into mytable
(firstname, lastname, email)
values
(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#u.firstname#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#u.lastname#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#u.email#">
)
</cfquery>
</cfloop>

While this fundamentally works it can cause performance issues.  Depending on configuration, a new database connection needs to be established for each insert.  Also, the database can’t optimize anything as it is going to handle the inserts one at a time.

The better way to handle this is to bulk load them in a single query.  Like so:

<cfquery name="insertData"> 
insert into mytable
(firstname, lastname, email)
values

<cfloop from="1" to="#arraylen(users)#" index="u">
<cfif u NEQ 1>,</cfif>
(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].firstname#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].lastname#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].email#">
)
</cfquery>
</cfloop>

 

This sends a single query to the database.  The database can then easily handle this and insert rows much faster.  You could also use this method to do updates as well.   The syntax is slightly different for updating but the principles are still the same.

Now, this is not a silver bullet.  There are issues that can arise from this method.  

  • You could run into some data locking issues.
  • It is possible to overflow the query buffer size or the max param limit.
  • A single error when looping could cause the whole batch to fail.

This example is MSSQL2008 + specific but you should easily be able to convert it to other databases as necessary.  

I hope this helps the next time you have to do some bulk loading and want to find a different way to do it.

Leave a reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.

Related