Database Reference
In-Depth Information
•
Fewer network round-trips:
Since the result set returned by the
SELECT
statement is passed
to the client where the cursor is maintained, extra network round-trips to the server are not
required while retrieving rows from the cursor.
•
Faster scrolling:
Since the cursor is maintained locally on the client machine, it's faster to walk
through the rows of the cursor.
•
Highly portable:
Since the cursor is implemented using data access layers, it works across a
large range of databases: SQL Server, Oracle, Sybase, and so forth.
Client-side cursors have the following cost overhead or drawbacks:
•
Higher pressure on client resources:
Since the cursor is managed at the client side, it increases
pressure on the client resources. But it may not be all that bad, considering that most of the
time the client applications are web applications and scaling out web applications (or web
servers) is quite easy using standard load-balancing solutions. On the other hand, scaling out
a transactional SQL Server database is still an art!
•
Support for limited cursor types:
Dynamic and keyset-driven cursors are not supported.
•
Only one active cursor-based statement on one connection:
As many rows of the result set as the
client network can buffer are arranged in the form of network packets and sent to the client
application. Therefore, until all the cursor's rows are fetched by the application, the database
connection remains busy, pushing the rows to the client. During this period, other cursor-based
statements cannot use the connection. This is negated by taking advantage of multiple active
result sets (MARS), which would allow a connection to have a second active cursor.
Server-Side Cursors
Server-side cursors have the following cost benefits:
•
Multiple active cursor-based statements on one connection:
While using server-side cursors, no
results are left outstanding on the connection between the cursor operations. This frees the
connection, allowing the use of multiple cursor-based statements on one connection at the
same time. In the case of client-side cursors, as explained previously, the connection remains
busy until all the cursor rows are fetched by the application. This means they cannot be used
simultaneously by multiple cursor-based statements.
•
Row processing near the data:
If the row processing involves joining with other tables and a
considerable amount of set operations, then it is advantageous to perform the row processing
near the data using a server-side cursor.
•
Less pressure on client resources:
It reduces pressure on the client resources. But this may
not be that desirable because, if the server resources are maxed out (instead of the client
resources), then it will require scaling out the database, which is a difficult proposition.
•
Support for all cursor types:
Client-side cursors have limitations on which types of cursors can
be supported. There are no limits on the server-side cursors.
