Is there any benefit to wrapping CFTHREAD around database inserts?

Question

We have a few thousand catalogs with pages that are accessed up to a half a million times each day. At the end of every page hit, we insert some of the CGI variables into a database. If a form was submitted or a search was performed, we insert some of that information to another database. No information needs to be returned from each of these database inserts. These inserts happen at the end of the page processing.

I've read that once a "run" thread is launched page processing continues and doesn't wait for a response. This seems like it would speed up the page being complete because it's not waiting for the queries in the pages to run. Is this correct?

Is there any benefit to putting these database inserts into their own thread like this?

<cffunction
    name="OnRequest"
    access="public"
    returntype="void"
    output="true"
    hint="Fires after pre page processing is complete.">

    <cfargument name="RequestedContent" type="string" required="true" />


    <!--- OUTPUT THE PAGE CONTENT --->
    <cfinclude template="#ARGUMENTS.RequestedContent#" />

    <cfscript>
        thread
            action="run"
            name="Tracking" {
            include "track1.cfm";
            include "track2.cfm";
        }
    </cfscript>

    <cfreturn />
</cffunction>

The benefit is that the user has a few fewer milliseconds to wait for the ColdFusion code to finish processing. — Dan Bracuk, Jul 10 '14 at 16:13
Setting the thread `priority="low"` might mitigate the potential overhead issues others have mentioned. — CfSimplicity, Jul 14 '14 at 08:01

score 3 · Answer 1 · answered Jul 10 '14 at 18:19

You are correct in that if you don't ever join the threads in the page then the page will finish sooner. The threads will potentially finish their execution after all content has been sent to the user and the http connection closed.

I would say this sounds like a bonafied use of that feature, but I also agree that if inserts are taking that much time you may want to look at how you are processing data.

score 2 · Accepted Answer · answered Jul 10 '14 at 18:02

I would say "no, there is [little] benefit in doing that". You'll save your user a few more ms, but you'd put your ColdFusion server under twice as much load, which in turn might cause a performance hit across the board. The server only has a finite number of threads available to use for all requests, so doubling the number you use for each request is gonna double the risk of using 'em all up.

Also starting a new thread has overhead in itself, so the gain you're giving your users here would not be linear.

If your insert queries are taking long enough that they are impacting your user experience, then what you should be doing is tuning those (at the DB end of things).

Also: unless you have a performance bottleneck on that code already, there's not really much point in prematurely optimising it.

score 2 · Answer 3 · answered Jul 10 '14 at 20:39

There can be table locking issues when inserting data into a table on every request, so a thread can potentially ease some of the variance to the end user for that variable insert time. I have seen this used with success on high volume sites. However, as Adam mentions, the threads are finite and you could end up tying up your threads anyhow during a deadlock issue for a process that really needs a free thread.

In this scenario, you might consider queuing up inserts in the application for a minute or two and then doing the bulk insert in a thread. This obviously has some risk to data loss if the server collapses before flushing the queue and it requires a bit more work to handle thread safety. However, if you don't need the inserted data immediately, it can work well.

Is there any benefit to wrapping CFTHREAD around database inserts?

3 Answers3