Board index » delphi » Client Side Keyset cursors instead of static cursors

Client Side Keyset cursors instead of static cursors

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

 

Re:Client Side Keyset cursors instead of static cursors


Quote
"Pat Riley" <pri...@piercefire.org> wrote in message

news:3c5324de_2@dnews...
| 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).

A client-side Keyset is not possible.

--

Quidquid latine dictum sit, altum viditur.
#319

Re:Client Side Keyset cursors instead of static cursors


Hi
a client side cursor will get the result set from your qoery and store it at
the client and there for it is static
the data won't be modified by the server unless you resync or requery

you can use the CacheSize and MaxRecords propertys to better control the
amount of retrived records

a Keyset cursor will reflect on-line changes in the database and there for
must   be handled thrue the server
which makes it server side cursor.

Regards
--
   David  B.E      supp...@agrosoft.co.il
   (Delphi is behind the power of things.................)

Re:Client Side Keyset cursors instead of static cursors


I realize that a Client-Side Keyset doesnt work, but can you tell me why the
idea of a client-side keyset (or at least a cache of Primary Keys (PK's) on
the client-side could not be used to reference rows of a table?
Quote
"DRS" <d...@removethis.ihug.com.au> wrote in message news:3c541193_2@dnews...
> "Pat Riley" <pri...@piercefire.org> wrote in message
> news:3c5324de_2@dnews...
> | 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).

> A client-side Keyset is not possible.

> --

> Quidquid latine dictum sit, altum viditur.
> #319

Re:Client Side Keyset cursors instead of static cursors


Quote
"David B.E" <agros...@arava.co.il> wrote in message news:3c5458a2_1@dnews...
> a client side cursor will get the result set from your qoery and store it
at
> the client and there for it is static the data won't be modified by the
> server unless you resync or requery

I accept this; a ClientSide Cursor will cache all rows it retrieves from the
server
and will not be modified unless you resync or requery.  Using the ADODataset
resync or requery is also a bad idea unless you want to download the ENTIRE
table again.  I have found AdoDataset1.recordset.requery(adAffectCurrent) to
be useful in this situation.

Quote
> you can use the CacheSize and MaxRecords propertys to better control the
> amount of retrived records

Not really!  True, you can limit the "MaxRecords" of a record set (same as
"TOP"),
but that is hardly useful if your trying to show the user all records of a
table.  The
CacheSize does not appear to work with a ClientSide Cursor at all, no matter
what
I set CacheSize to, when I open a table using a client side cursor all rows
of the
table are downloaded.

Quote
> a Keyset cursor will reflect on-line changes in the database and therefore
> must be handled thru the server which makes it server side cursor.

Ah.... That helps clarify one thing.  I'm not looking for this kind of a
"Keyset".  The
one I am looking for (as used by a bufferedAdoDataset) is a simple cache of
all PK's that existed at the time the table was open.  I dont expect the
cache to
somehow stay in sync with changes that have occurred at the server by
others.

Re:Client Side Keyset cursors instead of static cursors


Quote
"Pat Riley" <pri...@piercefire.org> wrote in message

news:3c545dca_2@dnews...
| I realize that a Client-Side Keyset doesnt work, but can you tell me why
the
| idea of a client-side keyset (or at least a cache of Primary Keys (PK's)
on
| the client-side could not be used to reference rows of a table?

There is no way of determining what changes have been made to the database
since a client-side dataset was created short of applying any updates and
resolving any conflicts which surface.  This is fundamentally contradictory
to the concept of a keyset cursor, which accesses the database every time
the cursor position moves out of ADO's record cache.

--

Quidquid latine dictum sit, altum viditur.
#319

Re:Client Side Keyset cursors instead of static cursors


Quote
"DRS" <d...@removethis.ihug.com.au> wrote in message news:3c559a9a_1@dnews...
> There is no way of determining what changes have been made to the database
> since a client-side dataset was created,, short of applying any updates
and
> resolving any conflicts which surface.  This is fundamentally
contradictory
> to the concept of a keyset cursor, which accesses the database every time
> the cursor position moves out of ADO's record cache.

So when your using a ServerSide Cursor with ctKeyset, does ADO at the
workstation
cache more than one single record?  My sense is no, but I'm willing to be
proven wrong.  It looks to me like only one record at a time is transmitted
to
the workstation as the position in the recordset changes.

Re:Client Side Keyset cursors instead of static cursors


From Microsoft, a ClientSide Keyset is possible;

"With keyset-driven cursors, a key is built and saved for each row in the
cursor and stored either on the client workstation or on the server
machine."  (first sentence, third paragraph of
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsen...
ml/veconkeysetdrivencursors.asp
)

Apparently they are not implemented though.  Sigh.

Pat.

Quote
"DRS" <d...@removethis.ihug.com.au> wrote in message news:3c541193_2@dnews...
> "Pat Riley" <pri...@piercefire.org> wrote in message
> news:3c5324de_2@dnews...
> | 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).

> A client-side Keyset is not possible.

> --

> Quidquid latine dictum sit, altum viditur.
> #319

Re:Client Side Keyset cursors instead of static cursors


Here is a link where you will find some info on ADO cursor types, location,
and locking on SQL Server:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5913

[from a post by Andy Mackie]

Re:Client Side Keyset cursors instead of static cursors


Thank you....  I read the article but still do not know why a client side
keyset is not possible.  Maybe its the use of the term "KeySet" that causes
a problem.  Most definitions of a "Keyset" say they must be on the server,
because they can track a subset of changes made by others.  This tracking
requires that a ADO Keyset Cursor live on the server side.  I understand
this requirement.  Most articles I have read have accepted that "Keyset"
implies that the tracking of changes is included and so the cursor must live
on the server side.  Who co-opted the term "Keyset" for this role only?

I'm trying to make a case for a non-tracking (doesn't track changes, more
like a static client/side cursor), "KeySet" that can live on the ClientSide.
I'm using the term "Keyset" in a more generic way (i.e. Its a set of primary
KEYS only!), and believe that clientside "keysets" would be very useful when
trying to access a very large recordset..  As the article you mention points
out very well, a result set only guarantees membership.  A "Keyset" provides
order and identity.  Nothing about this says the "Keyset" must live on the
server, unless the Keyset is also expected to track changes made to the
resultset.  If you eliminate this last requirement (tracking changes), then
a client/side keyset should be possible.   Instead of taking an entire
snapshot of the recordset (static client/side cursor), take a snapshot of
the set of PK's for that recordset.  Now with each PK, you can buffer and
scroll the rows of a recordset as you need them instead of downloading the
entire recordset on the initial open.

