The following is a brief summary of some common strategies for using cursors to illustrate how cursors can be optimized to support your enterprise application's data access requirements.
Use cursors sparingly Not all applications need to use cursors to access or update data. Some queries simply do not require direct row updating by using a cursor. Actually, cursors should be one of the last techniques you choose to retrieve data — and then you should choose the lowest impact cursor possible. Notice that whenever you create a result set with a stored procedure, the result set is not updatable using cursor edit/update methods.
Load the cursor immediately Moving to the last row loads the result set and releases the share locks taken when the cursor was built.
Use client-side cursors appropriately Except for static data that is not subject to change by other users over time, data that is replicated on the client machine begins to lose its relevance and accuracy as soon as it arrives. In situations where users need to browse or select from lists longer than a designated limit, you may choose to use server-side cursors where concurrency can at least be managed. Microsoft® SQL Server™ supports keyset-driven cursors that are created on the server. In some cases, this type of cursor can significantly improve performance over client-side cursors, reduce network traffic, and minimize workstation resource requirements.
Use batch cursors Many applications fetch a number of rows at once and then need to make coordinated updates that include the entire set of rows to be inserted, updated, or deleted. With batch cursors, only one round trip to the server is needed, thus improving update performance and decreasing network traffic. Using a batch cursor library, you can create a static cursor and then disconnect from the data source. At this point you can make changes to the rows and subsequently reconnect and post the changes to the data source in a batch.
Fetch a smaller result set Careful design can often reduce the number of rows and columns your application requires. You can benefit through efficient data page use, minimized locking, reduced server processing, and less network traffic.
Use multiple result sets By using a single query that returns several sets of results, you can use the query processor and system resources more efficiently. You can improve performance by running a single query to gather data to fill multiple data-driven list boxes and menus.
Submit multiple queries as one In many cases, your application can submit a set of insert, update, and delete operations as a single SQL statement. This increases performance because it reduces network and server processing overhead.
Execute queries asynchronously If a query takes an extended period of time to run, your application should have the option of either executing code while the query is being processed, or canceling the query. A good way to run your queries asynchronously is to use Microsoft Message Queue Server (MSMQ).
For More Information For more information on asynchronous queries, and an extended discussion of the features and benefits of Microsoft Message Queue Server, search online for "Using MSMQ" in MSDN Library Visual Studio 6.0.
Use transactions Using transactions and sophisticated locking strategies can improve overall system performance.
Use page-level locking Page-level locking balances locking detection overhead with superior transaction throughput when compared to row-level locking. Since all page-level locks are managed in memory, lock acquisition is extremely fast. In the majority of business situations, page-level locking significantly outperforms row-level locking.
Use the minimum lock In high-contention environments, refetching a row with the lock option may be faster than locking an entire screenful of rows.
Use off-hours bulk operations To avoid network traffic, you should perform all bulk operations using the database server. When the number of rows affected begins to impact other users, this type of operation should be deferred to off-peak hours.