Has anyone figured out how to make ADO use a Client side Keyset, instead of
a Static cursor with a MSSQL7 server? It appears that only a ctStatic
cursor type is available (it reverts to ctStatic when you open the
recordset).
Here's how I see the differences;
A Static cursor copies the ENTIRE result set to the client side. So a
select * from BigTable may result in a lot of traffic over the network,
while the entire result set is downloaded and cached into the workstation.
I could be wrong, but I have inferred that a keyset, is essentially a result
set consisting of only the primary keys for a given select. Access 97 seems
to implement this (via DAO, but it doesnt matter how) internally. When you
open a linked MSSQL7 table and display a grid of rows, the response is very
good. Behind the scenes (perhaps in different threads and using some kind
of async request), Access 97 first selects only the set of PK's for the
table, in the order that you request (i.e. A to Z, or Z to A, etc). Then
Access uses a second trip to request in groups of 10 the actual rows of
data, using the cached PK's via a prepared query. All of this can be seen
in a SQL trace. It always requests more groups of 10 then it needs so it
can quickly display all of the rows in a grid for the current and the next
page, should the user scroll down. Access also caches the rows (like a
ctStatic cursor) it does receive so scrolling up is always immediate.
Maybe I'm alone here, but I think this is good behavior. A Big Table can be
easily browsed, ordered, and perhaps filtered by the user from a convenient
grid. The only traffic impact the size of the table has is on the size of
the cached keyset, not the size of the ENTIRE table as would be in the case
of a static cursor. The second round trip to retrieve the actual rows (10
at a time) is an extra penalty, but minor from the view of the user.
It seems that an ADODataset or some descendent could be written to
encapsulate this behavior behind the scenes, making it behave much like the
old TTable of BDE, without the headaches in ADO we live with today. How
hard can this be?
begin
BufferedAdoDataset.connection := 'AdoConnection1'; // same o same o
BufferedAdoDataset.PrimaryKey := 'PK_Field_of_BigTable'; // the known
field name of the PK
BufferedAdoDataset.commandtext := 'Select * from BigTable order by name
DESC';
BufferedAdoDataset.Active := true;
// Behind the scenes, now the following 3 things actually happen;
// 1) APKRecordSet = results of "Select PK_Field_Of_BigTable from BigTable
order by name DESC"
// 2) Prepare 1 Select * from BigTable where (PK_Field_Of_BigTable = ?P1)
or (PK_Field_Of_BigTable = ?P2) or ...
// 3) sp_execute 1 1,2,3,4,5,6,7,8,9,10 // use the prepared query with up
to 10 PK Values.
// the 10 rows above used to populate the equivalent of a "Static" cursor.
// Put another way, those 10 complete rows are now buffered and do not
need to be fetched again
// when the BufferedAdoDataset is scrolled paste the first 10 rows,
// the prepared query is used to fetch the next 10 rows.
BufferedADODataset.First; // occurs immediately, first 10 rows already in
cache
for i := 1 to 9 do begin
BufferedADODataSet.next; // occurs immediately, first 10 rows already in
cache
end;
BufferedADODataset.next;
// scrolling past cache, fetch the next 10 actual rows using the prepared
query, something like
// sp_execute 1 11,12,13,14,15,16,17,18,19,20
// put those 10 rows in the cache and return the first (row 11) in
response to the .next
BufferedAdoDataset.commandtext := 'Select * from BigTable order by
ZIPCODE';
BufferedAdoDataset.Active := true;
// behind the scene's, the APKRecordSet cache is flushed, then we
// 1) APKRecordSet = results of "Select PK_Field_Of_BigTable from BigTable
order by ZIPCODE"
// 2) sp_execute 1 1,2,3,4,5,6,7,8,9,10 // use the prepared query with up
to 10 PK Values.
and so on.
Benefits of such a bufferedAdoDataset:
1) For a give selection (and order), after we retrieve the keyset we know
how many rows exist and also know the order to display them. A grid slider
can correctly be set and a user could drag it to a relative location within
the rowset.
2) Record count is known (or is reasonable close to correct).
3) The user can also go to the very end or back to the beginning (scroll
both ways).
4) The PK cache (APKRecordSet or simply "keyset") can be used for dataset
bookmarks.
5) using an async request, future rows could be retrieved while and after
the current request is satisfied.
6) bound data controls designed with a file server/table in mind (i.e.
DBGRID) will work again!
It seems that behavior should be implemented within ADO itself, but appears
that MS has chosen not to when MSSQL is the server side. Perhaps a client
side ctKeyset works with a different server. Then the ADODataset property
"CacheSize" would be useful. But CacheSize is useless with a client side
ctStatic Cursor when the server side is a MSSQL server.
If you think I have any or all of this wrong or know of a
"BufferedAdoDataset", I would like to hear from you! I dont have access to
the "TClientDataSet" (could only afford Professional/ADOexpress), so I dont
know if it implements any of this.
Pat
