Choosing ISAM rather than SQL

Question

Many developers seem to be either intimidated or a bit overwhelmed when an application design requires both procedural code and a substantial database. In most cases, "database" means an RDBMS with an SQL interface.

Yet it seems to me that many of the techniques for addressing the "impedance mismatch" between the two paradigms would be much better suited to an ISAM (indexed-sequential access method) toolset, where you can (must) specify tables, indexes, row-naviagation, etc. overtly - exactly the behavior prescribed by the ActiveRecord model, for instance.

In early PC days, dBASE and its progeny were the dominant dbms platforms, and it was an enhanced ISAM. Foxpro continues this lineage quite successfully through to today. MySQL and Informix are two RDBMSs that were at least initially built on top of ISAM implementations, so this approach should be at least equally performant. I get the feeling that many developers who are unhappy with SQL are at least unconsciously yearning for the ISAM approach to be revived, and the database could be more easily viewed as a set of massively efficient linkable hyper-arrays. It seems to me that it could be a really good idea.

Have you ever tried, say, an ORM-to-ISAM implementation? How successfully? If not, do you think it might be worth a try? Are there any toolsets for this model explicitly?

Answer 1

There are certainly times and places where ISAM provides the services needed by the application with less cost and overhead than a full-blown SQL DBMS. One downside of an ISAM mechanism is that there isn't necessarily a system catalogue to describe the data; another is that generally there are few user-friendly tools to get at the data. These are both places where the RDBMS provides considerable advantage. The best ISAM (or similar) systems provide transaction support - even XA transactions, sometimes.

Where you need to do complex joins and computations (aggregates, for example), the work done by the DBMS provides huge benefits. Where all you need is access to records, then ISAM could be beneficial.

Security tends to be harder to enforce with an ISAM-based system than with a DBMS. Also, you need to worry about integrity of the files in case of a crash. Most DBMS use a two-process architecture (DBMS client in a separate process from the DBMS server), which provides resilience in the face of the client crashing (or the client PC being turned off). You also have to worry about backup and restore - a competent DBMS has systems in place for providing a coherent backup of a database while the database is in use; it is not clear that ISAM systems would provide that level of integrity.

Overall, given a suitable ISAM mechanism, there would at least sometimes, maybe often, advantages to using an ISAM mechanism in an ORM system instead of a full RDBMS.

Answer 2

I implemented an ORM-to-isam library back in the 1990s that enjoyed some (very) modest success as shareware. I largely agree with what you say about the virtues of ISAMs and I think it better to use an ISAM when building an ORM layer or product if you are looking only for flexibility and speed.

However, the risk that you take is that you'll lose out on the benefits of the wide range of SQL-related products now on the market. In particular, reporting tools have evolved to be ever more tightly integrated with the most popular SQL packages. While ISAM product vendors in the 1990s provided ODBC drivers to integrate with products like Crystal Reports, it seemed, even then, that the market was trending away from ISAM and that I would be risking obsolescence if I continued using that technology. Thus, I switched to SQL.

One caveat: it has been nearly a decade since I was playing in the ISAM sandbox so I cannot purport to be up on the latest ISAM tools and their solutions to this problem. However, unless I was convinced that I was not going to be trapped without reporting tools support, I would not adopt an ISAM-based ORM regardless of its virtues. And that doesn't even cover the other tools available for SQL-based development!

Answer 3

Maybe Pig Latin is what you want? According to this article http://www.cs.cmu.edu/~olston/publications/sigmod08.pdf :

"Besides, many of the people who ana- lyze this data are entrenched procedural programmers, who find the declarative, SQL style to be unnatural. The success of the more procedural map-reduce programming model, and its associated scalable implementations on commodity hard- ware, is evidence of the above. However, the map-reduce paradigm is too low-level and rigid, and leads to a great deal of custom user code that is hard to maintain, and reuse. We describe a new language called Pig Latin that we have designed to fit in a sweet spot between the declarative style of SQL, and the low-level, procedural style of map-reduce."

Answer 4

I did my share of dBase, Clipper and FoxPro. However I believe the relational model provided by SQL is infinitely more powerful and useful, and products like Oracle and SQL Server deserve their success in the marketplace.

I'm always surprised why people make such a big deal of creating a mapping layer for the ~80-90% of the cases and writing 10-20% of custom SQL to deal with complex queries (mostly reports) and batch data movement. I must be doing something really good or something really silly by adopting the DAL/DAO model, given the level of hatred against hibernate, active record, etc. - vide Vietnam discussion from earlier.

Answer 5

Multivalue database anyone? (aka Pick) Think XML without the tags. They predate RDBMS by at least a decade, and still going strong if you know where to look.

Answer 6

Old question, but interesting discussion. The concepts of ISAM are important, the additional features that we're provided in today's RDBMSs (as discussed i.e. backup, consistency, security, metadata) offer us signficant benefits.

With the NoSQL craze (yes I said it...craze) it doesn't mean that we can't model ISAM-like access inside the RDBMS. You'll be sure I'm gonna push off as much logic to the DB as I possibly can, but there are times like "traditional" data gridding/multi-dimensional data interpolation where I'll traverse all necessary records via my own logical index.

Answer 7

I have used many data storage alternatives including Oracle, MySQL and ISAM for business ERP applications. Relational databases generally offer a significant advantage in delivering greater application portability because SQL is available for many different RDBMS vendor products. However, I have been using RMS (Record Management Services) which among other things is an ISAM data management system. RMS is a native component included within all OpenVMS operating system installations. Although there are ODBC client/Server components available from third party vendors which permit using SQL calls with RMS, I prefer to use its non-SQL access vocabulary. I have used RMS for over 15 years and have never experienced a single problem, which is certainly not the case with any of the Relational products. Furthermore, performance of RMS ISAM is staggeringly better than any of the RDBMS products I have used. Apart from the performance differential advantage provided with most ISAM databases, it is liberating to experience the precision and control afforded by the "manual transmission" of a quality ISAM database like RMS where you only retrieve what you ask for and you never have to put up with record (row) locking problems because you have the opportunity to avoid these problems from within your application design and code.

If the day arrives when the automatic transmission (RDBMS) can match the Manual transmission (ISAM), then my opinion will change. I have to admit that day has arrived with real cars because Audi has a twin clutch automatic (Known as DSG) which is better than any manual transmission.