Consider use of server side cursors internally

[brokenthorn]

Does this sum up all of the reasons why **adding support for server side cursors*** in the client is being considered?

Every cursor uses temporary resources to hold its data (in memory, a disk paging file, temporary disk files, or even temporary storage in the database). The cursor is called a client-side cursor when these resources are located on the client computer. The cursor is called a server-side cursor when these resources are located on the server.

While all of the cursor libraries are capable of building cursors with thousands of rows, applications designed to fetch such large rowsets may perform poorly. There are exceptions, of course. For some applications, a large client-side cursor might be perfectly appropriate and performance might not be an issue.

One obvious benefit of the client-side cursor is quick response. After the result set has been downloaded to the client computer, browsing through the rows is very fast. Your application is generally more scalable with client-side cursors because the cursor's resource requirements are placed on each separate client and not on the server.

With a server-side cursor, the server manages the result set using resources provided by the server computer. The server-side cursor returns only the requested data over the network. This type of cursor can sometimes provide better performance than the client-side cursor, especially in situations where excessive network traffic is a problem.

However, it is important to point out that a server-side cursor is - at least temporarily - consuming precious server resources for every active client. You must plan accordingly to ensure that your server hardware is capable of managing all of the server-side cursors requested by active clients. Also, a server-side cursor can be slow because it provides only single row access - there is no batch cursor available.

Server-side cursors are useful when inserting, updating, or deleting records. With server-side cursors, you can have multiple active statements on the same connection.

*_** - at least I hope it's to add support for, and not to switch to just SS cursors only._

[steffengy]

The main reason is looking into whether we can improve dealing more cleverly with overwhelming backpressure (e.g. queries with big resultsets that are handled delayed). Since microsofts JDBC driver doesn't use SS cursors by default, if at all, it will be added as another optional option, so users can choose to use them for specific queries.