I often hear ColdFusion developers and some of my colleagues who do training for Allaire say things like, "Don't just walk away from MS Access_run!" While I think I know much of the rationale for a statement like that, I still feel I have to come to its defense. The Bad News First!

Let me start by saying that I agree, to some extent, with the above derogatory comment about Access. Much of the criticism of ColdFusion comes in the form of "CF is too slow." When asked to explain, people will say, "It seems like most of the ColdFusion sites I visit are slow" or some similar remark about the response time of searches from ColdFusion-driven sites. This sentiment has been a thorn in Allaire's side since the very beginning. The reality is that ColdFusion isn't slow, but MS Access is. Chances are the sites in question are using MS Access as their back-end database. So the problem is really one created by Access, but often it's CF that gets the blame.

Let's face the fact that there are some real shortcomings to using MS Access as the database for our ColdFusion Web sites. The primary issues are speed, concurrency and the amount of data to be stored. According to its own documentation, Access 2000 has a physical file size limitation of 2 gigabytes and cannot support more than 255 concurrent users. In an enterprise-level Web application it doesn't take long to reach 2 gigabytes of data and I'd hate to see the response time if there really were 255 people requesting data from Access simultaneously.

As for speed, there's no question that Access is much slower than enterprise-level RDBMS applications like MS SQL Server or Oracle. (In a benchmark study I wrote a CF template that ran the same query against a table that returns 1,132 records in both Access and SQL Server 7.0. The average execution time — using CFQUERY.ExecutionTime — reported by the SQL Server 7.0 query was 81 milliseconds and the average execution time from the Access query was 381 milliseconds.)

One other limitation is the inability to leverage the power and speed of stored procedures and triggers. Stored procedures are simply precompiled SQL statements that execute much faster because they're already compiled on the server. Triggers are simply stored procedures that can be executed automatically by the server whenever data is changed. For example, a common need is to insert a record into the database and then immediately query the database to retrieve the primary key of the newly inserted record. Rather than write a separate query to retrieve this information, you can create a trigger (see Listing 1) that will automatically return that information any time a record is inserted.

So if you're planning to build a high-traffic Web site with a large amount of data that needs to respond very quickly, don't even think about using MS Access as your production database.

That said, however, there are some very legitimate places to use MS Access as the database for your ColdFusion applications.

Data Model Prototyping
Though the vast majority of CF applications I create for my customers ultimately use MS SQL Server 7.0 or Oracle as their production RDBMS, I do 100% of my initial development using MS Access. The reason is simple: I have yet to find a better tool than MS Access for doing database prototyping! In the early stages of development the data model for the application will need constant molding and refining. Access gives me a very easy environment in which to create, delete and restructure tables, redefine column types and create or modify primary key definitions. It may be nothing more than my personal opinion, but it just seems easier to open up Access to make changes to a table than it is to do the same thing through SQL Server's Enterprise Manager.

Upsizing to a real RDBMS
I have no qualms about developing my database in Access because it's so easy to upsize to a real RDBMS when the time comes. MS Access 2000 comes with a very easy-to-use Upsizing Wizard (see Figure 1) that walks you through a few simple steps to convert your database to SQL Server 7.0. Even without the wizard, it's not much harder to bring the table structures and the data into Oracle by writing a few SQL scripts.

Portability

Another reason I use Access in the early stages of application development is portability. I may start off by creating the database for an application on my laptop while in a hotel room when I'm on the road, training for Allaire. Then I'll need to share that database with other developers when I get back to the office. It sure is easier to copy a single .mdb file than it is to migrate the data from one server to another.

My clients may introduce another portability issue. A client may want us to create a test site on their server, but may not have a license for Oracle or SQL Server. All I have to do is upload the Access file to their server and create an ODBC Datasource in the CF Administrator that points to it — we're up and running in a matter of minutes and we didn't have to spend any money on additional software. Low- to Moderate-Traffic Web Sites

As I said earlier, there should be no doubt that Access just isn't suitable for prime-time, high-traffic Web sites. There are significant scalability issues that simply cannot be resolved using Access. But let's face the truth: the vast majority of companies that want to set up a data-driven Web site won't be receiving 100,000 or more visitors a day! Most companies and organizations would be ecstatic to receive 5,000 visitors a day. Not everybody who wants a data-driven Web site is a ToysRUs.com or a SmartMoney.com — they all want to be there someday, but they aren't there yet.

For these companies, organizations, groups and individuals that have fairly light traffic levels and response time isn't tremendously important, MS Access is probably going to be adequate to meet the demands of their limited audience. It's a great way to get started without the up-front expenses of buying an RDBMS like Oracle or SQL Server, not to mention the hardware to put those applications on.

