Making Decisions in the Database, Not in Your CF Code
Posted 15 October 2009
Like many people who develop Web applications, I don’t have a formal background in computer science. I’ve taught myself most of what I know, and I’ve been very fortunate to be the recipient of a lot of great knowledge from people much, much smarter than I. One of the areas in which my skills can be improved is with utilizing the full power of SQL. I’m no SQL expert, and while I’ve certainly read up on stored procedures, the variances of T-SQL (as I use MS SQL Server at work), and fun stuff like cursors (and how you should use them judiciously, at best), most of my ColdFusion queries revolve around four simple SQL statements: SELECT, INSERT, UPDATE and DELETE.
As I’ve been refactoring some existing code as part of the development of a new, modern codebase for one of our most important applications, I’ve been trying to ask myself “OK, if I’m making a series of queries, can’t I just do that all in the database instead?” The answer, often, is yes.
Here’s an example, and one that I think is quite common: You need to query the database to check on the existence or state or value in a particular record. Then, depending on the value returned from that first query, you either do an update or an insert in to the database, or nothing at all. I’ve covered the basics of the upsert (a conditional INSERT or UPDATE depending on the existence of a matching record in the database) in a previous post, but I’d like to take it a step further in this example.
To be more specific in this example, I track all logins to the site. However, if the user logs in more than once in 10 minutes, I don’t need to track that, as it’s considered to be part of the same session. If I were going old-school with my code, I’d probably do something like this:
- Query the database to get the last login time of the user.
- If there’s a record, compare the last login time to the current time, and if it is greater than 10 minutes ago, perform an update in the database.
- If there’s no record in the database, perform an insert in the database.
So that will work out to three different
There’s a simpler way to do all of this in the database. It involves using (gasp!) an IF tag in your SQL. Not too hard to do, really, and it takes care of everything in one query, rather than a series.
The code is as follows. I’ve included a “result flag” so you know whether any data update (insert or update) was performed.
IF statements in T-SQL work a whole lot like
So instead of making two or three