Data Management

Make the migration from ISAM to relational databases smoother

Fundamental considerations for migrating to a relational database

Making the migration from an ISAM database to a relational database is an inevitable transition for most organizations; however, the path isn’t an easy one. Because of this, many organizations are forced into trying to make their existing applications work with as little change as possible. This leads to a predictable set of challenges.

Typically, a relational database will perform better than an ISAM database, but this isn’t always the case. When programs are not changed, when they continue to approach the relational database in the same way, performance will almost never be the same as it was with the ISAM system. This is because relational databases are not optimized for the kind of single record, single table operations that programs written for ISAM are designed to use.

Doing a direct conversion from the ISAM database backend to a relational database backend will lose some performance. However, this performance impact can be managed by increasing the amount of memory on the database server, the careful use of indexes, and performance tuning. One of the decisions to be made during the conversion process is how much time the organization is willing to spend on evaluating achievable performance.

In many applications, throwing some hardware at the problem is enough to get performance close to what it was with ISAM. Since ISAM databases cannot truly leverage RAM in a system, providing a database server with more RAM may be enough of a performance nudge to overcome the natural decrease created by trying a direct conversion.

Program changes
In a way, describing the changes to the program that may need to occur because of a database migration simply as "program changes" does not really do the concept of changes proper justice. It’s more than just changing the program itself; it is also changing how you think. Changing to a relational database means changing many of the basic processes of designing software.

Set-based thinking
Changing thinking from processing each record in turn, as is done with ISAM, to asking for a set of records that needs to be processed is a shift that on the surface appears subtle, but is immensely more complicated because it is a shift that must be tested at every turn. The first step of filtering the list of returned records to a set of criteria provided is simple. This first level of utilizing set criteria is nearly a prerequisite for obtaining performance from a relational database. In SQL terms, this is nothing more than adding a WHERE clause that limits the results.

The second level of utilizing set-based thinking is a bit harder. Thinking through how you can aggregate the values of several records into a single record and how that can be used to update another record, is much more complex than just filtering a list.

A programmer working with a traditional ISAM database might approach the need to update the total lifetime purchases for a customer by reading each customer in turn, then reading all of their transactions, and then finally storing back the result if it has changed. A relational database programmer will recognize this as something that can be requested of the database—without any procedural code. A SQL statement that solves this need, which will perform substantially faster than the comparable ISAM code, might look something like Listing A.

Those few lines of SQL code, written for Microsoft SQL Server, cause all of the customers with transactions to be updated, and only those customers with transactions to be updated. The same procedural code would be substantially longer in code length and would take substantially longer to execute. This level of thinking about the sets of data involved in a problem requires more than just a few moments of deciding to write code for a relational database.

Making the paradigm shift from a procedural solution to a set-based solution won’t happen immediately. It will be necessary for programmers to challenge their normal solutions at every moment.

Changing logic
Perhaps the more insidious implication of the change to a relational database is that you’re reforming the logic of the application around the set-based thinking. In order to take full advantage of the benefits that relational databases offer, you’ll need to rethink and rework the business logic of the applications you’re working with. If the business logic is simple, then the conversion may not be all that complicated.

More often, however, the business logic is a maze of previous initiatives of the organization and blind alleys of code that haven’t been tested in years. Unfortunately, there is not a good way to ensure that the new code performs as expected in all conditions, since all of the conditions that the software was designed to support may not be present in the production data.

Although there are substantial performance gains that can be had if you are willing to restructure the logic of the application, the reality is that this is often a far too daunting task for most organizations to take on.

Understanding changing infrastructure
The final component of making the migration from an ISAM-based database to a relational database is changing views on the infrastructure. In an ISAM database, there is often very little thinking about reliability because the database mechanism itself is inherently unreliable. However, when working with a relational database, it’s important to consider high availability solutions including fault tolerant hardware and techniques such as clustering. These technologies and solutions can make the migration worth it by simply increasing the reliability of the database.

With a change as costly as an ISAM to relational database migration, getting an immediate reliability boost goes a long way to smoothing over the short-term costs that will eventually create long-term savings.

Editor's Picks