|  | | | Chapter 11 - Performance | | | byJohn Kauffman, Fabio Claudio Ferracchiatiet al.? | | | Wrox Press ?2002 | | | | | Usually, a summary will provide some closure on the disparate topics that have been presented throughout a chapter. This being a chapter on performance, however, a different approach will be taken: we'll list a series of good practices that you're encouraged to follow. The following suggestions could readily lead to improved performance with respect to establishing database connections: -
The .NET data providers for SQL Server and OLE DB implement connection pools automatically, so developers need not attempt to enable what is already enabled. -
Create connections in a single location, so that superfluous connection pools are not created by mistake in other parts of your application. -
Do not change innocuous connection string attributes, or you risk creating superfluous connection pools (see previous bullet). -
Use PerfMon and the .NET CLR Data performance counter to ensure that connection pools are behaving as anticipated, and to ensure that appropriate maximum and minimum values are specified for each connection pool. Consider the following suggestions when migrating legacy code that uses ADO to run under .NET: -
Recognize that migrating legacy ADO code to .NET will impact performance. -
Where possible, upgrade ADO to use ADO.NET. Use DataSet instances: -
Where caching is convenient (which might not be the case for a stateless application). -
Where memory is sufficient to handle the size of the cached data. -
In order to enable a client or other application tiers to manipulate data locally (especially since clients are not stateless). -
When the disconnected data must still maintain tables and relationships between tables. -
When data must be modified. -
When data can be shared as XML (imported to a DataSet, exported from a DataSet). Use data reader instances where: -
Data access is read-only, fast, and forward-only. -
Data access may only require accessing part of a dataset, or where only a row of data at a time is required for processing. When accessing data using either a DataSet or a data reader: -
Use ordinal values to look up specific data within DataSets and data readers. -
Enumerations provide a simple way to look up by ordinal, and can easily fall back to lookup by name. There is no perfect way to guarantee good performance. Probably the most important rule is the good old 80/20 - eighty percent of the work is done by twenty percent of the code. Don't spend all of your time improving the performance of a large action that happens only once; instead, focus on small things that take place numerous times, and have a greater overall impact on performance. Regardless of how you approach performance, ADO.NET has simplified the process by the simple expedient of preventing you from stepping in a variety of hazards. Connections pools, performance measurement counters, no cursors, and data access patterns are gift wrapped and handed to the developer. The result should be faster, more reliable data access code. |
