November 17, 2017
Bulk Inserting Data
Comments
(3)
November 17, 2017
Bulk Inserting Data
This space intentionally left blank.
Newbie 1 posts
Followers: 2 people
(3)

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.

3 Comments
2020-04-02 20:22:49
2020-04-02 20:22:49

I think you’ve got your closing cfquery and cfloop tags reversed in your bulk load.

Like
(2)
2019-07-31 02:29:17
2019-07-31 02:29:17

In the case of the example using MSSQL2008, the maximum number of rows that can be inserted at a time this way is 1000. But if you’re using query params, which you should be, you will likely encounter another issue first which is the 2100 parameter limit.

Like
(1)
(1)
>
KamasamaK
's comment
2019-08-30 22:02:50
2019-08-30 22:02:50
>
KamasamaK
's comment

The 2100 brick wall is exactly what happened to me.  But I like this angle for smaller tables.

Like
Add Comment