Provided that the database is designed correctly and the ColdFusion code is written properly (granted, these are two fairly large assumptions), Access can actually perform quite well under moderate load. As a developer, there are a few steps you can take to ensure that your Access-based application will work better.

Improving the Performance of Your Applications
Here are a few tips that will greatly improve the performance and stability of your ColdFusion/Access applications.

BLOCKFACTOR
First, add the BLOCKFACTOR="100" attribute to all your CFQUERY tags (see Listing 2). This alone will dramatically increase the speed of your queries. Without this attribute, when you run a query, ODBC hands the retrieved records back to the CF Server one at a time. By adding this attribute, ODBC will keep the records and then hand them off to CF in blocks of 100 at a time, which is much faster. In a benchmark test I ran against an Access table, the average response time without the use of blockfactor was 420 milliseconds. Just by adding BLOCKFACTOR="100" to the CFQUERY tag, the average response time dropped to 97.19 milliseconds for the very same query! This is an attribute you can use on any Oracle or ODBC datasource, not just Access. You can even use it on queries that only retrieve a few records — the response time will still be faster than without it.

Cache Your Queries
You can also improve the performance of your applications and the load placed on your database by caching your queries wherever you can. If you have a page that retrieves information from a database and that information is the same for everyone who runs that page, that query is probably a good candidate for query caching. The theory is this: let the first request for that page load the query into the server's memory. Subsequent requests for the same data can get it much faster from the server's memory than the server can get it from the database. There are basically two ways to cache a query: store it in a variable or use the CACHEDWITHIN attribute of the CFQUERY tag.

If the data retrieved from the query never or rarely changes, it might make sense to cache that query into an application-level variable. Once the variable has been created, it will stay in the server's memory and be available to all the pages in that application until the application times out (see the ColdFusion Administrator for establishing default timeouts for application and session variables). In this example (see Listing 3) the query creates a variable in the Application scope (notice the NAME attribute of the query tag) and the query is nested inside a CFIF block to check if the variable already exists. If it doesn't exist, the query runs and creates the variable, so the CFIF block will not run the next time the page is loaded. The CFLOCK tag ensures that only one user actually creates this query, just in case there are simultaneous requests for this page.

If the data returned by the query changes frequently, but those changes don't have to be seen immediately, you should look into the CACHEDWITH attribute of the CFQUERY tag. Basically, you just need to determine how long the results of that query should be kept in memory before going back to the database to see if anything has changed. You'll want to use the CreateTimeSpan function to determine the length of time to cache each query (see Listing 4). The first request for that query will put the results in the server's memory and it will stay there until the time limit is reached — in this example, for 30 minutes.

Using either of the above caching methods will improve the performance of the application and will reduce the amount of hits to your server's hard drives. Caching methods should be considered for all of your ColdFusion applications, not just those that use Access as the database.

Limiting Simultaneous Connections and Maintaining Database Connections
If you're using Access as your database on your Web site, you should definitely consider these two settings in the ColdFusion Administrator.

I usually say to my students: "Access is very bad at managing simultaneous connections but ColdFusion is great at simultaneous connections." The idea here is to let CF manage the connections, not Access. When you create an ODBC datasource for an Access database, the ColdFusion Administrator gives you the option of enabling the limit of x simultaneous connections to the database (see Figure 2). I strongly recommend you set that limit to 1. ColdFusion will then allow only one request at a time to be sent to Access, and it will queue subsequent requests.

The end result is that everyone will get their data faster by letting CF do the managing of requests, not Access. This will also keep your application from crashing during bursts of heavy requests, which is very likely if you let Access handle the concurrency issue. Note that this setting should only be used for Access or other file-based datasources such as dBase, Paradox and Excel, not for Oracle or SQL Server.

The other setting that you should definitely enable is the "Maintain Database Connection" option (see Figure 2).

Without this setting, CF will establish a connection to the Access database, lock the .mdb file for exclusive use, pass in the request and then release its connection. It will go through this process with every request for that datasource, which significantly slows the process. By telling CF to maintain the connection, it only has to go through the process for the first request. Subsequent requests are much faster because CF has maintained the connection to the file. Note that this can be rather annoying on a development server, because CF will lock the file, and not allow you to modify the database until it releases the connection.

Summary
Broad statements like "don't walk away from Access_run" shouldn't be made without giving some context. While Access has many shortcomings and should not be considered as a back-end database for enterprise-level Web sites, it does have a very valuable contribution to make to the world of data-driven Web sites and to the development of those sites. So, if you're thinking about abandoning Access as your database just because you heard some rather respected people in the industry badmouth it, think again. It's a great development tool and can be a more-than-adequate solution to your data-driven Web site, given the right environment.

• Source Code