Optimizing Application Performance Using Efficient Data Retrieval
One of the capabilities of the SQL language is its ability to filter data at the server so that only the minimum data required is returned to the client. Using these facilities minimizes expensive network traffic between the server and client. This means that WHERE clauses must be restrictive enough to retrieve only the data that is required by the application.
It is always more efficient to filter data at the server than to send it to the client and filter it in the application. This also applies to columns requested from the server. An application that issues a SELECT * FROM... statement requires the server to return all column data to the client, whether or not the client application has bound these columns for use in program variables. Selecting only the necessary columns by name avoids unnecessary network traffic. This also makes your application more robust in the event of table definition changes, because newly added columns are not returned to the client application.
Performance also depends on how your application requests a result set from the server. In an application using Open Database Connectivity (ODBC), statement options set prior to executing a query determine how the application requests a result set from the server. When you leave the statement options at default values, Microsoft® SQL Server™ 2000 sends the result set the most efficient way.
SQL Server assumes that your application will fetch all the rows from a default result set immediately. Therefore, your application must buffer any rows that are not used immediately but may be needed later. This buffering requirement makes it especially important for you to specify (by using Transact-SQL) only the data you need.
It may seem economical to request a default result set and fetch rows only as your application logic or your application user needs them, but this is false economy. Unfetched rows from a default result set can tie up your connection to the server, blocking other work in the same transaction. Additionally, unfetched rows from a default result set can cause SQL Server to hold locks at the server, possibly preventing other users from updating. This concurrency problem may not show up in small-scale testing, but it can appear later when the application is deployed. Therefore, immediately fetch all rows from a default result set. For more information, see Understanding and Avoiding Blocking.
Some applications cannot buffer all the data they request from the server. For example, an application that queries a large table and allows the user to specify the selection criteria may return no rows or millions of rows. The user is unlikely to want to see millions of rows. Instead, the user is more likely to reexecute the query with narrower selection criteria. In this case, fetching and buffering millions of rows only to have them thrown away by the user wastes time and resources.
For these applications, SQL Server offers server cursors that allow an application to fetch a small subset or block of rows from an arbitrarily large result set. If the user wants to see other records from the same result set, a server cursor allows the application to fetch any other block of rows from the result set, including the next n rows, the previous n rows, or n rows starting at a certain row number in the result set. SQL Server does the work to fulfill each block fetch request only as needed, and SQL Server does not normally hold locks between block fetches on server cursors.
Server cursors also allow an application to do a positioned update or delete of a fetched row without having to figure out the source table and primary key of the row. If the row data changes between the time it is fetched and the time the update is requested, SQL Server detects the problem and prevents a lost update.
However, the features of server cursors come at a cost. If all the results from a given query are going to be used in your application, a server cursor is always going to be more expensive than a default result set. A default result set always requires only one roundtrip between client and server, whereas each call to fetch a block of rows from a server cursor results in a roundtrip. Moreover, server cursors consume resources on the server, and there are restrictions on the SELECT statements that can be used with some types of cursor. For example, KEYSET cursors are restricted to using tables with unique indexes only, while KEYSET and STATIC cursors make heavy use of temporary storage at the server. For these reasons, only use server cursors when your application needs their features. If a particular task requests a single row by primary key, use a default result set. If another task requires an unpredictably large or updatable result set, use a server cursor and fetch rows in reasonably sized blocks (for example, one screen of rows at a time). Additionally, where possible, make use of Fast Forward-only cursors with auto-fetch. These cursors can be used to retrieve small result sets with only one roundtrip between the client and server, similar to a default result set. For more information, see Fast Forward-only Cursors.