SQL Tricks: What's an Upsert?
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:
<cftry>
<cfquery name="updQ" datasource="#APPLICATION.dsn#">
UPDATE dbo.tblATrack
SET tsLast = <cfqueryparam value="#CreateODBCDateTime(ARGUMENTS.aTrack.gettsLast())#" cfsqltype="cf_sql_timestamp" />
WHERE intAID = <cfqueryparam value="#ARGUMENTS.aTrack.getintAID()#" CFSQLType="cf_sql_integer" />
AND chServer = <cfqueryparam value="#ARGUMENTS.aTrack.getchServer()#" cfsqltype="cf_sql_char" maxlength="2" />
IF @@rowcount = 0
BEGIN
INSERT INTO dbo.tblATrack (
tsStart,
intAID,
tsLast,
chServer )
VALUES (
<cfqueryparam value="#CreateODBCDateTime(ARGUMENTS.aTrack.gettsStart())#" CFSQLType="cf_sql_timestamp" />,
<cfqueryparam value="#ARGUMENTS.aTrack.getintAID()#" CFSQLType="cf_sql_integer" />,
<cfqueryparam value="#CreateODBCDateTime(ARGUMENTS.aTrack.gettsLast())#" CFSQLType="cf_sql_timestamp" />,
<cfqueryparam value="#ARGUMENTS.aTrack.getchServer()#" cfsqltype="cf_sql_char" maxlength="2" /> )
END
</cfquery>
<cfcatch type="database">
<!--- Error Handler Here --->
</cfcatch>
</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.
http://www.kylehayes.info/blog/index.cfm/2007/4/23...
it also means you can track your missing record using some CFML
http://www.psoug.org/reference/merge.html
If you want to take db calls and network overhead into account. Your example is sending twice as many parameters for every query and around 2-4 times the query text. Plus you now have two insert statements in your DAO which just feels kinda wrong to me.
As your example is in a DAO, three lines of code would achieve things in a cross db manner, just after the closing CFQUERY tag add.
<cfif updQ.recordcount eq 0>
<cfset insert(arguments)/>
</cfif>
It's actually effectively the same code isn't it? The 'challenge' for me is more often how to keep things really simple and expressive (maintainable)?
Your example looks like it's a bit different to basic form DAO CRUD, in those kind of situations, i usually pass in a pk of -1 to indicate a new record to be inserted and thus i can avoid that pesky old exists() query
To each his own, everyone must use what works best for them and their application.
Therefore, you can't just pass arguments to it in a cfc as this demonstration is trying to show. You would have to make two DB calls.
INSERT INTO mytable (pkColumn,column1,column2) VALUES (1,'a','b')
ON DUPLICATE KEY UPDATE column1='a',column2='b'
That's it. No, really. Sheer beauty.
Julian.
One possibility here, though I'm not sure if there is a benefit...
To keep from sending the same parameter twice (in the case of a failed update resulting in a insert), you could Declare / Set each param in the SQL. It requires 2 additional lines per variable though. It'd be interesting to hear your DBA's recommendation on such:
DECLARE @tsLast datetime
SET @tsLast = <cfqueryparam value="..." cfsqltype="cf_sql_timestamp" />
Then in your update / inserts you just reference @tsLast.
It may be overkill in this situation, but I know of some nasty selects I've seen before that reference the same argument.myVar about 5 or six times. Seems declare and setting there would be more efficient, as opposed to having 5 sql params with the same value.