[SQL] Client and server cursors
Andreas Grosam
agrosam at computerworks.ch
Wed Mar 5 02:49:49 CST 2003
Hi All,
It's well chosen to discuss these issue, now ;)
For a DB App developer, the "Cursor" is the primary instance to insert, retrive, view and modify records from the database.
Since a cursor may be quite complex regarding its implementation, a cursor should also deliver a tradeoff between its features and performance respectively resource requirements.
The most simplest cursor is most likely a "forward" "read only" "sensitive" cursor. That is, it is only capable for forward iteration, is not updateable and retrives the records from server to the client only once when the current record will be fetched. The cursor is also not "stable" regarding modifications to the database from other cursors once it has been opened.
Its useability is limited.
A more feature rich cursor, which might be used within a client application in a table view for instance is a "random access" "updateable" "insensitive" cursor.
So, we can see some key propperties for a cursor:
- a cursor is either updateable or read-only.
- a cursor may have forward, bidirectional or random access iterator capabilities.
- a cursor may be either "Sensitive" or "Insensitive" or - the unspecified case - "Asensitive".
I want to explain "Sensitivity, briefly:
"Sensitivity" defines whether a cursor's row values may be affected by a modification to the database made other than through that cursor.
According to a proposal to the new SQL3 Standard (not yet available):
A change to database data is said to be visible to the cursor CR if and only if it has an effect on CR by inserting a row in CR, deleting a row in CR, changing the value of a column of a row of CR, or reordering the rows of CR.
If a cursor is open, and the SQL-transaction in which the cursor was opened makes a significant change*) to database data, then whether that change shall be visible
through that cursor before it is closed is determined
as follows:
- if the cursor is insensitive, then significant changes shall not be visible
- if the cursor is sensitive, then significant changes shall be visible
- if the cursor is asensitive, then the visibility of significant changes is implementation-
dependent.
*) Note: "significant changes" are committed changes from other transactions.
[As stated from a proposal to the new SQL3 Standard:
- a INSENSITIVE cursor is read-only.]
The SQL3 Standardization defines the behavior in more detail - but this would be far beyond the scope of this mail. If somebody is interested, please ask.
In addition to the Updateability, Sensitivity and Iterator capabilities, there are other propperties which are not so directly visible to the client, however affect its implementation:
Server-Side and Client-Side Cursors:
Every Cursor uses temporary resources - namely, its row values. If the resources are managed and owned by the server, then this is a Server-Side Cursor. If these resources are managed and oend by the client, this is a Client-Side cursor.
Both types have pros and cons:
For Client-Side cursors the server sends the whole result set over the connection after it has been created. After that, the Server releases its resources and the Client is responsible for allocating and managing the resoures required to hold the result set on the client side.
Client-Side Cursors:
-------------------
Advantages:
- For the Client, once loaded, it has quick access to the row values.
- For the server, it does not need to hold resources which may grow significantly when managing a lot of server side cursors at once.
Disadvantage for the client:
- it MUST load all row data before it can process the result set.
- it is now responsible for managing the resources which requires dedicated code on the client side.
For instance MySQL:
MySQL does only have Client-Side Cursors. You have to choices for retriving row data:
a) loading it ALL at once and let it manage by the client side mysql_ResultSet. This simply allocates each column value on the heap.
b) fetching one row by one UNTIL ALL rows have been fetched. Then the client application code is responsible for managing the data.
IMO, this is quite inconvenient since you HAVE to fetch all rows!!
Server-Side Cursors
-------------------
The server manages the resources for all server-side cursors on the server machine.
The server returns ONLY the REQUESTED rows over the connection.
Advantages:
- The client does not need to manage resources and does not need to allocate them.
- minimizes network traffic.
- ResultSets are more quickly available for the client, since it must not be downloaded.
- Enables sophisticated caching concepts and resource management on the server side.
Disadvantages:
- may temporarily consuming precious server resources for each open server side cursor.
One other aspect arises from the fact that a cursor is inherently associated to a transaction.
(This has something to do with the locks which might be aquired when building a result set, and when the cursor is updateable).
So, when the transaction will be closed - that is when it will be either committed or rolled back - then all its associated cursors will be unaccessable - means, they will be closed implicitly, too.
But sometimes, developers want to use a cursor even when the corresponding transaction has been closed. So a Cursor shall be "detachable" from its transaction. In SQL3 notation this refers to a "holdable" cursor.
So, "holdable" Cursors remain open when its transaction has been closed. Again, the upcoming (when??) SQL3 Standard explains the behavior of holdable cursors in detail.
Personally, I find this extremely usefull. This is just a Container which can be used just as any other container - but has been populated from a database query, may backup on disk, can be updated, etc.
Now, the question arises: what do we want??
[please look below, too - I also commented Ruslans suggestions]
On Dienstag, 4. März 2003, Ruslan Zasukhin <sunshine at public.kherson.ua> wrote:
>Hi guys,
>
>Just want discuss a little issue of cursors for Valentina
>Client.
>
>As some of you know mySQL and Postgre, have client-side and server-side
>cursors.
>
>When e.g. MySQL create client-side cursor this means that found N records
>are transferred to client into its RAM, and there is no more overhead on
>Server side.
>
>Now you can ONLY read records of this cursor.
>
>IF you want change or add or delete some record of this cursor, you need
>send to server OTHER SQL command as
> "INSERT ... " or
> "UPDATE .. WHERE ID = N"
> "DELETE .. WHERE ID = N"
>
>-----
>What we try now implement for Valentina is ability to use the SAME cursor
>API, that we use for local dbs.
>
>So this looks as
>
> curs = db.SqlSelect( "SELECT ...", ReadWrite, kClient )
Alternatively, if we want an updateable cursor, we could specify this through issueing the query command with the "FOR UPDATE" clause:
Cursor c = db.SqlSelect("SEELCT * FROM T WHERE ID = 100 FOR UPDATE");
This has the advantage, that the command string can be retrived from anywhere - for instance a SQL console program - and which then creates the appropiate cursor.
-> yields to an updateable cursor - provided, the relation is inherently updateable, too - which in this example is the case.
Regarding Client versus Serve Side Cursor:
Defaults to a third version, which first manages the resources on the server side - once fetched, they become onwed by the client and the server immediately releases the fetched rows.
This may be called "defered" ClientSide Cursor. Combines several advantages at low cost.
>
>Now if you want do modifications of records you can do simply:
>
> curs.Stringfield( "name" ) = 'something new'
> curs.Update(); <<<<<<<< this send command to server.
>
>€€>> What you think about this approach ?
>€€>> Anybody see any potential problems with this ?
>**>> I do not remember if any other db use this way? IT seems I have see
>always generation of new SQL command in background...
This is all ok. I like it.
JDBC defines this, too (similar).
One usefull addition would be such a construct:
Notice, that a cursor only have ONE current record. So, if you want simultaneously access two values from a certain column from different rows - or if you want access the record as a whole - you might find this usefull:
class Cursor; // the valentina cursor
Cursor c = /*...*/; // create and open the cursor.
c.pos(1); // position the the first record
Cursor::RowValue row1 = c.value(); // create and initialize a "RowValue" from current record
Cursor::RowValue row2 = c[2]; // position to pos 2, then assign row2 the values of the
// current record
// note, that row1 now contains a copy of the record at position 1, and row2 contains
// a copy of the record at position 2.
if (row1 == row2)
cout << " Heureka, they are the same!" << endl;
cout << row1[1]; // print out the first column.
cout << row1["name"]; // print out column with name "name".
row2["name"] = "Ruslan"; // assign the first column of variable row2 a value
c[1] = row1; // position the cursor to pos 1, then copy the
// values of row1 into the cursor buffer
c.Update(); // now upadte record at position 1
// Store the row values -just for fun - or for later retrival:
typedef std::map<Cursor::pos_type, Cursor::RowValue> Result;
Result r;
r.insert(Result::value_type(1, row1));
r.insert(Result::value_type(2, row2));
or in a vector:
std::vector<Cursor::RowValue> v;
v.push_back(row1);
cout << v[0]["name"] << endl;
This is what I have already implemented for another "high level" cursor. It can be simply incorporated into the valentina cursor, though. Some details ommitted. For C++ only.
You may wonder what the RowValue is? - source can be made available if it is considered to be usefull.
What do you think?
Best regards
Andreas
>-----
>As for me I think this is really MORE EASY and more comfortable way,
>Then generate new SQL string for command.
>
>And this looks to be more effective, because again,
>-- you do not spend time and resources to build SQL string
>-- server do not spend time to parse it.
>
>Data simply go by special protocol in binary form from client
>to server.
>
>
>--
>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
>
More information about the Valentina
mailing list