To reduce network traffic and improve performance, you can reduce the size of any data being retrieved to some manageable limit by using a database-specific command. For example, an Oracle data provider might let you limit the number of bytes of data the connection uses to fetch multiple rows. A Sybase data provider might let you limit the number of bytes of data that can be returned from a single IMAGE column in a result set. For example, with Microsoft SQL Server and Sybase ASE, you can issue Set TEXTSIZE n on any connection, where n sets the maximum number of bytes that will ever be returned to you from any TEXT or IMAGE column.
In addition, be careful to return only the rows you need. If you return five columns when you only need two columns, performance is decreased, especially if the unnecessary rows include long data.
Especially when using a DataSet, be sure to use a WHERE clause with every SELECT statement to limit the amount of data that will be retrieved. Even when you use a WHERE clause, a SELECT statement that does not adequately restrict the request could return hundreds of rows of data. For example, if you want the complete row of data from the EMPLOYEE table for each manger hired in recent years, you might be tempted to issue the following statement and then, in your application code, filter out the rows who are not managers:
SELECT * FROM EMPLOYEE WHERE hiredate > 2000
However, suppose the EMPLOYEE table contains a PHOTOGRAPH column. Retrieving all the extra rows could be extremely expensive. Let the database filter them for you and avoid having all the extra data that you don't need sent across the network. A better request further limits the data returned and improves performance:
SELECT * FROM EMPLOYEE WHERE hiredate > 2003 AND job_title='Manager'
