George,

Things seem to get a bit clearer.

>I'm talking strictly about SQL Server. However, other true RDMSs may or may not use the same techniques, such as Sybase from which SQL Server was derived. I'm not familiar enough, however, with those particular products to comment on them.

I think the discussion of the difference between ISAM and not ISAM cannot be based on the specific implementation of SQL-server and/or any specific version alone. Therefore I try to see this in a bit higher level.

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

>Walter what do you think tmp files are? They're VFP internal caching mechanism files, not the OSes.

AFAIK, The tmp files are used for storing cursors and other intermediate data, not chaching (physical) tables. You can try the following that supports my story:

1. Take a large or medium large table (>20 MB or so) on server, preferably accessed on a what slower network (10 mb).
2. Open the table exclusively with USE.
3. define a SQL SELECT statement that takes at lease 1 or 2 seconds to have a good compare.
4. Note the time needed to complete the query.
5. Shutdown VFP and reopen it again
6 repeat steps 2-4 for this same table.

If all is well, You'll see that the second time the query runs significantly faster than the first time. Since You closed VFP, the tables must be cached at the OS level. Also, the Exclusive oplocking mechanism is one that operates on the OS level, VFP has no control over it.

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

>You still have to have a file handle open to the entire file.

As each RDBMS should have file handle(s) (unless it operates on really low level directly accessing the media, but again, this depends on the DBMS) to the database file(s). You're absolutely right if you say that a DBMS that has integrated its tables in one database file (such a the access database) need less file handles. But what disadvantage are you implying then ?

>>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.
>
>Is a table a file in SQL Server, Walter? Or is it a series of files? Or is it an object?

In a DBMS server, a database file contains all kind of database objects, like tables, indexes, procedural code etc.

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

>You really need to read the book and have a greater understanding of how set based transactions work.

Well, I don't have any need to know all SQL server specific implementations right now. However I'll keep this in mind. However what I wrote above is universal for all DBMSs: They all have intensive interaction between the physical database and the server application. In fact the communication tends to be far greater than xBase languages because of more advanced locking schemes and writing transactional data for both replication and the transactionlog.

In fact, if you'd be able to seperate the server application and the physical database and place them on two different PCs connected trough a network, you'll see a lot more trips to the server than in xBase languages. Remember that the physical database is not able to do any processing. It's just a flatfile (or files) like a xBase Table, though the DBMS database contains a lot more than just tables.


>In SQL servers case, there's no adminsitrational information, there is information about the page and its location within the table, but technically no index either. Now if you mean transactional information, that's different.

O.K.

>>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.
>
>Are you thinking of the table as a single file?

No, I was referring to a mechanism used in DBMSs to reserve space in a table page for inserting records in the future. This is a requirement because with clustered indexes the indexnodes refer to the actual datapage of the table. IOW the physical order of the table represents the order of the index. If not using this index, it is more efficient to add the inserted record at the bottom of the table.

>Assume that the data is on a network server, or locally it does not matter. You have to first position the pointer (one trip to the server or OS) before you you can make an update to the table. If you are adding a record, you have to be able to insert it at the end of the table. When you update, you have to send the information back (one trip to the server or OS). With any set based RDMS, you retrieve just the information that meets the criteria, and when updated send it back. Seems like two trips, but on a table buffered ISAM, it's two trips for each row. With a set based RDMS, the whole thing is done in two trip total regardless of the number of rows.

I think you make a complete invalid assumption here. Though I inmediately agree that you're right about the network trips, I'm talking about the I/O needed to complete the transacion on low level I/O. When comparing the two you should compare what is happening internally on I/O level. I could easely 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 accross 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 difference between VFP and a server DBMS is that in VFP the physical database can be on another network machine as the database engine. On a DBMS they usually are both installed on the same PC. See description above.

When talking about low level I/O you'll see that the server DBMS does a lot more than just searching for a fileposition, update the record etc. In a server DBMS other facilities like authorisation, 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 decicated 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 ussually has a lot more (and complex) facilities it should take care of.



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

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

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

>Go back and read my orginal post. Even though I assume you understand what ISAM means, for anyone who does not it refers to a single file based table. The acronym stands for "Indexed Sequential Access Method". This means that, technically, the records are accessed one by one, in sequential order. Your typical RDMS is a set based. They are completely different and require a different design paradigm to be employed.

Your original post to which I replied:
. IOW, you cannot open an ISAM table without retrieving all the records. You can with SQL Server because it requires you to describe what records you want. A VFP query requires that the underlying table (all of it) be opened and, therefore, cannot be considered to be the same.

This phrase still is a mystery to me because I don't understand about open a table (all of it). Esspecially the ALL OF IT. I don't understand.

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

Walter,





>>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.
>>
>Why? My original point, however, was that these are two very different things.