Geeks With Blogs

Welcome to my blog.
Here's what we've got on the menu today:

Lorin Thwaits A geek says what?

While teaching the ASP.NET 2.0 class (3201) in Baton Rouge, some questions about performance came up surrounding the new DataSource classes.  I had actually meant to get all this testing done and posted by the middle of last week, so for those waiting to see these results, thanks very much for your patience.  Altogether this became a fun little experiment, and more time consuming than I expected!  I hope you find the results useful.

To get started let's review the type of design-time fun we could have back in v1.1.  If you drop a DataAdapter, DataSet, and DataGrid on a web form it starts to look like this:

You can then set the DataGrid's DataSource to be DataSet1 from its properties, but you still have to write these two lines of code to actually wire things up and see the data:


Pretty basic, and this approach has worked well for us for over four years now.  But for those that really hate writing code for whatever reason Microsoft wants to make it even easier with the new SqlDataSource component.  There are two tabular DataSource components that come out of the box, SqlDataSource and AccessDataSource.  There are other hierarchical data sources, but for this post we'll just focus on what these two can do, and test the heck out of specifically the SqlDataSource.

Although these data-oriented components sound like they should live in the System.Data namespace, they are actually found in System.Web!  So only available for use in ASP.NET projects, not in smart clients / WinForms.  Their main goal in life is to allow wiring up of data to controls totally at design-time.

In VS2005 you can just drag a DataSource and GridView onto the design surface, and then use the GridView's "Smart Tag" to specify the DataSource.  Like magic, you are pulling data.  Plus you can directly enable paging and sorting!  (The girl at the left reminds me of how excited most DataGrid-centric developers get when they see this in action the very first time.)  No code whatsoever to make all that happen.  Well, at least no code that you wrote.  The compiler is actually quite busy behind the scenes making sure everything is plumbed properly.

Here is a sample of how it looks in the designer:

Pretty simple on the surface, but as with many seemingly simple things in life, it's fairly complex underneath the hood.  Very important to those who care about performance then are the two possible values for SqlDataSourceMode, which designates the methodology the DataSource uses to bind to the data.  It can either be set to use a DataReader bound straight to the control, or to have an interim DataSet involved (the default).  Always interested in top-notch performance, I decided to examine the difference between these two options, and also see how they compared with coding it from scratch using the DataReader and DataAdapter classes.  Finally I also wanted to see how all these new data classes in .NET 2.0 fared against the lean and mean DataAdapter and DataReader from v1.1.  Usually I load between 50 and 2500 rows of data in my applications, so for my testing I used this query against the Northwind database that returns 830 rows:

 SELECT orderID,customerID,employeeID,orderDate FROM Orders

And here's the simple test application I created to time everything:

I also made a v1.1 version, but of course leaving out the two SqlDataSource tests.  If anyone cares to try it out for themselves, here's both projects:

For each run of the test it loops through 500 iterations, and I ran each test 5 times averaging the overall times.  Here are the results showing elapsed time, so shorter bars are better:

The top two are specific to the SqlDataSource class in .NET 2.0, showing how things differ with SqlDataSourceMode set to DataReader or DataAdapter.  Quite an impact that choice has on performance!  The remaining six results compare 3 tests in 2.0 with the same 3 tests done in 1.1.

As expected in 1.1 the fastest option is to use SqlDataReader.  But it costs only a little more time to have that DataReader first fill up a DataTable that gets bound.  And from there it costs only a little more to have the more automated SqlDataAdapter do the job of filling up the DataTable for you.

Even though the performance in 2.0 when working with DataTables is half of what we had in 1.1, note that this is with only 830 rows.  I intentionally tested with a data set consistent with the same sizes I currently use in my apps.  When significantly larger data sets are put into the new DataTable the performance then improves greatly, as described at the end of John Papas article DataSet and DataTable in ADO.NET 2.0.  It's just with these smaller data sets that things are slower.

Someone out there might be curious if the DataTable's BeginLoadData() method would speed anything up in this test.  This method has the effect of turning off notifications, index maintenance, and constraints, and is great if you have those items in place.  But in this example we have none of that, and testing with this method made absolutely no difference in the performance.

So bottom line in ASP.NET 2.0 when it comes to performance: unless you need to enable paging and sorting, when you use the SqlDataSource you would want to set the SqlDataSourceMode to DataReader.  And overall in 2.0 with small data sets you gain a little speed by choosing to bind to DataReaders where possible rathar than DataSets or DataTables.

That's all for this time.  Up next: AJAX fun in ASP.NET 2.0!


Posted on Monday, February 20, 2006 4:25 PM ASP.NET , Performance | Back to top

Comments on this post: Performance of the new SqlDataSource component in ASP.NET 2.0

# re: Performance of the new SqlDataSource component in ASP.NET 2.0
Requesting Gravatar...
I love dissecting stuff (esp those older radios, computers etc) too.
Left by DDos Protection on Nov 06, 2009 11:31 PM

# re: Performance of the new SqlDataSource component in ASP.NET 2.0
Requesting Gravatar...
But in this example we have none of that, and testing with this method made absolutely no difference in the performance
Left by used gun safes on Jan 26, 2010 5:29 PM

# re: Performance of the new SqlDataSource component in ASP.NET 2.0
Requesting Gravatar...
thanks for this article.. its very useful
Left by french door blinds on Feb 04, 2010 12:09 AM

# re: Performance of the new SqlDataSource component in ASP.NET 2.0
Requesting Gravatar...
thanks for this confusing information.
Left by decorative wall paneling on Jul 29, 2010 4:18 PM

Your comment:
 (will show your gravatar)

Copyright © Lorin Thwaits | Powered by: