ASP.NET, SQL Server, WPF, C#, VB.NET

My erstwhile, energetic eggheadcafe.com co-developer partner Robbe Morris has had his head buried in his C# books ever since I lent him my Visual Studio .NET CD's (I did finally get them back, thank you!). This guy is as stubborn as an ox. He can literally sit heads down at the PC, completely oblivious to fire sirens, beautiful women, and whatever other distractions life may bring, in order to master a new programming concept. I have known him to forgo all food and water (except for an occasional sip of Bourbon) while pursuing the solution to one of life's programming challenges.

Well, it's paying off. After a disappointing hibernation, Robbe has cracked the pen and produced an excellent and well researched article about ADO .NET performance for our site. He even found time to throw in a couple of classic ASP (ADO) comparison tests. The results are interesting - so much so, that I would suggest that if you haven't read his article yet, you do so http://www.eggheadcafe.com/articles/20010919.asp before reading this one.

Not to be daunted, and realizing the recursive benefits of a little friendly professional rivalry, I determined that I should produce something useful in this regard myself.   While Robbe's article covers performance comparisons in the important areas of the various ways of getting ADO .NET data out of the database, I thought I should focus my efforts on a gnawing problem that I believe all web developers who are either considering or beginning with ASP .NET (in other words, all of us) are consumed by on a daily basis: If I use ADO.NET and ASP.NET instead of classic ASP and ADO, what are the performance implications under load?

I determined that I needed to find some "common denominator" that represented in its innermost essence, the quintessential "thing" that a developer does on the web. I consulted the I Ching, and it came to me in a flash: Get some data out of a database, and display it in an HTML Table in a web page!. After all isn't that about 90% of what we do all the time, or some variation, more or less? Well, before you could say "foreach xy in z" I had whipped up an ASP.NET ASPX page and a Classic ASP page that were functionally equivalent. I put them both through Application Center test with one and then with two threads, for a one - minute test with a ten second warm-up to let connection objects get cached, pages to compile, etc.

In my Classic ASP page, "RecordSetToTable", I simply isssued code like this, in VBScript:

strConn= "Provider=SQLOLEDB.1;Data Source=(local);User Id=sa;password=;Initial Catalog=Northwind"
strSQL = "SELECT * FROM CUSTOMERS"
dim rs1
set rs1=Server.CreateObject("ADODB.Recordset")
rs1.Open strSQL, strConn
Response.Write RecordsetToTable(rs1,"Results")
rs1.Close
set rs1 = Nothing

I then immediately passed the recordset into a function that builds an HTML table out of it (the code for both these pages is in the download zip below). Pretty simple stuff.

For my ASP.NET page "DataSetRenderDataGrid", I used this code:

string sql = "SELECT * FROM Customers";
string connStr = "server=localhost;database=Northwind;uid=sa;pwd=;";
SqlConnection dataConn = new SqlConnection(connStr);
dataConn.Open();
DataSet ds = new DataSet("Northwind");
SqlDataAdapter dsc = new SqlDataAdapter(sql, dataConn);
dsc.Fill(ds,"Customers");
Customers.DataSource = ds.Tables["Customers"];
Page.DataBind();
dataconn.Close();

This line: Customers.DataSource = ds.Tables["Customers"]; is what populates the following ASP.NET Datagrid control:

<asp:DataGrid runat="server" id="Customers"
Width="100%" Gridlines="Horizontal"
BorderWidth="1" BorderColor="Black"
HeaderStyle-Font-Name="Verdana" HeaderStyle-Font-Size="9pt"
HeaderStyle-BackColor="#FF66CC" HeaderStyle-ForeColor="White"
HeaderStyle-Font-Bold="True"
ItemStyle-Font-Name="Verdana" ItemStyle-Font-Size="8pt"
AlternatingItemStyle-BackColor="#CCFF66" />

By the way, if you haven't started using the DataGrid, the above which is basically a single line of code, binds your DataSet and creates a beautifully custom formatted HTML table with alternating color rows, and that's just the tip of the iceberg as far as what this control can do.

Now when you fire up Application Center test, it's very similar to its predecessor, Homer. (homer.rte.microsoft.com). Everything it does, however is saved as XML. Here is an actual screen shot of the graph from my first run:

What you are looking at above is a default graph (which actually populates in real time while the test is running) that shows Requests Per Second over time. Obviously, the more requests per second, the faster your page is being served, and the happier you should be. As you can see, on my P-III running windows 2000 Advanced Server with some 320 MB RAM, the ASPX dataset -to - DataGrid page clocked in at about 22 RPS. Not exactly stellar performance, but at least -- a start.

I could go on for another two or three pages and start sounding like Consumer Reports Testing Labs here, so let's get to the bottom line: There appeared to be a slight performance hit using ASP .NET Vs Classic ASP.   However, I was not satisfied with Application Center Test. It's still Beta, and on several machines, I have gotten strange GPF's when attempting to run a test with more than 3 threads. So I switched back to HOMER and did some additional testing. While I was preparing Homer for my new tests, it occurred to me that the process of having the asp:datagrid control bind and render the HTML table may have been using a lot of resources. So, I rewrote the ASPX page in two additional versions:

