Walter,

Rather the giving you two replies, I’ve compressed things into one.

You’ve said that you don’t believe that VFP through its tmp files does any caching. I may be mistaken, but I seem to recall a tip in FoxPro Advisor a few years ago regarding restoring data that had been lost due to a system crash when the table was open. It would seem to me that, if this is the case, that VFP does indeed engage in some internal buffer of some sort.

You said, “I think you make a complete invalid assumption here. Though I immediately agree that you're right about the network trips, I'm talking about the I/O needed to complete the transaction on low level I/O. When comparing the two you should compare what is happening internally on I/O level. I could easily build a out of process VFP server, stored on the same machine as the database, that accepts the same commands as you send to a SQL server. You'll see that the trips across the network are just the same. In fact you'll see that this VFP application is just like a mini SQL-server, a server DBMS based on ISAM tables.”

The fact is, Walter, you couldn’t do what you’re implying here. There are a couple of problems in your out of process server scenario. First is that even though the application resides on the server, it still has to be loaded into client memory and do its I/O over the network wire. Even if this weren’t the case, it still has to position the pointer and make updates. Finally, what good would it be? After all, it’s apartment model threaded, which means it could handle only one transaction at a time.

By contrast, and again using SQL Server, any requests, whether for data or updates, are handled client side. Further, it manages a pool of threads, and can and does handle multiple transactions at once.

You said, “When talking about low level I/O you'll see that the server DBMS does a lot more than just searching for a file position, update the record etc. In a server DBMS other facilities like authorization, advanced locking schemes, isolation levels, transaction logs and replication, come into action when updating a single record.

This all has totally nothing to do with ISAM itself. Its just that ISAM tables are often accessed from another networkstation than where it is stored physically. In server DBMSs this is all encapsulated onto one dedicated PC. To do a fair comparison, you should compare ISAM based DBMSs with server DBMSs where the database AND engine are BOTH locally installed. Then you should compare the effectivity of ISAM related storage with DBMS related storage. You'd come to the comclusion a server DBMS does a lot more I/O because it usually has a lot more (and complex) facilities it should take care of.”

Of course, but it’s all handled on the server side, not the client. However, I was talking about the scenario where the data is not stored locally.

>Yes but you have to position the read pointer in either case.

”Just like a DBMS does internally (among other things) to be able to read the contents for the record which is needed to fire triggers (and begin able to query the current value), for replication and the transaction log.”

At least with SQL Server may not have to position any record pointers, since it probably has internally cached the 8K pages involved and writes them back changing only the data on the affected pages.

“And though of course you can describe what you want to get with SQL server, internally in both stategies very much the same is happening (on low I/O level). You only took the freedom to look at SQL-server at a much higher abstraction level and compared this with the low level I/O that xBase languages employ. You're forgetting that SQL-server does more or less the same as xBase internally. The difference however is that in the case of SQL server all is one one machine, while in the xBase example the engine is on the client and the database is on the server. This is not what a regard a fair comparison, and has nothing to do with ISAM itself.”

You still don’t understand the difference here. An RDMS such as SQL Server is set-based. You may or may not cause the entire table to be read into memory (both server side and client side). An ISAM DBMS is file based. When you open a table, you have no control over how much of it is read into local memory for manipulation. When you retrieve a result set from such a backend server, you only get what you ask for. This isn’t an abstract difference. This is concrete.

When you send updates back across the wire, with ISAM they happen one-by-one on the client side. With an RDMS, you send back the entire set for processing on the server side.

As far as the low-level I/O, I think I know a bit about this, and speaking to a SQL Server vs. VFP, they are very different.