Using <CFQUERY> opens up a whole range of Oracle functionality.
This includes calling your own functions, formatting data ready for your ColdFusion templates, and using Oracle bind variables to reduce the load on your Oracle database.
Lesson 1 in using Oracle with ColdFusion is that <CFINSERT> and <CFUPDATE> don't work - the database is accessed using either <CFSTOREDPROC> or <CFQUERY>, the last mentioned being the focus of this article. Hopefully I can provide insight into the wide range of Oracle functionality that can be drawn into your ColdFusion templates by effectively utilizing this one tag. Having opened with that rather profound statement, I'll immediately water it down by saying <CFQUERY> becomes a whole bunch better when used in conjunction with <CFQUERYPARAM>.
When I started at my current job I implemented <CFQUERY> across the board and saw performance improvements of up to 80%. It was like a dream. One thing to note when you try it yourself, however: the first time you execute the query shows no discernible difference in execution time whether or not you use the <CFQUERYPARAM> tag. Hang in there though, and execute the query again - you'll see a big difference.
This is because of the way Oracle executes a new query. It has to be parsed, the execution plan has to be determined, and then the query is executed. Oracle tries to avoid doing this over and over whenever possible, so it caches queries in the shared global area, or SGA, as it's known. If the same query is executed again, it doesn't have to repeat the whole process and therefore executes much faster.
For a real-world example look at the following queries. They retrieve profile information for visitors to a Web site (all driven by an ID stored in a cookie). As you might expect, it's likely that such queries will be run thousands of times a day. If you don't use <CFQUERYPARAM>, you'll have thousands of versions of what is effectively the same query, just using different IDs - all having to be parsed and validated, and all having to have execution plans determined. In short, you won't be maximizing your use of the SGA.
SELECT name, eye_color, shoe_size
FROM users
WHERE id = 12
SELECT name, eye_color, shoe_size
FROM users
WHERE id = 99
If you use <CFQUERYPARAM>, you capitalize on Oracle bind variables and a process called late binding. The query stored in the SGA looks something like the example below, where the :1 is a placeholder into which different parameters can be passed. As far as the SGA is concerned, it's running the same query over and over again, and each time it's executed it gets shifted to the top of the pile within the SGA. Hence it's much more likely that you'll have a cached copy to run. This is an effective use of the SGA, as the parsing and creation of the execution plan is carried out only once and hence is much faster.
SELECT name, eye_color, shoe_size
FROM table
WHERE id = :1
Another aspect to bear in mind is that Oracle's SGA is case sensitive and hence will regard
SELECT ID, NAME
FROM TABLE
as different from
select id, name
from table
The SGA won't recognize that they're the same, so you'll miss out on a performance gain.
Performance Drag
Carrying on with the performance theme, another possible drag is the lazy programmer - oh, yes, you know you're guilty - specifically:
SELECT *
FROM table
instead of listing the columns you need.
This isn't slower, I hear you protest, but it is. The reason lies in the way Oracle processes queries. If you use *, it has to go to the data dictionary and find what columns are in the table before the query can be executed. This adds another stage to the execution process, which naturally makes it slower. Also, why retrieve the columns back to ColdFusion if you're not going to use them? It increases the size of your ColdFusion page and the amount of network traffic. Even if you're running ColdFusion and Oracle on the same server, the data still needs to be transferred between applications, which can be a real performance lag.
On the same thread, why make more trips than you have to? By using the often overlooked <CFQUERY> attribute BLOCKFACTOR, you can prevent this too. What BLOCKFACTOR does is define how many records will be returned at once. The default is one, so if you have a query returning 50 records, that means 50 round-trips to Oracle. If you were to up the BLOCKFACTOR to 50, that's one round-trip to Oracle, which is faster, faster, faster, and that's what we like. Use this attribute sparingly, however, as it really works well only when you know how many rows are going to be returned. Setting this to the maximum 100 won't necessarily speed things up. In fact, you may even see performance degradation.
While I love speed, and endeavoring to tune my SQL gives me a warm feeling inside, there are a number of other neat things you can do with a <CFQUERY> tag. There are inserts, updates, and deletes, but my favorite by far is calling your own Oracle functions in your queries. It's fairly standard to be able to use Oracle functions such as COUNT, UPPER, and SUM in your queries, and you should use them where appropriate. After all, Oracle is very good at data manipulation; let's not forget that's what it's famous for!
I know that often ColdFusion has similar functions, but properly using Oracle will speed up your templates, and using Oracle functions to preformat data will reduce the template size, especially if you use a methodology like Fusebox, which promotes reuse of queries. You only have to do this formatting once in the query rather than many times in different display templates.
I digress. I was writing about calling your own Oracle functions. This was first introduced in Oracle 7 but has been simplified in Oracle 8 so all you have to do now is create your function and compile it successfully. Then you can call it something like this:
SELECT id, get_name(id, type) as full_name
FROM table
WHERE id = 33
This means you can carry out your own formatting, processing, and general data manipulation and have it output as if it were a column in the database. So what happens? Well, you're just calling a function as you would in normal PL/SQL and the returning value is output into, in this instance, the variable full_name. The use of the keyword "as" assigns the result of the function call to the variable "full_name". From that point on the output can be treated like any other column output from a query. Beware, though, this method really works only with data retrieval, formatting, and the like. If the function you're calling does something like an insert, update, or delete, it won't work, and ColdFusion won't let you know. So keep a keen eye out.
What if you just want to call a function without its being part of a query? All is not lost. I know ColdFusion doesn't provide an explicit method for calling functions, as it does for procedures, but don't lose faith. It can be done, and very simply. This is what you do:
SELECT get_name(id) as v_cheese
FROM dual
It looks just like the last example, doesn't it? And it almost is, except this select uses Oracle's "dual" table, a sort of psuedotable that's created by default in every Oracle installation. Without belaboring the point, this little baby enables you to call functions, your own or standard Oracle functions, in a standalone manner.
As you can see, there are loads of things you can do inside a <CFQUERY> tag, and it doesn't stop here, either. You can concatenate columns with columns, and even with strings, thus:
SELECT 'my Name is ' || firstname || lastname as full_name
FROM table
Or overcome those pesky date problems by outputting dates in Julian format for ordering, thus:
SELECT TO_CHAR(start_date, 'J') as julian_date
FROM table
I've covered barely a fraction of the power available within Oracle, but hopefully I've pointed you in the right direction or hinted at a hidden gem or two. Moreover, I hope I've shown that, in general, the leap to Oracle from any other database isn't that great, and you can gain very impressive results with minimal effort.
