Database Portability
One of the key requirements for this version of the Microsoft .NET Pet Shop was to provide an implementation of the application that supported both Oracle and SQL Server databases. When designing the database access mechanism for the application, we had a choice as to which database providers we should use; we could either use the generic OLE-DB managed provider or the database-specific, performance-optimized .NET managed providers, such as the SQL Server and Oracle managed providers provided with the .NET Framework 1.1. One of the key requirements for the application was to create a high-performance solution, so we chose to build the application using the database-native .NET managed providers. For an analysis of the difference in performance between the managed providers and the generic OLE-DB providers, readers should refer to Using .NET Framework Data Provider for Oracle to Improve .NET Application Performance, which shows that the vendor-specific providers can perform two to three times better than the equivalent OLE-DB provider. The tradeoff that we made when choosing to go with a database-specific access class was that we would need to write a separate data access layer for each database platform we wanted to support and hence the application would contain more code. While the two data access layers share much common code, each is clearly targeted for use with a specific database (Oracle or SQL Server 2000).
In order to simplify the use of the database access classe,s we elected to use the Factory Design Pattern as outlined by the GoF, with reflection being used to dynamically load the correct data access objects at runtime. The factory is implemented as follows: a C# interface is created with a method declared for each method that must be exposed by the database access classes. For each database that we want to support, we create a concrete class that implements the database specific code to perform each of the operations in the interface or "contract." To support the runtime determination of which concrete class to load, we create a third class which is the factory itself and reads a value from configuration file to determine which assembly to load using reflection. With the reflection namespace in .NET, you can load a specific assembly and create an instance of an object using that assembly. To make the application more secure and provide improved support for versioning, we can add "evidence" about the assembly file to load in the application configuration file, in this case, web.config. This means that the .NET Framework will only load the assembly that we have signed during compilation and has the correct version number. Figure 10 shows how the business logic, factory, and databases access classes interact with each other. The key advantage of the solution built is that the database access class can be compiled after the business logic classes as long as the data access class implements the IDAL interfaces. This means that should we want to create a DB2 version of the application, we do not need to change the business logic tier (or UI tier). The steps to create a DB2 compatible version are:
1. Create the database access classes for DB2 which implement the IDAL interfaces.
2. Compile the DB2 access classes into an assembly.
3. Test and deploy the new data assembly to a running server.
4. Change the configuration file to point to the new database access classes.
No changes or recompiles need to be performed on the business logic components.

Figure 10. DAL factory implementation in .NET Pet Shop
Stored procedures
Normally we would recommend to customers that they use stored procedures to access tables in the database. There are several reasons for this:
• Stored procedures provide a clean mechanism to encapsulate queries.
• Changing a query can be done without changing the data access code.
• A DBA can easily see what SQL statements are being executed.
• Stored procedures are typically more secure, and it is easier to control database access.
• By using stored procedures you can avoid round trips to the client by issuing more than one request in the stored procedure.
• Stored procedures usually offer the best performance compared to middle-tier-generated SQL.
• Stored procedures offer an excellent way to encapsulate XML queries and XML input parameters.
The disadvantage of stored procedures is they tend to be proprietary and are not portable across platforms.
However, to make the best use of the financial investment that you have made in your database software and hardware, developers will tend to optimize the SQL used in their applications for their specific database engine regardless of whether the SQL is in a stored procedure or generated in the middle tier. A good example of this is generating unique numbers or identity numbers, as all databases support their own particular mechanisms for doing this, and so the SQL used to generate the unique number tends to be specific to the database being used. There are always alternatives, but they do not perform as fast as the proprietary solutions.
With the .NET Pet Shop we took the conscious decision not to use stored procedures in the application as this might be seen to be an unfair advantage for the .NET solution in the Middleware Benchmark. In reality the difference in performance is small because the application is relatively simple and most of the SQL statement execution plans are cached in the database. However, for purposes of the Middleware Benchmark specification, which disallows the use of stored procedures even to wrap simple SQL statements, the .NET Pet Shop 3.0 uses no stored procedures.
Caching
One of most effective ways to improve the performance of a database-driven applications is to eliminate accessing the database for every request. ASP.NET offers a variety of caching mechanisms that boost performance in most applications. The two main ways to use caching in ASP.NET are output caching and data caching.
.NET Caching Options
Page-level output caching takes the response from an ASP.NET Web page and stores the full page in a cache. The page cache is designed to work between the Web tier and the middle tier and caches the results/data of middle-tier methods, or the results of database calls in the case of two-tier apps. The first .NET Pet Shop distribution was offered in a page-level output-cached version and a noncached version. Version 3 only supports data caching but can easily be modified to support output caching. With Windows Server 2003 and IIS 6.0, certain output cached pages (those with VaryByParm="none" and the directive to Cache 'Anywhere') can also be kernel-level cached, with even faster access from Internet clients. Regardless, any output-cached page (kernel or nonkernel cached) is served extremely quickly by the Windows application server with much less resource (CPU) consumption, since no processing is actually occurring to recreate the page.
ASP.NET Output Caching
The original .NET Pet Shop application, version 1.5, also used a variation on page-level output caching, partial page caching or fragment caching to cache different regions of page. For instance, the header information that appears on the top of every page is cached. However, the header information is dependent on the user signing in (so two different versions of the page have to be cached). ASP.NET easily allows this by using the VaryByCustom attribute on the 'OutputCache' directive. Using VaryByCustom requires overriding the GetVaryByCustomString method to get a custom cache for the header.
ASP.NET Data and Object Caching
The object cache (Cache API) allows you to store the results of a method call or database query in the internal caching engine with the .NET Framework. Since you have gone further through more of the application pipeline, the data cache may not offer the same performance boost that output caching yields, since HTML pages still must be dynamically constructed on each request. However, it does offer a good compromise between fully dynamic pages and reducing the load on the database by storing nonvolatile data in the middle tier. For example, you may want to display the same piece of data differently in two Web pages, or use the cached objects or data differently in various pages of the same application.
ASP.NET Cache Monitoring
There are several ways to monitor what is happening in the ASP.NET caching system. The foremost method is to use Perfmon but you can also use a SQL Server trace to check when database access is occurring or not occurring as the case may be. To monitor the cache in Perfmon, select the Output Cache Entries and Cache API Entries counters under the ASP.NET Application performance object and add them to the Perform graph. It is also possible to monitor the turnover rate and the hit rate within Perfmon to detect whether the item is being used in the cache.