Professional SQL Server™ 2005 Programming

23.3. Client vs. Server-Side Processing

Where you decide to "do the work" can have a very serious impact — for better or worse — on overall system performance.

When client/server computing first came along, the assumption was that you would get more/faster/cheaper by "distributing" the computing. For some tasks, this is true. For others though, you lose more than you gain.

Here's a quick review of some preferences as to which end to do things:

Static cursors	Usually much better on the client. Since the data isn't going to change, you want to package it up and send it all to the client in one pass — thus limiting roundtrips and network impact. The obvious exception is if the cursor is generated for the sole purpose of modifying other records. In such a case, you should try and do the entire process at the server-side (most likely in the form of a stored procedure) — again eliminating roundtrips.
Forward-only, read-only cursors	Client-side again. The ODBC libraries can take special advantage of the FAST_FORWARD cursor type to gain maximum performance. Just let the server spew the records into the client cursor, and then move on with life.
HOLDLOCK situations	Most transactioning works much better on the server than on the client.
Processes that require working tables	This is another of those situations where you want to try to have the finished product created before you attempt to move records to the client. If you keep all of the data server-side until it is really ready to be used, you ...