[SQL] Client and server cursors

[Andreas Grosam]

On Mittwoch, 5. März 2003, Ruslan Zasukhin <sunshine at public.kherson.ua> wrote:
>on 3/5/03 6:13 PM, Andreas Grosam at agrosam at computerworks.ch
>wrote:
>
>>>> b) fetching one row by one UNTIL ALL rows have been fetched. Then the client
>>>> application code is responsible for managing the data.
>>> 
>>> Andreas, but this sounds like Sever-Side ForwardOnly cursors.
>>> No ?
>> 
>> YesNo.
>> 
>> Server versus Client Side Cursors says something bout the location of the
>> resources which are required for the cursors to operate as long as it is
>> opened.
>> 
>> If the server is not required to hold any resources when creating a cursor -
>> there is no question of server or client side.
>> 
>> For instance, consider this simple query:
>> Select * from Persons;
>> 
>> If the cursor is specified Sensitive and not updateable and also shall have
>> only forward iterator capabilities - then the server is not required to
>> allocate any resources because it can select the candidate records for that
>> cursor "on the fly" when the client fetches the rows.
>> 
>> The client does also not store the resources, otherwise it would not be
>> sensitive.
>> 
>> As a result, no resources will be allocated at all.
>> 
>> 
>> If the cursor is now specified Insensitive, the server must guarantee this
>> behavior by either creating the complete set and transfering  it all to the
>> client which becomes responsible for the records (Client Side Cursor) or it
>> must create a lokal copy of the records in the server side which then yields
>> to a Server-Side Cursor.
>> 
>> Certainly, in any case, the server may or may not require to temporarily
>> create and hold resources for creating the result set on the server side - but
>> IMO, what counts is the fact which - Server or Client  - becomes responsibel
>> after the creation of the result set and is responsible for it as long the
>> cursor is opened.
>> 
>> If the server releases the resources on its side after they have been fetched
>> from the cursor, then this is a Client-Side Cursor - which also implicitly
>> means that the Client side is actually willing to manage the resources and
>> become the owner of it once they have been fetched.
>> 
>> If a Cursor manages resources on the client side, even if the server was not
>> required to hold and allocate resources while creating the result set - for
>> instance, if it it was able to create the set on the fly, IMO this is a
>> Client-Side Cursor since it manages resources while it is open on the client
>> side.
>
>
>Well, I think it is possible to make more simple definition for this
>classification.
>
>-- IF to read next/prev record cursor must ask for data Server, then this is
>Server-side cursor. Otherwise this is client-side cursor.
>
>Question of resources is SECONDARY, and your example with SELECT * is just
>an optimization... 
>
>----
>In the same time it is obvious that may be really introduced hybrid --
>Differed Client Cursor.
>
>IF user ask for Bidirectional, Deferred, ReadOnly client cursor,
>We can at first keep cursor as Server side. During fetches, client cursor
>STORE records into buffers, so it can faster return to already read records,
>and after all records read the server drop its version of
>cursor.
>
>I don't know if this have sense...may be only for faster reaction of system
>to introduce first records of result.

I can tell you, this in fact is of greate interest and it makes sense for a DB application developer:

Suppose you have a "Address Manager" prorgam where you store Person records.
Suppose,there are 10.000 persons in the table.

Suppose, the application has a window which shows in the upper part a dialog with fields corresponding to the person record.
The lower part of the window shows a list of all persons.

Due to the application needs, we need an "Insensitive", "random access" cursor (due to the list showing the person). This requires the server to create a copy of the records.

If the DBMS would only have Client-Side Cursors, the complete set of records - 10.000 - must be transfered over the network before the window can display the first record.

For the user this is not acceptable because he/she must wait.

For an application develeper this may require to implement a threaded architecture in order to keep the app responsible during the download.


So, in this case, server side cursors would help a lot:
The user has to wait only until the server has finished creating the result set on its side, and transfering the first records which are visible in the table view and the dialog.

Thus, the app is immediately reponsible (except for a fraction of a second).

When the user scrolls up and down, the records will be fetched from the server as required - which is not extremely fast - but sufficient when displaying records in a table view.


On the other hand, if useing server side cursor, the server must allocate these resources and keep track of it until the cursor is closed.

If the cursors is Insensitive, its contents will never change, so it would make sense, to cache the records on the client side - if the client CAN allocate the resources. Once, allocated, it also may become completely responsible for that resources, so the server can release its resources.

So, in the middle of that process this is a "Client-Server-Side" cursor ;)

But seriously, having the option transfering ownership of resources to the client - enables the client to access the records more quickly. Iterating through the rows is then mutch faster - at the cost of resources which must be allocated on the client side.


So, if one wants to process the data in the set, which requires fast iterating, then having the resources on the client side would be a plus.

Andreas
>
>
>-- 
>Best regards,
>Ruslan Zasukhin      [ I feel the need...the need for speed ]
>-------------------------------------------------------------
>e-mail: ruslan at paradigmasoft.com
>web: http://www.paradigmasoft.com
>
>To subscribe to the Valentina mail list go to:
>http://listserv.macserve.net/mailman/listinfo/valentina
>-------------------------------------------------------------
>
>_______________________________________________
>Valentina mailing list
>Valentina at lists.macserve.net
>http://lists.macserve.net/mailman/listinfo/valentina
>