Amazing no one knows this!!! Client side vs Server Side cursors
Brian C. Drab
When connecting to Access databases using ADO, should you use client-side
cursors or server-side cursors and most importantly why??
I printed several Knowledge base, MSDN articles, etc off and they all
contridicted themselves. I finally called Microsoft Pay Per Incident tonight
and talked to their lead database and DCOM person and he first said that you
should use client side cursors and that many of the articles that are
written by Microsoft are incorrect. Great.
I just don't know who to believe? Does anyone really know?
--
Brian C. Drab, MCP
Computer Consultant
MOUS Office 97 Master
ICQ #45264667
Dan Billingsley
Why is the discussion of which cursor to use connecting to Access DB any
different that when connecting to any DB?
Also, are you talking about the DB being on a share on a server, or local?
Forgive me, I'm just always trying to see if there are holes in my
understanding of these concepts.
Brian C. Drab <bria...@ameritech.net> wrote in message
news:4f_y4.209$XF.4...@nntp0.detroit.mi.ameritech.net...
Frank Andrew Buchan, III
In its simplest form, it boils down to this:
Client-side cursors ignore the specific features of the data provider
(driver) being used, and can essentially allow for the use of most ADO
features even if the provider itself does not directly support that feature.
For older providers, and incomplete implementations of OLE DB providers this
can create a more capable and robust solution, because you aren't restricted
to a specific provider-supported set of ADO features. Its an abstraction.
Server-side cursors do the opposite, exposing the features of the data
provider being used. This means that special provider features not directly
part of ADO can be accessed, but it also means that unsupported ADO features
cannot be employed, so the solution you create is bound more closely to the
abilities of the provider. This means that its a tad harder to lift out a
backend Access database and drop in a MS SQL Server solution, because some
of your code might be reliant upon specific features that the MS SQL Server
provider doesn't expose. (Unlikely to be a problem in many solutions, but
still to the point.)
I believe the confusion many people have is that they mix the concept of
cursor location with cursor type. There is a clear relationship because if
you user server side type cursor location some cursor types may not actually
be supported, so you could end up writing code that is not portable across
providers. The cursor types actually control response and motion via the
recordset itself.
Dynamic cursor type allows for all movement commands (potentially excluding
bookmarks as that is provider specific), and will let you view the changes
made by others in the recordset. This can server a useful purpose in
multiuser environments, etc.
Keyset cursor type is much like dynamic, but it does not show records added
by other users after the recordset is opened. It does reflect other edits in
existing records, and does reflect deletions (in a somewhat awkward
fashion).
Static cursor type basically takes a snapshot of the recordset being opened,
and holds onto it, isolating you from seeing anyone else's data operations.
This can be useful too, especially when creating reports that do not require
write-back but may require inpersistent modifications, etc.
Forward Only cursor type is exactly like a Static cursor type except that it
allows you to only move forward through it once. (This is extremely useful
for pulling data to reports in a stream.)
Again, most of the confusion seems to come from a misunderstanding of the
fact that cursor location and curso type, while very closely related, are in
fact different issues. For some real world examples, you might use a Server
side cursor location to get at a special feature of some provider that is
otherwise impossible to access. For the most part, with Access solutions,
the default server side cursor location works fine -- but the client side
location is more flexible if you want to write portable solutions where the
backend database can be replaced more easily.
Hope that makes some sense, and my typographic errors don't obscure the
meaning....
"Brian C. Drab" <bria...@ameritech.net> wrote in message
news:4f_y4.209$XF.4...@nntp0.detroit.mi.ameritech.net...
> Is there anyone out there that really knows the correct answer to this???
>
> When connecting to Access databases using ADO, should you use client-side
> cursors or server-side cursors and most importantly why??
>
Brian C. Drab
concern.
When you use a client-side cursor, your only option is a static cursor type.
You can't choose any of the other ones???
Also, when you set the cursor location to server, dynamic cursor types are
not directly supported. There are different methods that are used other than
keyset when you set it to dynamic which makes it more efficient when working
with large data sets but it's really not directly supported.
If you haven't encountered this, you may want to try it. I think it's a bug
but maybe I just don't understand it. Can you make heads or tails of this???
I really want to understand FULLY the cursor types which directly tie to the
cursor location.
1. Start a new EXE
2. Add a Data Environment to the project
3. Using the default connection, connect to any Access 97/2000 database (You
can use the Jet 3.51 or 4.0 provider..doesn't matter)
4. Add a command to the connection object and set the properties to pull any
fields from any table...doesn't matter.
5. Drag the command object onto the form to automatically bind the controls
to the form.
6. Run the project and everything is fine. As you know the default cursor
engine that is being used is the client-side cursor engine and it's a
read-only recordset that is being returned.
7. Open the data environment again and change the properties of the command
object to user the server-side cursor engine, cursor type to keyset, and
lock type to optimistic.
8. Run the project now. Nothing shows.
....now for the Bug????
9. Add another command object to the connection. You can set properties if
you wish but not needed for the example.
10. Run the project and still nothing which I would expect. No reason for a
new command object to affect it.
11. Now add a child command to the new command you just added. No need to
set any properties for the example.
Now run the project and huh??? It works!!!!!!!!
Thanks for any help/insight you have.
Brian C. Drab
To be more specific, the Access database is split into a front-end and
back-end and the back-end database will reside on a server. Doesn't really
matter however since Access is not a client-server database, when you set
the cursor location to server-side, the keys that are generated will still
be placed on the local machine.
Thanks for any insight you have on this.
Marcos Masagão Ribeiro
Marcos
Frank Andrew Buchan, III <future....@sympatico.ca> escreveu nas notícias
de mensagem:uQh6ffRj$GA.236@cppssbbsa03...
Jeff Kohn
differences between client- and server-side cursors aside from just what
functionality is supported. With a server-side cursor, rowdata isn't
actually fetched to the client until that row is accessed, since the cursor
is on the server. This can provider a performance benefit in some cases,
especially if the perceived time of getting that first record is what is
important. On the other hand, server-side cursors use more resources on the
server, which can cause performance problems when you try to scale your
application to more and more users. Because of this, I tend to feel that
client-side cursors are the best choice unless you have a specific reason
for using a server-side cursor.
Then again, the above is only true for a client-server database. With Jet, I
can't see how it would make any difference since there is no server. For
this reason, I would recommend client-side because it will probably make
porting the code to another database easier if the need arises.
Jeff
"Frank Andrew Buchan, III" <future....@sympatico.ca> wrote in message
news:uQh6ffRj$GA.236@cppssbbsa03...
Frank Andrew Buchan, III
pretty much live with client-side cursors.
Even developing against SQL Server the only time I actually use server-side
cursors is when the specific advantages outweight the performance hit, and
that's been once in the space of 8 months of development and 3 massive
client-server systems.
What I think is happening with all the confusion about cursor issues is that
the terminology was badly chosen. When many people hear client/server they
immediately try to roll into the phrase all they know about client/server
technology of old. While there is certainly a connection between the
concepts, most of my team expressed a lot of angst over the fact that all
they were really getting was a chance to decide which layer of the
distributed application was handling the rowset control. Some wanted the
server-side cursors to be overwhlemingly powerful in all circumstances, and
others wanted the separation of authority to be along the lines of
backend/frontend services. Few immediately caught on to how really simple
the issue is. For this I suspect we can rightfully blame Microsoft, for a
series of pretty poor examples as to what each scenario was designed to
accomplish. That's a shame not because the examples were so poor, overall,
but because the technology itself seems to be eating a lot of dust instead
of proving its worth.
Some days, it also makes me want to start an ADO concepts website of my own.
I imagine I would eventually learn how to make ADO even better than it is
today in our software....
"Jeff Kohn" <jk...@houston.no-spam.please.rr.com> wrote in message
news:uP5SSYcj$GA.217@cppssbbsa05...
Ben Digby
<future....@sympatico.ca> wrote:
>
>Some days, it also makes me want to start an ADO concepts website of my own.
>I imagine I would eventually learn how to make ADO even better than it is
>today in our software....
Yes! Yes! Yes! I am moving over to ADO from DAO and while the basics
are easy to grasp, there seem to be so many more ways of doing
different things in ADO, it's a minefield.
So, if you can pursue that ADO concepts website, I'm sure you'd get
thousands of happy 'customers' !
Ben
ssto...@apexmail.com
application can benefit through batch operations, and resources on the
client. Maybe someone can site some specific examples of why you would
want to use a server-side cursor if you have the choice. I can't think
of any real-world scenarios myself.
In article <uP5SSYcj$GA.217@cppssbbsa05>,
Sent via Deja.com http://www.deja.com/
Before you buy.
Frank Andrew Buchan, III
the use of server-side cursors. The row fetch can be completely dynamic in
as much as the data never leaves the store until the request is processed,
so any query against the data store will always return the most up to date
information. I agree that a real-world example is hard to come by, because
good design can eliminate some of the value, but I did have occasion to use
a server-side cursor recently for a very odd small scale application. (I
won't insist this was the best solution, but it was fast and it met the
client needs.)
In the client environment they are using a multitude of small raw parts to
create their product. They also do precision machine shop work to create
specialized parts, and a combination of inventory parts and special order
parts make up most orders. The work order process in their shop follows a
clearly defined path, and various people are responsible for various aspects
of product development. In all, three "inventory process managers" are
handling the workflow from three stations in widely separated areas. They
all issue orders and quotes on time-to-delivery based on inventory
conditions when the order is formulated. What was happening was that the
inventory itself was remote, as well. It was becoming increasingly common to
quote an order, only to find out that the parts were not in inventory when
the assembly was started. The assumption was that the work order planning
system was buggy, and a trace was done to determine why the application was
misreporting inventory.
The investigation revealed that the application itself was doing its job. It
was never designed to calculate base time to delivery, because the client
felt that was too job-specific for the machine to be trusted. So, the
application saw the inventory overages and applied them to the schedule for
order or production appropriately. What the problem really was, was that the
human operators were seeing the inventory for the requested parts as it
stood when they began creating the item list for the production management
process to follow. As the shop became busy, because many of the smaller
parts were shared across many products, the three inventory managers were
fetching counts, but as they were decremented they were no longer seeing
live data. For example, worker 1 identified that widgets would be required
to create their order. The application fetched the inventory count for
widgets, which was being displayed properly. It later decremented the
invenory count by the number of widgets worker 1 indicated were required for
the custom product. The application also knew enough to test the actual
count when it decremented, and if it saw it had 20 on hand, but needed 30,
it would alert the worker to that and then the time-to-delivery could be
modified. When the work order was saved, it would then also automatically
calculate any parts orders required, or production required to create the
items. This was filed and scheduled to meet time-to-delivery requests. What
was happening, of course, is that when the item list was resolved to show
the widgets on hand for worker 1, worker 2 was also requesting widgets. It
was returning the present count when the item type was requested, and
unaware that worker 1 was committing all available widgets to a job. Worker
2 would request maybe 5 widgets, and the application would decide all was
well. Interestingly, the application was a client/sever model where the
parts production and scheduling orders were being filed by the server side
processes. That means the widgets were being slated for production, even
when the inventory managers were not notified -- but they were being slated
for an arbitrary seven days out, making any deliveries prior to that date
potentially late. It took so long to notice because for the most part the
number of orders being done was so great that very seldom were the parts
actually running out, until recently when the company started using a
slightly modified common array of parts.
What was done to resolve this temporarily, was simply to replace the parts
list cursor with a server-side cursor, which dynamically allows us to trap
changes and broadcast them to the client machines. Because the object model
and class structure is client server, the solution works beautifully without
requiring a rewrite of more than a few lines of code. A kludge, but a
functional one and one that doesn't seem to have broken any other
functionality. Was server-side the best solution? Most of the team feels it
wasn't technically the best solution, but for the client the price was right
and it was fast. With a client-side cursor held in that place we would have
had to do about three times as much work to synchronize the three stations.
Again, not necessarily elegant, but it was a functional solution and dos
provide an example.
<ssto...@apexmail.com> wrote in message news:8bm464$71r$1...@nnrp1.deja.com...
Jason Bailey
on this topic would be:
Programming ADO ISBN 0-7356-0764-8 David Sceppa
Which is a great read if you want to understand the ADO cursor engine.
Hitchhikers Guide to Visual Basic & SQL server: Sixth edition William R
Vaughn
Which is a great reference for ADO and other forms of data access
but can be very heavy reading if you have little experience with
data access.
Jas :)
"Ben Digby" <fourum...@hotmail.com> wrote in message
news:38de4deb...@news.clara.net...
> On Tue, 14 Mar 2000 13:39:49 -0500, "Frank Andrew Buchan, III"
> <future....@sympatico.ca> wrote:
>