Database Reference
In-Depth Information
Application Design
Last, but certainly not least, design choices can have a significant impact on application response time.
Certain coding techniques can negatively affect performance, such as excessive roundtrips. Although
this may not be noticeable when you're running the application against a local database, it may turn out
to be unacceptable when you're running against SQL Azure.
The following coding choices may impact your application's performance:
•
Chatty design
. As previously mentioned, a chatty application uses excessive
roundtrips to the database and creates a significant slowdown. An example of a
chatty design includes creating a programmatic loop that makes a call to a
database to execute a SQL statement over and over again.
•
Normalization
. It's widely accepted that although a highly normalized database
reduces data duplication, it also generally decreases performance due to the
number of
JOIN
s that must be included. As a result, excessive normalization can
become a performance issue.
•
Connection release
. Generally speaking, you should open a database connection
as late as possible and close it explicitly as soon as possible. Doing so improves
your chances of reusing a database connection from the pool.
Shared database account
. Because SQL Azure requires a database login, you need
to use a shared database account to retrieve data instead of using a per-user login.
Using a per-user login prohibits the use of connection pooling and can degrade
performance significantly, or even render your database unusable due to
throttling.
There are many other application design considerations, but the ones listed here apply the most to
programming against SQL Azure. For more information, read the following chapter from Microsoft's
Patterns and Practices:
http://msdn.microsoft.com/en-us/library/ff647768.aspx
.
•
Conclusion
This chapter provided an overview of some of the most important tuning techniques that are available to
help you address SQL Azure performance issues. As you've seen, troubleshooting and tuning statements
can be complex and require various tools and methods to obtain the desired outcome. You saw a few
dynamic management views and execution plans, took a quick tour of indexing, and briefly touched on
the statistics provided by the ADO.NET library. You also learned about some design considerations that
can affect application performance.
Many more tuning options are available in SQL Azure that couldn't fit in this chapter. You can
discover additional techniques and concepts on Microsoft's web site at
http://msdn.microsoft.com/en-
us/library/ms190610(v=SQL.90).aspx
.
