So, despite the debate on utilizing an Active Record (sometimes called Table Row) design pattern, there are times when it can be incredibly useful. Especially when dealing with simple forms that deal with a single record within a table (go figure). It's also really handy if you're utilizing something like The Illudium PU-36 Code Generator to auto-generate your data modeling. But, occasionally, you hit a glitch and need to rethink.

One of the great things about working where I do is that we have two full-time database administrators. These guys are nothing but head-on experts in SQL, specifically MS SQL. That is their sole mission, to monitor and refine our SQL processes. Now, we have a lot of strictly SQL side processes that churn through data all day long, but these guys also watch SQL threads being processed, identify issues in various execution plans, and assist our entire development team in learning to write better SQL within our code. Now, none of our guys are slouches at SQL anyway. They can all write fairly complex query statements, stored procedures, and manage transactions, but sometimes things come up that it's nice to have a specialist's eyes.

The other day I ran into just this type of issue. I had a very vanilla row object, just four data fields, two of which formed the table's unique key. Now, the DAOs, created by Illudium, have a simple save() method. It takes a bean object, passes it to an exists() method, which in turn queries the database to find out if a record already exists with the keys identified in the bean. If the record doesn't exist it will return false, to which the exists() method will then pass the bean into a create() method. If the record does exist then the exists() method will pass the bean to an update() method. All pretty simple and straightforward. I've seen several different ways of writing the Active Record pattern, and many of them have some permutation of this action, either in the DAO or the Gateway. My problem, though, is that I was getting cfquery timeouts, for no true apparent reason. So, I called in John to look at it with me.

Now, I'll start this with saying that there wasn't anything wrong with the code the way it had been generated. Everything should have functioned as planned. In fact my process was looping over a large structure to insert or update records in the database, and the exists() method's query was only timing out about once in 1900 records. But it was erroring, so I asked for a specialist. I showed my code to John, explained exactly what was happening with each record iteration, and John said "Why are you doing all of this? Why don't you just write an Upsert?" What's an Upsert? Well, what does it sound like? An Upsert is a single query block which will update an existing record if it's found, or create a new record if not. And it is ridiculously easy:

view plain print about
1<cfset var updQ = "" />
2        <cftry>
3            <cfquery name="updQ" datasource="#APPLICATION.dsn#">
4                UPDATE     dbo.tblATrack
5                SET        tsLast = <cfqueryparam value="#CreateODBCDateTime(ARGUMENTS.aTrack.gettsLast())#" cfsqltype="cf_sql_timestamp" />
6                WHERE    intAID = <cfqueryparam value="#ARGUMENTS.aTrack.getintAID()#" CFSQLType="cf_sql_integer" />
7                AND        chServer = <cfqueryparam value="#ARGUMENTS.aTrack.getchServer()#" cfsqltype="cf_sql_char" maxlength="2" />
8
9                IF @@rowcount = 0
10                    BEGIN
11                        INSERT INTO dbo.tblATrack (
12                            tsStart,
13                            intAID,
14                            tsLast,
15                            chServer )
16                        VALUES (
17                            <cfqueryparam value="#CreateODBCDateTime(ARGUMENTS.aTrack.gettsStart())#" CFSQLType="cf_sql_timestamp" />,
18                            <cfqueryparam value="#ARGUMENTS.aTrack.getintAID()#" CFSQLType="cf_sql_integer" />,
19                            <cfqueryparam value="#CreateODBCDateTime(ARGUMENTS.aTrack.gettsLast())#" CFSQLType="cf_sql_timestamp" />,
20                            <cfqueryparam value="#ARGUMENTS.aTrack.getchServer()#" cfsqltype="cf_sql_char" maxlength="2" /> )
21                    END
22            </cfquery>
23            <cfcatch type="database">
24                <!--- Error Handler Here --->
25            </cfcatch>
26        </cftry>

Man, I love these guys. I went from three methods [save() - exists() - update()/create()] with two separate hits to the database server, to a single, simple save() method with one hit to the database server. Now, this code is specific to MS SQL, but I would be surprised if MySQL, Oracle, and PostgreSQL didn't have similar processes to draw from (if you know the answer to that please post it below). Now I'll have to adjust Illudium's XSLT doc for the DAO creation, but this can save quite a bit of overhead in certain circumstances.

This might've been old hat to some of my readers out there, but I thought it worth sharing for those who are like I was. Those who didn't know. I hope this nugget helps someone. If so, feel free to comment.