Coldfusion Database Basics

This isn't about how to write queries, or stored procedures. It's also not about relational databases function or how to properly index. It is about being kind to your database and more efficiently communicating with it.

One of the great things about CFML is how easy it is to communicate with the database and how incredibly easy it is to write queries. We don't need to concatenate a huge string to create a query with variables we just need to surround it with #'s. I remember how painful it was to create a query in ASP and how I rejoiced in the ease of CFML.

Stored Procedures

We all know that farming out the heavy lifting to the database is the right thing to do. Typically the DB is a beefy machine with lots of horsepower and ram so why do we keep doing so much work in CF? One theory is that we are web developers not database experts, we can write simple queries and some complex ones too but stop at stored procedures and user defined functions etc.

Well I am going to tell you again that creating stored procedures for complex database work is ALWAYS going to be more efficient than doing the same work in CF.

Aside from learning how to create stored procedures in your favorite database there are some things you can do to make your work with DB's better.

CFQUERYPARAM

We all know that Adobe recommends using the cfqueryparam to help prevent sql injection attacks but there is an even better reason to use the tag, performance. When you submit a query to your database it runs it through an optimizer and generally stores the result in a system table, when the same query is run again the already optimized query is retrieved from the system table so you save the optimizer time. By using parameters in your query via the cfqueryparam tag the database can more easily reuse the query. This is especially important when it's a frequently used query.

Don't do in CF what you can do in the DB

If you need to do some math, totals or otherwise it's best to do it with SQL. We all know the basic SQL aggregate functions the most popular being sum() and count(), max()

So what I am about to show you is something that you would never consider doing EVER!

The example might be a bit over the top, no one would ever consider doing something so inefficient but when we reach the end of our comfort zone in SQL we start getting inventive and thats when things generally start to fall apart.

The who? no the Where

The WHERE clause is your friend, in most cases he is all that stands between you getting the right results in a timely manner.

If you are writing a query with an inline view be sure to restrict your results there too, this will greatly speed up your query.

Here is also where you would use the cfqueryparam, and don't forget that you can still use the cfqueryparam with a list of values, it's just not for a single value.

Query of Queries and In-memory Queries

Query of Queries is a great tool but it's use, like in-memory queries, should be, in my opinion, limited. When you use QoQ the results, like that of the initial query are stored using the servers memory therefore heavy usage will also have a negative affect on your severs memory.

QoQ can be used to easily get column totals on a returned query instead of assigning a column value to a variable you can simply use QoQ to get the sums.

Insert Into Select...

I can't tell you how often I see a select query followed immediately with a loop that loops over the results of the query and then immediately inserts that data into another table.

The above code is bad for a number of reasons least of which is the network traffic as it chugs through the returned result set. A better way to tackle this is the use of an "Insert into select" statement, an even better approach would be to create an update trigger on the table that automatically inserts the old record into your audit table every time a record in the table is updated. Now if your Database System doesn't support triggers then that's another problem altogether.

Stored prcoedures

I mentioned above that I wasn't going to talk about how to write stored procedures and I am not. What I am going to say to this topic is to learn how to write them...correctly. Like in everything there is a right way and a wrong way to writing stored procedures and I encourage you to learn the right way for your database (in sqlserver try to avoid exec() with dynamic sql for example). Once you have learned the right way Coldfusion provides a great mechanism to communicate with your new found stored procedure writing genius.

CFStoredproc.

The CFStoredproc tag, in conjunction with cfprocparam and cfprocresult, allows you to call stored procedures and get results back from the stored procedure. The cfprocparam tag lets you pass variables to the stored procedure as well as get single results back. If your stored procedure returns a recordset then you need to use the cfprocresult tag.

Conclusion

In the end be kind to your database and your database will be kind to you. You database server is typically going to be a brute he likes to lift heavy things so let him do the heavy lifting he is better and faster at it.

Learn your Database (especially if you don't have a DBA on staff to do all the dbwork for you) find out about indexes, stored procedures, inline views and built-in functions, the more work you do on the database the better. In my opinion CF should only be used for simple selects, updates and deletes, anything more complex should be left to the database where it belongs. Lastly please for the love of God don't use CFML to compile statistics!

Happy Coding....