Does the Windows Forms DataGridView implement a true virtual mode?

Question

I have a SQL table containing currently 1 million rows that will grow over time.

There is a specific user requirement to present a sortable grid that displays all rows without paging. The user expects to be able to very quickly jump from row to row and top to bottom by using the scrollbar.

I am familiar with "virtual mode" grids that only present a visible subset of the overall data. They can provide excellent UI performance and minimal memory requirements, (I've even implemented an application using this technique many years ago).

The Windows Forms DataGridView provides a virtual mode that looks like it should be the answer. However unlike other virtual modes I've encountered, it still allocates memory for every row (confirmed in ProcessExplorer). Obviously this causes overall memory usage to needlessly greatly increase and, while allocating these rows, there is a noticeable delay. Scrolling performance also suffers on 1 million + rows.

A real virtual mode would have no need to allocate any memory for rows not being displayed. You just give it the total row count (eg 1,000,000) and all the grid does is scale the scrollbar accordingly. When it is first displayed the grid simply asks for data the first n (say 30) visible rows only, instantaneous display.

When the user scrolls the grid, a simple row offset and the number of visible rows are provided and can be used to retrieve data from the data store.

Here's an example of the DataGridView code I'm currently using:

public void AddVirtualRows(int rowCount)
{
    dataGridList.ColumnCount = 4;


    dataGridList.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
    dataGridList.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;

    dataGridList.VirtualMode = true;

    dataGridList.RowCount = rowCount;

    dataGridList.CellValueNeeded += new DataGridViewCellValueEventHandler(dataGridList_CellValueNeeded);


}
void dataGridList_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
{
    e.Value = e.RowIndex;
}

Am I missing anything here, or is the "virtual" mode of the DataGridView not really virtual at all?

[Update]

It looks like the good old ListView implements exactly the sort of virtual mode I'm looking for. But unfortunately the ListView does not have the cell formatting capabilities of the DataGridView, so I can't use it.

For others that might be able to, I tested it with a four column ListView (in Detail mode), VirtualMode= True and VirtualListSize =100,000,000 rows.

The list is displayed immediately with the first 30 rows visible. I can then scroll rapidly to the bottom of the list with no delay. The memory usage is constant 10 MB at all times.

Answer 1

We just had a similar requirement to be able to display arbitrary, unindexed 1M+ row tables in our application with "very good" performance, using the stock DataGridView. At first I thought it wasn't possible, but with enough head scratching, we came up with something that works very well after spending days pouring over Reflector and .NET Profiler. This was difficult to do, but the results were well worth it.

The way we tackled this problem was by creating a class that implements ITypedList and IBindingList (you can call it LargeTableView, for example) to manage the asynchronous retrieval and caching of information from the database. We also created a single PropertyDescriptor-inheriting class (e.g. LargeTableColumnDescriptor) to retrieve data from each column.

When the DataGridView.DataSource property is set to a class implementing IBindingList, it goes into a pseudo-virtual mode that differs from regular VirtualMode, where as when each row is painted (such as when the user scrolls), the DataGridView accesses the indexer [] on the IBindingList and the respective GetValue methods on each column's PropertyDescriptor to retrieve the values as needed. The CellValueNeeded event is not raised. In our case, we access the database when the indexer is accessed, and then cache the value, so that subsequent re-paints don't hit the database.

I performed similar tests re: memory usage. The DataGridView does allocate an array that is the size of the list (i.e. 1M rows), however each item in the array initially references a single DataGridViewRow, so memory usage is acceptible. I am not sure if the behavior is the same when VirtualMode is true. We were able to eliminate scroll lag by immediately returning String.Empty in the GetValue method if the row is not cached, and then performing the database query asynchronously. When the async request is finished, you can raise the IBindingList.ListChanged event to signal to the DataGridView that it should repaint the cells, except this time reading from the cache which is readily available. That way, the UI is never blocked waiting for database calls.

One thing we noticed is that performance is significantly better if you set the DataSource or number of virtual rows before adding the DataGridView to the form - it cut initialization time in half. Also, make sure that you have both Row and Column autosizing set to None or else you will have additional performance problems.

Side note: the way we accomplished "loading" such a large table in our .NET application was by creating a temporary table on the SQL server that listed the primary keys in the desired sort order along with an IDENTITY (row number), and then persisting the connection for subsequent row requests. This naturally takes time to initialize (approx. 3-5s on a reasonably fast SQL server), but without knowledge of available indexes, we have no better alternative. Then, in our ITypedList implementation, we request rows in pages of 100 rows, where the 50th row is the row that is being painted, so that we limit the number of queries performed each time the indexer is accessed, and that we give the appearance of having all of the data available in our application.

Further reading:

http://msdn.microsoft.com/en-us/library/ms404298.aspx

http://msdn.microsoft.com/en-us/library/system.componentmodel.ibindinglist.aspx