Almost every time you create components with Microsoft development products, you are creating your components as COM objects. These days, “component” is a term frequently encountered by users of Microsoft development tools. There are business components, Active Server components, and Windows Scripting components, among others. In ASP, a component is code that is removed from an ASP page or an included source file and wrapped with a COM interface. It can be any of the aforementioned component types or have various other monikers.
For a COM object to be compatible with ASP it has to be a special kind of COM object called an Automation Object, meaning it supports “automation.” Automation is a powerful extension of COM that allows the object to be leveraged by late-binding scripting languages, such as VBScript and JScript. Converting the bulk of your application code to a component architecture can increase the performance, scalability, and maintainability of your Internet application. But which of these technologies is best?
I have often wondered if a Windows Scripting component would be faster than an ASP page. I have pondered the same about compiled Active Server components. In this two-part article I will develop a small portion of an Internet Auction application using all three architectures. I will implement the database with Microsoft SQL Server 7.0. Code-wise, I will start with plain ASP pages, then migrate up into two other component architectures. In part one, I’ll implement the database and the ASP framework using Microsoft Visual InterDev. In part two I will first port the code into a Windows Scripting component, and from there to an Active Server Component built with Visual C++ 6.0 and the Active Template Library (ATL, a tool that is used to develop COM objects in C++). Finally, I’ll test the performance of the three architectures with WebLoad 3.5. The full source code for the database schema and all three components will be available for you to download. On top of that, the final functioning mini-application will be available for you to play with live on the Internet for at least 30 days from when this article is published.
Auction Sites Are Cool!
Some of the projects I worked on recently were Intranet projects for which scalability was not high on the list of requirements. I needed a scenario to develop around that if actually implemented in the real world, would require a highly efficient, scalable code architecture. I chose the Web-based Auction site. Since I enjoy buying and selling on eBay and thought it would be fun to toy with, so I decided to develop the searching functionality and the ability to open auction detail pages as the basis for this article. These two types of requests would be one of the most frequently hit on by auction enthusiasts like myself. It quickly occurred to me that I would need a database full of interesting auction items for users to search through and a full-text search engine for them to search with.
Data Modeling an Auction Site
My first draft of my auction model was nice and normalized, but it quickly became evident that perhaps a more flat approach would perform better. I also consulted Microsoft’s Commerce Server Auction Component http://www.microsoft.com/siteserver/commerce/, which includes a sample auction data model. My model (see Figure 1) is much simpler than that on a real auction site. Only the tables and fields for my two primary tasks are present.
Figure 1
Users, Auctions, and Bids, Oh My!
To load realistic test data into an application for testing, I’ve frequently used Microsoft Excel as a test data creation tool. This technique fit this project as well. I needed a wagonload of users, auctions, and bids in my database so that I could accurately benchmark real-world performance. I made heavy use of VBA Excel Macros to create users, bids, prices, and date ranges. Then I used the “MadLibs” approach to create item descriptions and titles. I randomly combined a large list of verbs, adjectives, and description phrases. The result was 200,000 very strange items up for auction. Make sure to check out the http://c56387-a.mckiny1.tx.home.com/ to see what I mean. By saving the data into tab-delimited text files, you can use the Biphase Communication Processor (BCP), a command-line tool, to load the test data into the database very quickly. BCP is documented in the Microsoft SQL Server Books Online. Example 1 shows the BCP command I used to import the tab-delimited data.
Example 1
bcp auction..AUCTION in auctions.txt /e Eauction.txt -c -E -U sa -S MYMACHINENAME
..repeat for other tables. One text file per table
The Search for a Search Engine
Now that I had auctions with descriptions and titles, I needed a way to search them. I knew I would need a more powerful and faster text-search engine than the SQL “LIKE %keyword%” technique. “LIKE” would never perform to the level required by a popular auction site, nor is it very robust or accurate from the user’s perspective. Microsoft SQL Server 7.0 includes a new full-text indexing service called Microsoft Search. This seemed like a good opportunity to put this new Microsoft tool through a few hoops, so I downloaded it at: http://www.microsoft.com/sql/bizsol/textsearch.htmand jumped right in.
Me vs. Microsoft Full-Text Search
Installing the SQL 7.0 Full-Text Search engine was easy. It’s an optional feature not selected by default during SQL 7.0 setup, so you may have to run setup again to get it installed. However, getting it to work right was not simple. To support the Full-Text Search engine, new in SQL Server 7.0, Microsoft added some new Transact SQL functions. Getting these new full-text querying functions to work with parameters in a stored procedure is not as intuitive as I expected. It turns out you need to delimit the value of your parameters with a sometimes complex combination of single and double quotes, depending on how you want the engine to treat your search phrase. You cannot just retrieve a search phrase from the user, pass it into a stored procedure, and then use the variable as a parameter to the Transact SQL functions. I found some help on microsoft.public.sqlserver.programming, and Example 2 demonstrates my results in in the stored procedure “fetchSearchResults.” This procedure handles both the simple and advanced search forms. The only other procedure is “fetchItemDetail,” which simply fetches everything needed to display an auction item’s detail page, given a primary key. There are more advanced features of the full-text support functions that I chose not to use for sake of simplicity.
Example 2 fetchSearchResults
CREATE PROCEDURE AUSP_fetchSearchResults
(
@searchphrase varchar(100), --text to look for (simple and advanced search)
@minprice money = -1, --low price range to include (advanced search form)
@maxprice money = -1, --high price range to include (advanced search form)
@orderby varchar(20) = "end_dt", --order by this field (advanced search form)
@sortdesc int = 0, --use TSQL DESC keyword if = 1 (advanced search form)
@both int = 0 --search both title and desc indexes if = 1 (simple and advanced)
)
AS
set ROWCOUNT 500 --max # of rows a search will find
declare @sql varchar(1024) --local var for sql string
set @sql = 'SELECT a.auction_seq, a.auction_type_seq, a.title, a.end_dt,
a.current_price, COUNT(bid.bid_seq) AS bid_count, TXTTBL.RANK '
if @both = 1 --search both full-text indexes, use '*'
set @sql = @sql + 'FROM auction a INNER JOIN
freetextTABLE(auction,*,''" '+@searchphrase+' "'')
AS TXTTBL ON a.auction_seq = TXTTBL.[KEY] '
else --search only title
set @sql = @sql + 'FROM auction a INNER JOIN
freetextTABLE(auction,title,''" '+@searchphrase+' "'')
AS TXTTBL ON a.auction_seq = TXTTBL.[KEY] '
...further building of SQL where clause omitted for brevity
--select @sql as sqlstring --for debugging
exec(@sql)
Although it seemed logical to me, TSQL syntax does not support using the @searchphrase parameter directly with the new full-text query functions. You have to build an SQL statement dynamically inside your procedure code, combining the search phrase the user has chosen with standard SQL (see Example 3). I could not come up with a better way, and reportedly many developers are using this approach. Example 3
This works:
set @sql = @sql + 'SELECT * FROM auction a
INNER JOIN freetextTABLE(auction,*,''"
+@searchphrase+' "'') AS TXTTBL ON
a.auction_seq = TXTTBL.[KEY] '
exec (@sql)
The following does not work, but it would be great if it did:
SELECT * FROM auction a INNER JOIN
freetextTABLE(auction, *, @searchphrase)
AS TXTTBL ON a.auction_seq = TXTTBL.[KEY]
Another delay was caused by the way the query engine sorts a result set that is ordered by the full-text search’s rank field. The new “freetexttable( )” Transact SQL function creates an additional column in the resultset, which is a numeric accuracy ranking. A higher rank number is better than a lower one. That seems logical, however, if you do not use the “descending” keyword, the resultset will be in backward order by rank. That is because the “order by” clause sorts the “rank” column as it would any other numeric column, from lowest to highest. The hits that least match your search phrase will be at the top. It is all clear to me now, but this eluded me for a while.
A final complication introduced into the development process by the Full-Text Search Service is that you are not allowed to make a schema change to any table that has been full-text indexed. You can’t even make small changes to fields and indexes that are unrelated to the full-text indexed fields, unless you remove full-text indexing from the table first. Depending on the change you make, you may have to repopulate the full-text index again. Full repopulation is a chore than renders my database server (albeit not production-quality equipment) useless for about three hours. If you had an environment with more than a million rows of full-text indexed data this process could really become a burden. The engine did perform very well, and the functions provided are very robust. In some quick tests I ran the Full-Text search engine, which beat the heck out of the old “LIKE”-clause method. Once my data was in place and my stored procedures written, it was time to get busy with some ASP.
Disconnected Recordsets
In a high-performance Web application it is very important to open and close a database connection as fast as possible. Efficient use of database connections when using Microsoft’s ActiveX Data Objects (ADO) can be achieved through disconnected recordsets (see Example 4). Microsoft Data Access Components, the key technologies that enable Universal Data Access, include the ActiveX Data Objects, among others. A disconnected recordset is an ADO Recordset object that is full of resultset data but has had it’s ActiveConnection property set to Nothing. Don’t try this with a regular recordset. You have to use a special CursorLocation value to achieve thisExample 4
Dim oConn
Dim oRS
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=Article.Auction;"
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient ‘NOTE: This is important for Disconnected Recordsets
oRS.Open "AUSP_fetchAuctionDetail " & lAuctionSeq, oConn, adOpenStatic, AdLockReadOnly
oRS.ActiveConnection = Nothing
oConn.Close
Set oConn = Nothing
The first thing my two ASP pages do when processing a search request or an individual auction detail request is retrieve the data and disconnect the recordset immediately. There is a VBScript function for each task, fetchAuctionDetail() and fetchSearchResults(). These functions share the name of their respective stored procedure counterparts. Both functions take a reference to a recordset object and then fill that recordset object with data. As soon as the data is available, the Recordset is disconnected. By the time the first line of HTML text is written through the Response object, the database connection is closed. The only task left is formatting that data into a usable Web page. While the Web server is performing the task of formatting the page with the data, the database server has been freed from any more duty. It is now available to handle requests from some other client. This is efficient and is highly scalable. It could be thought of as a “Get iIn and Get out as fast as possible” approach to database access. I used disconnected recordsets in all three architectures (the ASP, Windows Scripting Component, and Active Server Component built in C++). You could speed up the code even more by explicitly defining your stored procedure parameters with a cCommand Object.
For more information on enhancing your ASP performance see this article in the October issue of MIND, http://www.microsoft.com/mind/1099/inthisissue1099.htm.
<% Tag Time %>
Although I am a big fan of eBay’s site, I’m not a fan of their visual design. As I re-created an eBay-like search page, search results page, and auction item detail page, I made a few visual enhancements. I slapped on the Verdana font and got rid of the hospital-gown-green table backgrounds. I also coded some of the necessary minor functions that are needed by the detail page, such as changing the presentation of some of the data depending on the value (see Figure 2). An example of this is the “time left” string. This has several different looks, depending on how much time is left until the auction ends.
Figure 2
The Search Page
There are many different ways to search the eBay database. I re-created three of them - the simple search, the advanced search, and the item number search. The item number search just redirects you to the item detail page, item.asp. The search page is completely static and remains unchanged for all three versions. The hard work of providing results is handled by results.asp. The code structure in results.asp is much like item.asp, except the output HTML is different. The finished working demo returns up to 500 search hits in pages of 50-items each (see Figure 3).
Figure 3
Include Dude
I love server-side <!-- #include --> statements. They allow you to develop modular, re-usable, maintainable ASP code, instead of behemoth, memory-hogging, difficult-to-maintain pages. Applying the object orientation, abstraction, and reusability principles of more robust programming languages to the ASP development platform will take you a long way towards a successful application, even if you are just sticking with plain ASP. In my first implementation of this application, I created several functions in an included ASP file. Developing your code with reusable includes also does something else that is very important for this article and for anyone who wants to leave their future options open. It makes converting to any of the Component architectures extremely simple. Your code will already be structured like it is accessing the code in a reusable object. However, it will be without one layer of abstraction, the COM interface your Windows Scripting Component will expose. Figure 4 depicts the Part I code architecture of my two ASP pages and their included library of functions.
Figure 4
To Be Continued …
In the second half of the article, I will convert the pure ASP and include file architecture to a Windows Scripting Component and also an Active Server Component written in C++. Then I will load test all three.
Jason W. Taylor (MCSD, MCP) is president of Dallas-based Code Authority, Inc. Code Authority specializes in architecting and developing Windows, Internet and Intranet applications on the Microsoft Windows 2000 and .NET platform. Jason can be reached at jwtaylor@codeauthority.com.
Special Thanks
Special thanks to Beckett Publications for helping to test the code and also to Keith Barber, Jaime Amaya, and Eric Couch for their assistance.
Tool Parts provide an interface for Web Part properties well beyond the capabilities of the default property pane. In this article Gayan Peiris shows how to customize Web Parts with custom Tool Parts. [Read This Article][Top]
This article demonstrates how to create a reusable component in ASP.NET 2.0 and then consume it from an ASP.NET page. Also learn how the ObjectDataSource control can be used to directly bind the output of an object to the controls in an ASP.NET page and how precompilation can be used to increase the performance of the Web application and catch compilation errors. [Read This Article][Top]
Browser Helper Objects (BHOs) are COM components that communicate with Internet Explorer to enrich the browsing experience. Michele Leroux Bustamante returns to the world of COM to show you how to build a managed BHO with the help of the .NET Framework's COM interoperability features. [Read This Article][Top]
In addition to creating custom Web Parts for SharePoint Portal Server, developers can actually create their own custom properties to further enhance Web Part appearance and behavior. Gayan Peiris explains the process and provides all the necessary code. [Read This Article][Top]
Accessing shared resources is a challenge for many ASP.NET developers. Tony Arslan explains how a simple serviced component can solve this infamous problem. [Read This Article][Top]
Using callbacks and function pointers in VB can be risky and complicated. Ben Garcia explains his work-around for the function pointer issue he encountered while creating the VB version of his SNMP component. [Read This Article][Top]
In part two of this intriguing article series, Ben Garcia shows how to build an updated and improved SNMP component in VC++ AND VB, and he briefly explains why limitations in VB make VC++ a better language for developing this type of application. [Read This Article][Top]
Ben Garcia sheds some light on the Simple Network Management Protocol
(SNMP). First he provides a history of SNMP, then he dives right into its
architecture. Finally, he shows how to build a COM component that
communicates with SNMP-enabled devices. [Read This Article][Top]
Paul Apostolos begins his series on using Web services and the MSComm32.OCX
component to access caller id information from a Web page. In part 1, learn how to write the Visual Basic program that runs on the server and updates a database with incoming callers.
[Read This Article][Top]
Doug Dean explains different methods of retrieving and manipulating data from a database in a VB DLL so that it is ready to be rendered in a browser. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
