Attention: We are retiring the ASP.NET Community Blogs. Learn more >

High Performance DataBinding for ASP.NET - A True Story

Performance Through Custom Paging

These days, just about every ASP.NET application has a grid of some sort in it, which is tied to a back end database through some databinding mechanism.  In many cases this is either a DataSet, SqlDataSource, ObjectDataSource, or in some cases a custom business object layer.  Further pushing the envelope, grids are no longer used to display 50 or 100 records, but thousands to hundreds of thousands have become commonplace.  AJAX can be used to boost performance by enabling paging, while not horribly disrupting the user experience.  In this case, getting the data from you DBMS to your application becomes the bottleneck. 

If you're paging on the front end, and not the back end, you're not working as efficiently as you can.  In the past there was a valid excuse, SQL 2000 didn't support paging.  That's right, I said in the past because SQL 2005 does support this.  Unfortunately, the logic couldn't be built directly into the SQLDataSourceControl for ASP.NET.  The solution lies in the ObjectDataSource, and good old typed DataSets.  ScottGu posted some links on this about a year ago, and 4GuysFromRolla.com has a great article with source code available here.

Putting it to Use

So what's the big deal?  Well, I don't get to work with large datasets very often, so I figured I would setup AdventureWorks on my laptop and give it a go.  I followed the article (mostly) and set up my TypedDataSet and ObjectDataSource.  Then I started setting up the Infragistics WebGrid to do partial databinding.  That's when things started going wrong for me.  See, I had already planned out exactly how I was going to connect my grid to the data, using the PartialDatabinding feature of the grid.  If you've never used this feature before, it's pretty hardcore.  The InitializeDataSource event is used, and the eventargs can be cast to the specialized DataSourceEventArgs.  In short, the event allows you to provide the grid with a page of data, rather than forcing you to bind to a complete set of data.  Well, when I assigned my ObjectDataSource to the Grid's DataSourceID property, my InitializeDataSource event stopped firing - but data was still showing up accordingly.  Confused, I turned to SQL and attempted to use the SQL profiler to find out what was actually going on. 

Off-topic Rant

If you're not in the mood for a story, you can skip this paragraph.. Remember how I said things started going wrong?  this is where they really went wrong.  I only had the Express version of SQL and Management Studio installed, so that meant no profiler.  I popped in the CD for the full version of SQL to install the workstation tools, and started the install.  The install was nice enough to let me know that I could not install the workstation tools, because I already had a newer version of the components installed.  So, I attempted to remove SQL Express and all of its components.  Yes, I attempted.. after 'uninstalling' the components, they were all still there!  I went on to manually uninstall the components, and finally decided to turn of UAC (yes, I was using Vista).  If this ever happens to you, ARPWrapper.exe /remove is your friend.  It took me about 3 hours of installing and uninstalling to finally get to a point where I had the full version of SQL 2005 installed and working. 

Unexpected Surprise

Ok, back to work.. time for some profiling.  I started up the profiler and took a look at the queries and the parameter values.  To my amazement, the WebGrid was already doing the data paging for me.  I didn't need to mess with the custom partial databinding through the InitializeDataSource event, I just had to provide the grid with a pageable datasource.  I didn't really believe it at first, but confirmed it with the SR. Developer on the grid team.  It turns out, he put in support for data paging for datasources a while back as a quasi bug fix, but since the SQLDataSource doesn't support data paging, the feature went pretty much unnoticed for the most part. 

Key Takeaways

One thing to keep in mind is that paging your back end data does not have to be paired with your typical paged results in a grid ie. [First prev next Last].  In the WebGrid for instance, paged data can be displayed through a scrolled interface via the "VirtualScrolling" feature.  The beauty here is that the end user still feels like they're looking at the entire dataset at once - while the grid manages to pull down the appropriate pages of data asynchronously.  You can see what I mean by looking at the ScrollOnDemand sample. When combining this performance and user friendly front end technique with an efficient and speedy paged datasource connecting to your back end, you get the best of both worlds.

A couple of stumbling points.. The ObjectDataSource uses the same signature for the SelectMethod and SelectCountMethod - in otherwords, if you define 2 paramters for your SelectMethod, the ObjectDataSource will use those same parameters for the SelectCountMethod.  I added the dummy parameters to my SelectCountMethod definition in my TableAdaptor to make everyone happy.  Also, sorting is a slightly more complicated issue than you would hope.  The beauty of parameterized queries is defeated when you can't parameterize an ORDER BY.  To get around this a bit of dynamic SQL or a cased SPROC is in order.  There's a great article on how to do that from 4GuysFromRolla.com.

You can download the source here (http://download.infragistics.com/users/tonyl/custompaging.zip)  All you'll need to do is set up the connection string and press play!

4 Comments

  • 1. SQL 2000 *does* support paging. It's not quite as simple as in 2005, but it works fine for me.

    2. I suspect that the reason the SqlDataSource control doesn't have built-in support for paging has more to do with time constraints than technical limitations.

    I've implemented a PagedSqlDataSource control, derived from the SqlDataSource control, and it works just as well as the ObjectDataSourceControl. Apart from the lack of designer support, and one instance where I had to resort to reflection to create a SqlCacheDependency object, there was nothing particularly challenging in the implementation. Most of the work went in to re-implementing the data caching, which is all internal to the System.Web assembly.

    An added benefit of the PagedSqlDataSource control is that, unlike the ObjectDataSource control, I don't have to declare overloaded methods (or different method names) for every combination of optional parameters for the stored procedure I'm using.

  • Thanks for the clarifications Richard. Paging in SQL 2000 was spotlighted due to the addition of the RowNumber() keyword - which lead to a single paging solution rather than the 'roll your own' way of doing it in SQL 2000. For those of you who are looking for a solution for paging in SQL 2000, take a look at this article - http://www.4guysfromrolla.com/webtech/041206-1.shtml

  • Tony,
    Thank you for a nice example. The paging works really nicely and smoothly but if you use the up and down keys or PgUp/PgDn rather than the scroll bar, then the cursor gets whacky and eventually disappears and you have to click on a cell inside the grid. How would you implement scrolling using the keys in addition to the scroll bar?
    Den

  • Hi Den,
    Ideally the grid should be providing the keyboard support for you. I'll take a look and if this is a bug, forward it on to our product team. Thanks for the feedback!

Comments have been disabled for this content.