Ok, maybe everyone has accepted that the definition of "Keyset" implies that
its a cursor type that must live on the server and can track some of the
changes made to the recordset.  Maybe I need to coin a new cursor name that
better describes a "set of keys" that can live on the Client Side, providing
Order and Identity to the rows of a recordset.  Any suggestions?

Thanks for the dialog.

Pat.

Quote
"Demian Lessa" <dem...@knowhow-online.com.br> wrote in message

news:3c57fe98_2@dnews...
Quote
> Here is a link where you will find some info on ADO cursor types,
location,
> and locking on SQL Server:

> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5913

> [from a post by Andy Mackie]

Re:Client Side Keyset cursors instead of static cursors


Quote
"Pat Riley" <pri...@piercefire.org> wrote in message

news:3c5847e3$1_2@dnews...
| Thank you....  I read the article but still do not know why a client side
| keyset is not possible.  Maybe its the use of the term "KeySet" that
causes
| a problem.  Most definitions of a "Keyset" say they must be on the server,
| because they can track a subset of changes made by others.

Absolutely.  A Keyset cursor is similar to a Dynamic cursor in that both
reflect the current state of the database.  That is only possible where the
dataset uses a server-side cursor.

| This tracking
| requires that a ADO Keyset Cursor live on the server side.  I understand
| this requirement.  Most articles I have read have accepted that "Keyset"
| implies that the tracking of changes is included and so the cursor must
live
| on the server side.

There's no implication about it.  When you use a client-side dataset the
server spits it out as fast as it can using a specialised form of a
ForwardOnly cursor called a firehose cursor.  The dataset is then discrete
from the database and therefore by definition its cursor must be static
(ForwardOnly or Static).  A dataset which is discrete from the database
cannot by definition dynamically reflect changes made to the database.
Client-side datasets are not managed by the database engine, they're managed
by the ADO Cursor Engine.

--

Quidquid latine dictum sit, altum viditur.
#319

Re:Client Side Keyset cursors instead of static cursors


Quote
"Pat Riley" <pri...@piercefire.org> wrote in message

news:3c5460ac_1@dnews...

Quote
> Ah.... That helps clarify one thing.  I'm not looking for this kind of a
> "Keyset".  The
> one I am looking for (as used by a bufferedAdoDataset) is a simple cache
of
> all PK's that existed at the time the table was open.  I dont expect the
> cache to
> somehow stay in sync with changes that have occurred at the server by
> others.

You may want to look at the Optimize dynamic ADODB.Recordset property at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado2...
mdprooptimizepropertyrds.asp

From the sample, it could work, but reading through the links that sent me
to this property, I do not have much confidence in it.  The ADO client-side
cursor system is pretty robust, so it might work, but I would not be
surprised if it does not work.

Not a warm fuzzy, but it is better than a cold shoulder.

Al

Re:Client Side Keyset cursors instead of static cursors


Al,

I have read about this before.  Apparently you can ask that a recordset,
cached at the client, have fields that have been indexed (optimized).  Since
you can search a clientside record set, it is a logical extension to the
record set that allows a search.   This would be great if your entire
records has been sent to the client workstation; something I'm trying to
avoid with my flavor of a "keyset" (in the literal sense).  Thanks for the
link.  Pat.

Quote
"Al Cantu" <alca...@att.net> wrote in message news:3c58a8b1$1_1@dnews...

