Hi george,

>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.

I know, that for cursors TMP files might be created. Maybe for data that is involved in transactions they're also created. However the example I gave is clear, tables can be cached on the OS level. Of course this might be influenced by some registry settings on either on the server and the workstation.

>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.

No, I'm talking about an out of process server that is started at the server (DCOM), Only the object is openen on the client. All commands in a specific method called from the workstation are processed by the server, not the workstation. So if I call a method it and pass a SQL command, it gets processed on the server, rather than on the workstation. Only the command (and some administarional data) crosses the network.

Positioning the record pointer and make the updates are lowlevel commands. They also occur (amongst other things) in SQL server internally. There is not a lot of difference in these respects. In fact the record pointer is not much more than setting a filepointer to the current record position. Since that current record has been read before, setting it back at the beginning of the record is not an action that causes any network or disk I/O. As you know the filepointer is calculated from the size of the tableheader, the record width and the value of the record pointer.

>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.

So why do we have multithreaded Dlls within VFP ? Using COM+ I could build the same in VFP with respect of handling multiple transactions at once.

>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.

Aha.... now we've got a point. Yes, ISAM tables are often used from a file server while the database engine itself operates on the workstation. However, this has nothing to do with ISAM itself, but rather what data strategies you could employ with it.

>”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.

Ehhhh, I don't get it. How much different is a VFP record pointer as a filepointer used for positioning before an update ? Of course SQL server also has to lookup the modified records (with a filepointer) and update it. In what respect is VFP different here ?

>“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.

I'll agree you don't have ultimate control about how much is downloaded into memory, but you definitely have SOME control. When openening a VFP table, only the header and the first few records are downloaded into memory. If you do a query the number of downloaded records depend on the the command you gave (SKIP would require at most one record, if no filer is in effect), on rushmore (If a query is fully optimized) only the required records are downloaded into memory etc. In SQL server internally a comparable thing is happening (but then the records are requested from disk directly, in stead of from a fileserver).

All is a matter of abstraction. As in my example of the VFP (DCOM) server, I could do the same. From an application I could send a command to the DCOM application, which processes my command and send me back my result. In what respect does ISAM or being record or set based have anything to do with this ? Nothing.

How about webservices: If i've got a client connecting through webservices to a VFP database or a SQL-server database. How would this influence communication ? Not ...., both can accomplish the same task. Whether the backend is based on ISAM or not, or being set or record based, does not make any difference.

>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.

Again,in this example this is because the database engine is seperated by the network from the actual database. If you'd be able to split the database from the database engine in for example SQL - server, you would see even more trips across the server because more I/O has to be done.

Bottom line. You're comparing a situation where the database is on the same machine as the database engine (SQL server) with a situation where the database resides on a fileserver and the database engine on a workstation. You cannot compare this situations. You try to link ISAM to this situations, but fact of the matter is that ISAM can apply to both situations. IOW server DBMSs (as shown in my VPF DCOM server example), can be both ISAM or NOT.

>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.

Sure they are different, in a respect that SQL server has a far more complex I/O scheme. However I you look at the process of finding a specific record and update it, not much is (and can't be) different, as all I/O found in VFP tables is also found in server DBMSs (in some form).

Walter,