Now that several different data processing implementation techniques have been compared, the next step is to use these methods to access SQL Server data effectively. The proper implementation of these concepts eliminates application overhead.
Some third-party application development controls and objects provide properties and methods for accessing and retrieving data. These objects and controls expedite the development process by creating a simple interface that allows you to access tables while minimizing or eliminating the need to use Transact-SQL. This abstraction layer can put you at a disadvantage, however, because you will reduce your ability to tune the data-access operations.
By using Transact-SQL statements and stored procedures, you have direct access to the tables involved with your application. This allows you to determine how, and in what order, the data operations are performed. By accessing the tables directly, your application can issue Transact-SQL statements that are tuned for optimal performance.
In addition, stored procedures written in Transact-SQL can be used to encapsulate and standardize your data access.
You may be tempted to use server cursors to implement data seek operations at the client to emulate the ISAM-like processing techniques used in the Btrieve environment. However, using this methodology to implement data retrieval greatly diminishes the performance advantages of using SQL Server.
SQL Server incorporates an intelligent, cost-based query optimizer that quickly determines the best access plan for executing a Transact-SQL statement. The query optimizer is invoked with every Transact-SQL statement that is sent to the database server. Its goal is to minimize execution time, which generally minimizes physical data accesses within the database. The query optimizer chooses from among the indexes you create on the tables. For more information about indexes, see “Using Effective Indexing Techniques,” later in this chapter.
Use table joins on the server rather than processing nested iterations of result set data on the client to reduce the amount of processing required at the client and the number of round-trips between the client and the server to process a result set.