ColdFusion and SQL Server

Query caching. In some cases, using query caching can yield a bigger performance gain than using stored procedures. Stored procedures are an efficient way to set up and use queries. But if you want to maintain a dynamic area of your site, query caching is the perfect tool. For example, we use query caching on the Windows 2000 Magazine home page to check for changes or new content based on the time frame we determine. Listing 3 shows an example of a query we use to pull recent news stories from the database. The query retrieves the most recent three news stories and ensures that they weren't displayed in another spot on the home page. The query makes calls to the SQL Server machine at 10-minute intervals. The CACHEDWITHIN parameter lets you set the time interval to days, hours, minutes, and seconds. Until the designated expiration time, ColdFusion maintains the query results in memory.

Web Distributed Data Exchange (WDDX). Exchanging data among servers with different programming environments is a challenge, no matter how simple the data structure is. Even if you were to use Extensible Markup Language (XML), the people on both ends of the data exchange would need to learn XML and create and agree on a common Document Type Definition (DTD). But by using WDDX on both ends, each side can convert the data to a common format. WDDX uses an XML vocabulary for describing complex data structures such as arrays and record sets in a basic way so you can move the data among different application server platforms by using only HTTP. Target programming environments for WDDX include ColdFusion, ASP, JavaScript, and Perl.

Listing 4 shows how you can use WDDX to serialize into XML an array created in ColdFusion and send the array to an ASP server that will then deserialize it from XML into a VBScript array object with all the types natively converted. Using WDDX makes the conversion process between languages relatively transparent.

CFMAIL. You can use the CFMAIL tag to create email messages in which the recipients' names and addresses and the message are generated dynamically. In other words, you can query a database for recipients' email addresses or other information and create an email message on the fly. ColdFusion supports connectivity to email servers with SMTP through the CFMAIL tag. With the example in Listing 5, I can notify someone in another department by email when certain company accounts are going to expire. I set up a scheduled task, and the recipient gets daily notifications.

CFHTTP. You can use CFHTTP to download a file, document, or Web page from any site and store its contents in a ColdFusion variable so that a ColdFusion application can manipulate the data or store it as a file on the server. For example, we use CFHTTP to pull Web pages from the Monster Board Web site. We then parse through the pages for the data we want and display it on our site.

Custom Tags. Using custom tags is an efficient way to reuse code. Custom tags let you call a page built in CFML and add its functionality to any ColdFusion application. You can write the tags or obtain them from ColdFusion resources, including the Allaire Web site (http://www.allaire.com). Let's say you repeatedly add functionality to your site that displays only when certain values are passed to it. Rather than rewriting the code each time you need to use it, you could write a custom tag and send variables to it. We use the following tag on the Windows 2000 Magazine Web site to tell users whether they need subscriber access to view a particular article.

CF_AccessImage AccessLevel="#AccessLevel#">

The CF_ before AccessImage tells ColdFusion to look in the current directory, then the Custom Tag directory, for a file called AccessImage.cfm. If we need to change the default settings of the tag, we simply pass those attributes. By default, we show the small image. But in some circumstances, we might need to show the big image, which the code in Listing 6 does.

Another great use of custom tags is to deploy code written by someone who's willing to let you use it. For example, the DirectoryList_Enhanced tag in Listing 7, which I downloaded from Allaire's site, makes a list of all the files in a directory and its subdirectories with the file extensions you specify in the filter parameter. Because someone else had already written the 100-plus lines of code I needed, and I needed to use the code in only one place, using the tag made sense.

Debugging. One especially developer-friendly feature of ColdFusion is its debugging capability. If debugging is turned on, ColdFusion delivers an enormous amount of information with each page to any person with an approved IP address. Because ColdFusion administration is Web-based, from any computer with Internet access I can enter the IP address of the machine I'm on and instantly receive de- bugging information. Screen 1 shows a sample of information the debugger gives you. This information can help you tweak a page for better performance or identify the source of a problem. ColdFusion 4.5 includes the capability to determine the time it takes to process each component that makes up the delivered page. This feature helps you isolate the specific component that's causing a problem.

The debugging tool also shows how the query performed. You receive a count of records returned and how long it took the query to run. In Listing 8, you can see that the query Question returned 1 record and took 1 millisecond (ms) to run. The stored procedure instaNTQuestion returned 1 question and took 0ms to run. But the query getNews shows that it returned cached results because the time is listed as Cached Query. The last pieces of debugging information, URL Parameters and Form Fields, show what values are being passed from one page to another.

Data-source management. You can create, edit, delete, or verify data sources on the Web server, as Screen 2 shows.

Page scheduling. The ColdFusion Administrator's scheduling facility lets you schedule the execution of ColdFusion pages and generate static HTML pages. Page scheduling is useful for a page that needs to remain dynamic but whose content changes only every few hours. For example, the Windows 2000 Magazine home page is dynamic because it draws information from a database, but the information doesn't change every minute, and no parameters are passed that require the page to vary. I can schedule ColdFusion to run the dynamic page, save it as an HTML-only page that doesn't require a SQL Server request, and serve the page. Screen 3 shows the scheduled task.

Caching. ColdFusion offers page and database connection caching. ColdFusion caches pages into memory across multiple user requests, giving you a noticeable performance advantage over having a page built each time a user calls it. ColdFusion also caches and pools database connections for use across multiple client requests. Database connection caching improves a Web site's performance by eliminating ODBC connection times and by having multiple page requests share the same connection.

Automatic server recovery. You have the option to configure automatic server recovery. For example, you can tell ColdFusion to restart the ColdFusion service after the occurrence of a certain number of failed requests that execute component code such as an ODBC driver or a CFX tag. ColdFusion 4.5 includes the ability to restart the ColdFusion service when requests terminate abnormally. If you enable this option, the engine will track requests that encounter abnormal termination conditions.

Verity Search Engine. ColdFusion's Verity Search Engine lets you use a directory structure to index any textual data stored in relational databases and standard text and document files. To index static files, you first create a new collection. Then you assign a path and the extensions of the files you want to index. Next, click Update, and you're finished. Indexing ODBC data sources requires you to take a few more steps, but it's also a simple and flexible process.