Data Access and Transactions |
Technology, like MDAC, comes with a price.
Suppose you’re creating a site that publishes bus schedules for more than a hundred routes. The “static” solution might be an index page—perhaps with an HTML form—that allows the user to select and view route-specific pages. The “dynamic” solution might use a query page to look up each bus schedule as it is requested, and return it on a customized, dynamic page.
Both approaches offer the same solution, but the “static” one offers better performance, for two reasons:
Consider how your data will typically be used. Often a static approach is best for static data (such as bus schedules), and a dynamic one is generally best for dynamic data (such as stock quotes). However, the best solution is to provide a controlled mix of static and dynamic pages, as your users require them, that your site can support. For example, if people need infrequent access to large amounts of data, the best solution may be the dynamic approach: a query page. But if they can read a relatively short list of articles while online, it’s better to generate the page once and display a static listing.
Once you’ve determined that the dynamic approach is necessary, choosing a data access method is based on what your application requires in the following areas:
The emphasis you place on each factor should be motivated by both the current and future needs of your application. Given the robust growth patterns of the Web in recent years, application performance and scalability should be your foremost goals. It is important to note, however, that throughput numbers should in no way dictate your choice of technology. For example, although the cost of developing an ADO application can be significantly less than with ODBC, the number of transactions ADO can process per second (TPS) is significantly lower than what ODBC can achieve. If both ADO and ODBC exceed your target TPS, your decision on which technology to use should be based on factors other than performance.
Table 7.1 compares the TPS results of similar tests performed with each data access technology. For these tests, Component Services (implemented in Microsoft® Visual C++®) and connection pooling were used. The SQL Server database was scaled to 800 TPS, with 384 megabytes (MB) RAM, and 4 percent procedure cache.
Table 7.1 TPS Per Number of Threads by MDAC Technology
1 | 2 | 5 | 10 | 20 | 50 | |
ODBC | 66.37 | 146.28 | 350.46 | 626.76 | 900.24 | 859.91 |
OLE DB | 67.30 | 141.92 | 326.19 | 590.57 | 794.91 | 715.78 |
OLE DB 2.0 | 61.73 | 126.93 | 297.29 | 506.75 | 575.35 | 526.61 |
ADO 2.0 | 51.24 | 108.12 | 240.91 | 377.30 | 361.26 | 310.34 |
The results of using relational data are striking (the comparison is not valid if the data is not relational). The ODBC component produced the highest throughput (as much as 277 percent higher than ADO using 50 threads). All data access technologies increased in throughput up to approximately 20 threads, after which performance dropped off.
These test results make the cost of data access very clear. If you use data access frivolously, you will be plagued with excessive delays and bottlenecks as pages are generated over and over again, and as transactions are processed for thousands of concurrent requests. By using the right data access methodology, and judiciously choosing when to generate dynamic pages, you can enhance and complement the static elements of your site.