No I'm not talking about dead man walking. I'm talking about your database execution plan. I want to give fair warning to all of you Microsoft haters out there (and you know who you are) that I'm going to use lingo from Microsoft SQL Server 2000. It's a ubiquitous and full-featured database with good documentation regarding this subject. Much of what is said here applies to other databases as well (no doubt using different lingo). So please, feel free to post comments of how Oracle or MySQL or PostgreSQL or Interbase or your flat file - all have a great way of doing this. But please don't post about how your favorite DB is so great and Microsoft is the spawn of Satan. That's not helpful and it's makes me want to poke out my eye with an ice pick! Whew! Now that that's out of the way.
The database execution plan is the series of steps that a database takes to deliver a particular query or task. These steps are cached on the DB server. When you run a query it looks in the cache for a plan that matches. If it finds one, it uses it. If not, it creates a new one. Why is this important? Because the more often your DB Server finds a matching plan in the cache, the better it performs. In fact, it can run significantly faster when it is not tasked with constantly building execution plans from scratch. Here's the rub, much of the query code written in Coldfusion requires the RDBMS to compile a new execution plan. Here's why.
The query plan is the steps necessary to run the query. In it's simplest form:
You probably knew there was a catch didn't you. You see there is one more piece of data that the DB server needs to make a hit on the cache. It needs to know the "type" of the data in the context. Why? For 2 reasons. 1) Databases support conversion. For example, if you pass in 2.43 to a field that holds an INT you will end up with 2. The DB will know what to do with it. If you search "username = 1" (no single quotes) - the db may know enough to treat it as a string. 2) Databases have to "sort out" the list of key words from the list of parameters. If you do not specify a "type" for a parameter you force the DB to parse through the whole string and sort out the execution code from the context.
Let me illustrate it with some code. If you write a query like the one above, here's what the SQL server will see.
The solution is CFQUERYPARAM. With it you provide a "typed execution context". You tell the DB enough to look up the plan straightway. It doesn't even need to look for the column name types and match them with the context. Why? Because if it finds a cached execution plan it means that this query has been run successfully before, and the bindings ensure that the context data is typed correctly. It has a measure of guarantee that the plan will succeed. So, using the same query:
There are other reasons to use data binding (SQL Injection attack prevention is chief among them), but the performance benefit can be quite dramatic. I have seen as much as a 40% decrease in query execution time - just by adding cfqueryparam. Here's another tip, add the shell for CF_SQL_CHAR and CF_SQL_INTEGER to your IDE as a short cut (snippet). It will save you a lot of typing. These are by far the 2 most common types.
One more tip. On MS SQL server, fully qualifying the objects in the query can further increase your chances of hitting the cache. So "SELECT * FROM dbo.users" has a better chance than just "SELECT * FROM users". SQL 2000 is far superior to SQL 7 in this regard - but even it can benefit from fully qualified objects.