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?

Nellnella

Answer 1

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!

Gravity

How far off would Foxpro be, used without SQL? – Nellnella

I'm not sure I understand...do you have an ORM interface to Foxpro data available? – Gravity

dunno, but it's got all the tools report writer etc. – Nellnella

Answer 2

Maybe Pig Latin is what you want? According to this article http://citeseerx.ist.psu.edu/viewdoc/download;jsessionid=693D79B5EFDC0452E1C9A87D1C495D4C?doi=10.1.1.124.5496&rep=rep1&type=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."

Rutabaga

Answer 3

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.

Lula

But those features have been incorporated into isam packages in the past. dBASE had catalogues, and I think FoxPro has transactions. A server daemon would be pretty trivial (and probably a good idea). But I'm mainly thinking about the logical interface between clients and data. – Nellnella

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.

Sexagenarian

I agree, but we're stuck with the fact that a big deal is being made, and whether or not the problem exists, people keep coming up with new ways to make things more complex - beyond SQL. Whatever might be said about LINQ/ORMs, they're not simplifying tools - just more overhead. – Nellnella

more overhead in processing time or in learning the technology? or maybe in finding ways to make the technology to fit your particular problem? I am not very excited with LINQ, nor with Hibernate - their limits show early. – Bartle

mainly learning - I agree that LINQ/Hibernate/ORM seems to add complexity without much simplification going on elsewhere to make it worthwhile. – Nellnella

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.

Alesiaalessandra

Arrays? You want arrays? No problem! – Nellnella

Answer 6

If you know exactly what you want to do with your data and how you want to do that, pick ISAM. You will be happy because you will have structured your indexes to serve your exact needs. Know upfront that if your needs change, you will want to change your indexing. Data access will be blazing fast.

If you are not sure what uses the data will be put to, or you know your data needs will change a lot over time, pick SQL. You will have the flexibility of ad hoc queries, quick reporting turnaround, data mining, etc.

Both types of databases have matured over the years. Both can have robust servers with live backup, transactions, security, metadata, etc.

Aksum

Answer 7

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.

Mucosa