> "Pat Riley" <pri...@piercefire.org> wrote in message
> news:3c5460ac_1@dnews...
> > Ah.... That helps clarify one thing.  I'm not looking for this kind of a
> > "Keyset".  The
> > one I am looking for (as used by a bufferedAdoDataset) is a simple cache
> of
> > all PK's that existed at the time the table was open.  I dont expect the
> > cache to
> > somehow stay in sync with changes that have occurred at the server by
> > others.

> You may want to look at the Optimize dynamic ADODB.Recordset property at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado2...
Quote
> mdprooptimizepropertyrds.asp

> From the sample, it could work, but reading through the links that sent me
> to this property, I do not have much confidence in it.  The ADO
client-side
> cursor system is pretty robust, so it might work, but I would not be
> surprised if it does not work.

> Not a warm fuzzy, but it is better than a cold shoulder.

> Al

Re:Client Side Keyset cursors instead of static cursors


Quote
"Pat Riley" <pri...@piercefire.org> wrote in message

news:3c597247_1@dnews...

Quote
> I have read about this before.  Apparently you can ask that a recordset,
> cached at the client, have fields that have been indexed (optimized).
Since
> you can search a clientside record set, it is a logical extension to the
> record set that allows a search.   This would be great if your entire
> records has been sent to the client workstation; something I'm trying to
> avoid with my flavor of a "keyset" (in the literal sense).  Thanks for the
> link.  Pat.

For a good article on this topic, see
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5913
For pointers on cursors, see the following links:
http://www.sql-server-performance.com/cursors.asp
http://www.concresco.com/speed.htm
http://www.4guysfromrolla.com/webtech/112998-1.shtml
http://www.avdf.com/oct98/art_ot002.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/opti...
p_tun_1a_71nw.asp

You could mimic a "keyset" client cursor by loading a static client-side
rowset of the keys and then some combination of events (and calculated
TField objects provided you want to use data-bound controls) to fetch
smaller client-side static copies of the visible rows. This would give you
an effect that would mimic a client "keyset"  based on your definition,
since Delphi would fetch only the data for the visible rows. You will need
keep track of which keys had data already downloaded.  You may have better
luck doing this by using non-bound controls and populating the controls
yourself.

I used a similar technique in populating a treeview, only calling the rest
of the key's data when I wanted to see the children of a particular key
node.

Another option is to use a master-detail approach, where you query the
detail data when you select a certain key.  One example would be having a
master query of only the keys, and a detail query of the entire row.

Hope that helps.

Al

Re:Client Side Keyset cursors instead of static cursors


Your reply is right on target, on all counts.  Thanks for the idea's, tips
and suggestions.  It seems that if most are using bound data controls than a
dataset that handles the mimic'd client keyset would be the way to go (when
a client keyset is called for).  Since no one has yet produced the
"Buffered" ADODataset, I'm wondering if most projects avoid bound data
controls.  Do you have a sense on this?  Are there serious cons to using
bound data controls in general?

Completely off the subject, are you related to Marco?  Just curious, I don't
really need to know but everytime I see one of your posts I wonder.

Pat.

Quote
"Al Cantu" <alca...@att.net> wrote in message news:3c5b6259$1_2@dnews...
> "Pat Riley" <pri...@piercefire.org> wrote in message
> news:3c597247_1@dnews...
> > I have read about this before.  Apparently you can ask that a recordset,
> > cached at the client, have fields that have been indexed (optimized).
> Since
> > you can search a clientside record set, it is a logical extension to the
> > record set that allows a search.   This would be great if your entire
> > records has been sent to the client workstation; something I'm trying to
> > avoid with my flavor of a "keyset" (in the literal sense).  Thanks for
the
> > link.  Pat.

> For a good article on this topic, see
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5913
> For pointers on cursors, see the following links:
> http://www.sql-server-performance.com/cursors.asp
> http://www.concresco.com/speed.htm
> http://www.4guysfromrolla.com/webtech/112998-1.shtml
> http://www.avdf.com/oct98/art_ot002.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/opti...

- Show quoted text -

Quote
> p_tun_1a_71nw.asp

> You could mimic a "keyset" client cursor by loading a static client-side
> rowset of the keys and then some combination of events (and calculated
> TField objects provided you want to use data-bound controls) to fetch
> smaller client-side static copies of the visible rows. This would give you
> an effect that would mimic a client "keyset"  based on your definition,
> since Delphi would fetch only the data for the visible rows. You will need
> keep track of which keys had data already downloaded.  You may have better
> luck doing this by using non-bound controls and populating the controls
> yourself.

> I used a similar technique in populating a treeview, only calling the rest
> of the key's data when I wanted to see the children of a particular key
> node.

> Another option is to use a master-detail approach, where you query the
> detail data when you select a certain key.  One example would be having a
> master query of only the keys, and a detail query of the entire row.

> Hope that helps.

> Al

Go to page: [1] [2]

Other Threads