Feedback
Did this article resolve your question/issue?

   

Article

Client Side vs Server Side Cursors

« Go Back

Information

 
TitleClient Side vs Server Side Cursors
URL Name6343
Article Number000173636
EnvironmentProduct: Connect for ODBC
Version: All supported versions
OS: All supported platforms
Database: All supported databases
Application: All supported applications
Question/Problem Description

What is the difference between Client Side and Server Side Cursors?

Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution

Client-Side Cursors Versus Server-Side Cursors
Every cursor uses temporary resources to hold its data. These resources can be 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 machine. The cursor is called a server-side cursor when these resources are located on the server machine.

Client-Side Cursors
With a non-keyset client-side cursor, the server sends the entire result set across the network to the client machine. The client machine provides and manages the temporary resources needed by the cursor and result set. The client-side application can browse through the entire result set to determine which rows it requires.

Static and keyset-driven client-side cursors may place a significant load on your workstation if they include too many rows. 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 may be perfectly appropriate and performance may 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 machine, 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.

Server-Side Cursors
With a server-side cursor, the server manages the result set using resources provided by the server machine. 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.
Server-side cursors also permit more than one operation on the connection. That is, once you create the cursor, you can use the same connection to make changes to the rows — without having to establish an additional connection to handle the underlying update queries.

However, it's 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. With SQL Server, you can have pending results in multiple statement handles.

Advantages to using server-side cursors include those in the following list.

  • Memory usage The client does not need to cache large amounts of data or maintain information about the cursor position because the server is doing that.
  • Performance If you are going to access only some of the data in the result set, or access the data just a few times, a server-side cursor minimizes network traffic.
  • Additional cursor types With server-side cursors, both keyset and dynamic cursors are available.
  • Positioned updates Server-side cursors support direct positioned updates, whereas ODBC simulates positioned cursor updates by generating an SQL search and update statement. Direct positioned updates are not only faster, they avoid the risk of unintended update collisions.

Notice that server-side cursors do not support the execution of queries that return more than one result set. However, it is possible to use the server-side cursor library with this type of query if you request a forward-only, read-only cursor with a rowset size of 1. Basically, this set of options disables the scrolling overhead associated with the cursor and enables the cursor driver to manage each result set individually.

For information on how to use Client-Side cursors, see the ODBC Programmers Reference at:
http://msdn.microsoft.com/en-us/library/ms714177(VS.85).aspx 
and Using the ODBC Cursor Library at:
http://msdn.microsoft.com/en-us/library/ms713601(VS.85).aspx

Workaround
Notes
Last Modified Date9/14/2015 2:05 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.