With 2 nested gridviews with large data sets each what's the most optimized way to implement

Question

Say I have 2 tables in a database, each with 100,000's of rows in detail and 10,000's of rows in the master 5 columns each, properly indexed and related. I want to have master/detail nested gridviews. What's the fastest architecture I could design in .net to do this? The largest result set on the master could be in the thousands (usually in the tens though) and the detail per records could be in the hundreds (usually in the single digits though). I'd like to be able to do a display all masters if possible.

Answer 1

The bottom line: bind to DataReaders, use Repeaters instead of GridViews, and turn off ViewState.

The design you're proposing is going to be pretty hard on your users (thousands of records, yikes), but you probably already know that.

If this is just for display and you want the absolute fastest architecture for Asp.Net, you should obtain an IDataReader for each data segment (master and child), sorted such that you can manually match child records while reading both resultsets in a forward-only fashion. (See Scott Mitchell's Why I don't Use DataSets in my ASP.NET Applications for some details about DataReader performance - as long as you aren't optimizing prematurely, it's quite good advice.)

Instead of using a GridView, I'd use a Repeater which has less overhead and lets you write more compact HTML (for a smaller payload to the client): bind the master IDataReader to that repeater. (I'm not sure whether you meant the GridView control, or just a conceptual grid - the basic architecture would be the same for a GridView)

Then add a handler to the Repeater.ItemDataBound that checks if the child data reader's current record matches. If it does, manually render your detail data for that master record.

Finally, you should also turn ViewState off, at least for the Repeater (preferably for as much of the page as possible), again so that the HTML payload is smaller.

If you're totally committed to nested GridViews, particularly to using a GridView to render the detail data, it's going to hurt the performance one way or another, because you'll have to either make many more database calls (to obtain discrete resultsets you can bind to) or you'll have to massage the detail data into an intermediary container.