Professional SQL Server™ 2005 Programming

B.2. General Performance Considerations

In many places in this book, we talk about things you can to on the server side to help yoursystem perform well. Keep in mind, however, that there are a lot of places your system can have performance issues introduced — not just in your server-side components. The manner and frequency of your connections can play a significant roll in performance. Here are some key issues to think about.

B.2.1. Connection Management

The opening of a connection can be one of the most expensive parts of any query. Exactly howlong it takes will vary depending on a wide variety of issues, but, even in the best of scenarios, a connection can take several times as long to open as the actual execution will take for most simple queries.

So — how to manage this? Fortunately, utilizing some basics will eliminate most of the issue:

• Utilize "connection pooling" if possible — Connection pooling is a concept that is built into most modern connectivity methods and is usually turned on by default. Under connection pooling, the underlying connectivity model you're using (ODBC, OLE-DB, SQL Native Client) "pools" the connections on your client. This means that when you close a connection in your code, the connection is not actually immediately closed to the server — instead, the connection is put into a pool for possible reuse. When another block of code (even from a different process on your client) makes a connection with the same connection properties (server, username, ...