I did a quick define on the terms you highlighted.
transactional/Btrieve (iRowset) - fast low level interface
iCommand - relational interface for ADO and OleDB/ODBC
fast transactional access like Btrieve, ActiveX, and OleDB rowset - low level access to the data which is file based.
Relational access is the dominant data access method these days, but it is certainly not the only one, nor would I say that it is the best way to go get data for all tasks. It's kind of like having to choose between a screwdriver and a hammer to do household tasks - neither is ideal for every task and each has it's strengths.
Relational was originally concieved to save disk space and reduce redundancy which is not so necessary now in a time of 40GB drives for $100. In Relational and SQL the programmer/user writes an english like query and the database engine translates that into something that can be executed by the actual component that does I/O on the disk. Computers don't understand english so by necessity that looks completely different from english. This means that there is a lot of translation going on that that necessitates overhead. Thus SQL is relatively slow for many things because it parses the query and creates a query plan and optimizes it. SQL/Relational is quite flexible and standard (though not as much as people seem to think) and it great for reporting and ad hoc queries. It also often doesn't take as much technical knowledge of data to begin to use since it is a consistent logical model and the user doesn't have to know about the physical properties of the data and database because they are abstracted.
Other data models such as network, hierarchical, and ISAM/VSAM, may not be as flexible, but they can be lots faster and more powerful becuase they don't have to adhere to the "10 rules" of the relational database model. They often can take more time to code unless wrappers and components are built and used. Basically these models are usually file based so you do things like open a file and get a record, then get another record, etc... These access methods have been used for years on mainframes and minis, in languages like COBOL, and in products like Btrieve (which supports BASIC, C, Fortran, Pascal, COBOL, and lots of 4GL tools) and much of that code has been migrated to PCs. This sort of access is great for "line of business apps" where you have to have instant response and data formats and layouts may not align well with a relational model. I have seen Pervasive.SQL do 4000+ inserts, updates, and deletes a second on quite modest dual CPU PC servers with a good disk array. Pervasive.SQL provides both a full relational engine, the SRDE, and the Btrieve engine which is transactional (also called navigational or ISAM).
The advantage of transactional is that it can do enourmous amounts of work on relatively modest computers with little CPU overhead and it usually maxes out the disk I/O or netowrk before the CPU starts working hard. Relational on the other hand usually benefits from large multi CPU systems and often requires an administrator to keep the data and engine in 'tune' - a DBA (who usually makes a hefty salary). In addition Pervasive.SQL also has a lot of different interfaces for programmers to use in their SDK: ActiveX control accesses BTrieve, PDAC is for Borland Delphi or C++Builder and can use either relational or transactional, while JCL is transactional for Java and JDBC is relational for Java, ADO can do both transactional (iRowset) and relational (ICommand), ODBC is relational only and the native SQL interface in Pervasive.SQL.
The whole point of this is that it's a good idea to use the best tool for the task at hand provided that there is adequate knowledge of how to properly use the tool. You could install a screw with a hammer, but it would be better to learn to use a screwdriver and have a more satisfactory result. Many developers are using SQL for tasks it is not well suited to do and so resort to making it work by throwing hardware at it so it will perform adequately and spending hours, days, and weeks optimizing queries and writing stored procedures.
I would recommend getting a copy of the Pervasive.SQL 2000 SDK and downloading and reading some white papers from the Pervasive site.
Pervasivite
