Some Practical Database Access Tips

The following is a brief summary of some common strategies that will provide high-performance, scalable data access for your enterprise application.

• Design your data carefully   The design of your database is usually a bigger performance factor than the design of your application.

For More Information   For more information on data modeling, designing data structures, and determining data attributes, see Designing Enterprise Data in Chapter 4 of this book.

• Understand cursors and how to use them   Your application should use the most efficient cursor type for your application's data access requirements. To gain the full benefit from cursors, you must understand cursor types and their configuration options.

For More Information   For more information using cursors in your application, see Choosing and Managing Cursors in Chapter 7 of this book.

• Retrieve only the required data   Retrieving large result sets for browsing on the client adds CPU and network load. It's generally better to minimize network traffic by specifying only the required fields and rows.
• Adjust network packet size   You should optimize data access to remote servers by adjusting the size of the network packet. Configuring the packet size provides a choice between responsiveness and overall throughput. Larger packets provide less packet overhead and better throughput. Smaller packets are more responsive.

For More Information   For more information on configuring SQL Server packet sizes, search online for "Setting the Configuration Options" and "Network Tuning" in MSDN Library Visual Studio 6.0.

• Provide for cancellation of a query in progress   Your application should allow cancellation of a query in progress. No application should force the user to reboot the client computer to cancel a slow query.
• Set a query timeout   Do not allow queries to run indefinitely.
• Use transactions   In a multiuser environment, wrap updates within a transaction, and always commit or roll back a transaction as soon as possible.
• Use connection pooling   Recycling the connections provides better performance and reduces the number of idle connections.

For More Information   For more information on the benefits of connection pooling, see Using Connection Pooling in this chapter.

• Use stored procedures   Stored procedures are already parsed, normalized, and compiled, and they run very quickly.
• Put data service components on the database computer   Deploying data service components on the same computer as the database engine can greatly reduce network traffic and improve overall data throughput.
• Keep lookup tables locally   There are usually instances in which read-only data is accessed fairly often by your application. By keeping that data locally, lookups become extremely fast.