George,

>>I get the feeling we are talking about apples and oranges.

>We certainly are...

Getting more confused by the hour....

>>Even if you issue GO BOTTOM VFP does not a lot more than reading the last record. It does not read the whole table. When in a browse it might also read some near records, but it does not read the whole table.

>>As loading into memory, I think this is a lot OS related. for a large part it is not VFP but the OS that buffers portions of the table. How do VFP views and queries compare to SQL resultsets ? I think they're very simular. Remember the filtered SQL results, issue a go bottom, and it goes to the bottom of the table while the resultset does not contain all records ? Is this ISAM or not ? I don't think that has anything to do with this.

>No it is not OS related. VFP employs its own iternal memory manager. Naturally, with a large table it may not be physcally possible to load the entire table into memory.

Lot's of the table is cached by the OS, not VFP. This is the very same reason we should restart the computer to compare two SQL statements for optimizing. The number of chached records does seem to depend on a number of things. First of all it makes a lot of difference if you're opening the table exclusively. In this case all records that have been retrieved are cached locally. When opened shared, it can be opened in exclusive oplock mode when you're the only user in it. When openen in oplockmode 1 or 2 (readonly or none) (other users have also openened this file) only small portions of the table are cached. Lot's of this is controlled by the OS, not VFP.

>Both views an queries require that the underlying table, which is a single file, be open. They're similar in that they return a result set, but the underlying mechanisim is very different.

The storage and management is very different, yes. but that was not a point of discussion.

>With a parameterized result set from SQL server, issuing GOTO BOTTOM only affects the result set, nothing else. Issuing a GOTO BOTTOM against a VFP table, which does not have have an index set requires VFP to either physically locate it (worst case), or use the existing indexes to find the last record in the table.

Go bottom, When no index order in effect, calculates the position of the last record in the file and reads it. It does nothing with all the records between the record pointer and the last record. Of course, when the last record falls outside of the scope of an active filter, it skips back one records until it finds one. Therefore GO BOTTOM is not rushmore optimizable.

When an index order is in effect, It just takes the leftmost indexnode of the b-tree and goes to the record it points to. If a filter is in effect and this record falls out of scope, it skips back trough the indextree until the first record that falls into scope.

In general, when no filter and index order is in effect GO BOTTOM is a very fast effecient straightforward command.

>>>One other difference is that with an ISAM table. Since ISAM is only capable of a positioned update, it takes at least two round trips to the backend. One to position the row, the other to actually commit the changes. An a SQL set based operation, it requires only one trip to the server, telling it to update the records that meet a specified criteria.
>>
>>That is just a matter where the border between the client and server is. I could do the same with a out-of-process VFP server running on the server directly. In a way the SQL server does just the same, though the database is just on the same phisical computer: It also must first read the contents of the record (for various reasons) before it can update it. Only there is no network in between. I see no big distinction between the two here.

>You're making a completely invalid assumption here. You're assuming that an SQL Server database is a single physical file. It is not. It is one or more physical file(s).

How did you read into that? How was I implying this ? You're argument was that VFP has to do more trips towards the server compared to a server DBMS. This is the case if VFP is accessing a database on another PC than the application. If the VFP database is on the database PC, you'll see that the process is very simular to a server DBMS.

Imagine what trips around the server would occur if in SQL server, the physical database was on another server than the SQL server application itself. Things would even be worse, because not only records have to be searched, locked, replaced etc, but also the transactionlog mechanism would have to be written to that server.

>Tables are made up of 8KB data pages. This contain the not only the phyical rows, but other information regarding the data page.

Indexes, administrational data about the contents of the page, etc. I know. However, this is DBMS depended.

>>>Sure, that's the way SQL Server is built. Just because you add a record to a SQL Server table doesn't mean that it is physically the last record in table. SQL Server, as I recall, is page based with the pages accessed through a binary tree index.

>>You mean clustered indexes ?

I see, you mean the space you can reserve in each datapage for inserting records. I don't know for sure, but this seems foremost to be a requirement for clustered indexes where the index order directly represents the physical table order.

>>Nontheless, I still don't understand what you mean by "opening a table without reading all its records" ?

>You're think in file based terms. There's a lot more to this under the hood.

I certainly don't think in filebase terms only. I only can't figure out what you did mean by "IOW, you cannot open an ISAM table without retrieving all the records"

I'm fully convinced you ment something else from what I'm reading here. If I open a given file, I could read any particular record (or even byte) of the file without reading any other records.

>>That is not the point: a DBMS can be based on ISAM, but still have the same external scheme as SQL 7. The external schema is just a layer on top of the internal one, hiding its internal schema (implementation).

>But SQL Server isn't based on ISAM. It's completely different. I've given you specific points based on the fact that I've RTFM. You can't argue with these points if you haven't. If you want to find out, then buy the book and read it.

I'm not talking about SQL server 7 specificly. I'm talking about the difference between server based DBMS (like SQL 6, 6.5, 7, 2000, SYBASE, ORACLE, INFORMIX etc) and ISAM. IMO they don't relate to eachother. Again a server DBMS can be based on ISAM. Wasn't Watcom SQL based on ISAM ? The internal schema could deal with ISAM while the external scheme could hide this for the frontend user.

You can only compare ISAM and the database storage used in a specific DBMS implementation, like SQL. They are a lot different, of course. However, IMO, since SQL hides the internal data storage mechanism for the outside through its internal schema, it does not matter how the data is stored. When connecting to a server DBMS you don't have to know how the underlying database is stored and managed. When dealing with ISAM tables this is much more a requirement.

Walter,