1) DataSetMakeTable: This page gets the data into a DataSet same as the original. However instead of binding to a control, I chose to iterate through the column and rows collections, using Response.Write 's to output the table, like this:

Response.Write( "<TABLE style=\"font-size:11;font-family:Verdana\">");
string it;
Response.Write("<TR>");
foreach( DataColumn dc in ds.Tables[0].Columns )
{
Response.Write("<TD BGCOLOR=#FFCC66><B>"+dc.ColumnName+"</B></TD>" );
}
Response.Write("</TR>");
for(int d=0;d<ds.Tables[0].Rows.Count;d++)
{
Response.Write("<TR>");
foreach( DataColumn dc in ds.Tables[0].Columns )
{
it=dc.ColumnName;
Response.Write("<TD BGCOLOR=#FF66CC>" + ds.Tables[0].Rows[d][it].ToString()+"</TD>" );
}
Response.Write("</TR>");
}
Response.Write("</TABLE>");
}

2) DataSetMaketableSB: My final example tested another hypothesis - instead of using multiple Response.Write's to output the HTML table, I iterated the collections and just kept stuffing everything into a StringBuilder, finally outputting the entire result HTML in a single Response.Write:

StringBuilder SB = new StringBuilder("");
SB.Append("<TABLE style=\"font-size:11;font-family:Verdana\">");
string it;
SB.Append("<TR>");
foreach( DataColumn dc in ds.Tables[0].Columns )
{
SB.Append("<TD BGCOLOR=#FFCC66><B>"+dc.ColumnName+"</B></TD>" );
}
SB.Append("</TR>");
for(int d=0;d<ds.Tables[0].Rows.Count;d++)
{
SB.Append("<TR>");
foreach( DataColumn dc in ds.Tables[0].Columns )
{
it=dc.ColumnName;
SB.Append("<TD BGCOLOR=#FF66CC>" + ds.Tables[0].Rows[d][it].ToString()+"</TD>" );
}
SB.Append("</TR>");
}
SB.Append("</TABLE>");
Response.Write(SB.ToString());
}

I ran my tests with Homer on the client machine - a P-166 with 96 MB RAM running Windows XP Pro. The Server was a P-III 667 with 320 MB RAM running Windows 2000 Advance Server with IIS throttled up for maximum capacity and all debugging turned off. In addition, I ran my tests with 10 threads and no random delay, and a second series of the same tests with 100 threads and no random delay. The final results, I believe, are quite revealing. Examine the table below and see if you agree with my conclusions:

THE SUMMARY:

My short analysis is certainly not "Lab Quality", but I do believe it is not flawed either. There appears to be some performance hit using ASP .NET vs Classic ASP especially when using the asp:DataGrid control. Since this is still considered Beta software and I know for a fact that there is a lot of optimization going on at Microsoft in preparation for the final release of the .NET Platform and Visual Studio .NET, I would suspect that this gap will get smaller by the time this becomes production code. However, ASP.NET held up remarkably well under a 10 - thread load when using the Response.Write method of "manually" building a table with the returned data. And, under a heavier 100 - thread load, while Classic ASP appears to be the winner at 1626 hits in a minute with no http errors, the ASP.NET DataSetMakeTable and ASP.NET DataSetMaketableSB (StringBuilder) methods both held up quite well under up to a 100 thread load with no delays built in. Also, when I ran a one minute test with a single thread, the RecordsetToTable( Classic ASP) page clocked 854 hits for 14.21 RPS, and the DataSetMakeTable (ASP.NET) page clocked 714 hits for 12.08 RPS - comparable performance. There are also some optimizations that can be done for ASPX pages and caching that I did not use. For example, with output caching turned on, the DataSetMakeTable page turned in a winning 1110 hits or 18.41 RPS vs. the Classic ASP at 854 hits / 14.21 RPS. I would strongly advise .NET developers to read up on and carefully examine the flexible configuration options available for page caching under the .NET platform. I no longer have the fear that ASP.NET cannot compete with classic ASP in terms of speed and scalability --for software that hasn't yet had the Beta stuff removed from it and hasn't been optimized as release code, it competes very well. My analysis? I'm totally sold on .NET. Not only is it a really powerful platform with thousands of base classes and an elegant and compact new language (C#), it can save the developer - and consequently his employer - a significant amount of coding time because of its efficiencies both in the individual development arena, in the code - reuse area, and especially in the team or group development effort.

(This article is dedicated to the late great Bill Evans - one of the most articulate and sensitive jazz artists who ever lived!)

Download the code that accompanies this article

Peter Bromberg is an independent consultant specializing in distributed .NET solutionsa Senior Programmer /Analyst at in Orlando and a co-developer of the EggheadCafe.com developer website. He can be reached at pbromberg@yahoo.com