问题描述
我有一个 SQL 表，目前包含 100 万行，这些行会随着时间的推移而增长.
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.
我熟悉仅呈现整体数据可见子集的虚拟模式"网格.它们可以提供出色的 UI 性能和最低的内存要求(我什至在多年前使用这种技术实现了一个应用程序).
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).
Windows 窗体 DataGridView 提供了一种看起来应该是答案的虚拟模式.然而，与我遇到的其他虚拟模式不同，它仍然为每一行分配内存(在 ProcessExplorer 中确认).显然，这会导致整体内存使用量不必要地大幅增加，并且在分配这些行时，会有明显的延迟.100 万行以上的滚动性能也会受到影响.
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.
真正的虚拟模式不需要为未显示的行分配任何内存.您只需给它总行数(例如 1,000,000)，网格所做的就是相应地缩放滚动条.当它第一次显示时，网格只要求前 n(比如 30)行可见的数据，即时显示.
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.
以下是我当前使用的 DataGridView 代码示例:
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;
}
我在这里遗漏了什么，还是 DataGridView 的虚拟"模式根本不是虚拟的?
Am I missing anything here, or is the "virtual" mode of the DataGridView not really virtual at all?
[更新]
看起来不错的旧 ListView 实现了我正在寻找的那种虚拟模式.但遗憾的是ListView 没有DataGridView 的单元格格式化功能，所以无法使用.
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.
对于其他可能能够做到的，我使用四列 ListView(在详细模式下)、VirtualMode=True 和 VirtualListSize=100,000,000 行对其进行了测试.
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.
列表立即显示，前 30 行可见.然后我可以毫不延迟地快速滚动到列表的底部.内存使用量始终为 10 MB.
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.
推荐答案
我们只是有一个类似的要求，即使用股票 数据网格视图.起初我认为这是不可能的，但是经过足够的挠头，我们在花了几天时间倾注 Reflector 和 .NET Profiler 之后想出了一些非常有效的东西.这很难做到，但结果非常值得.
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.
我们解决这个问题的方法是创建一个实现 ITypedList 和 IBindingList 的类(例如，您可以将其称为 LargeTableView)管理从数据库中异步检索和缓存信息.我们还创建了一个继承 PropertyDescriptor 的类(例如 LargeTableColumnDescriptor)来从每一列检索数据.
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.
当将 DataGridView.DataSource 属性设置为实现 IBindingList 的类时，它会进入不同于常规的伪虚拟模式VirtualMode，当每一行被绘制时(例如当用户滚动时)，DataGridView 访问 IBindingList 上的索引器 [] 和每列的相应 GetValue 方法PropertyDescriptor 根据需要检索值.CellValueNeeded 事件未引发.在我们的例子中，我们在访问索引器时访问数据库，然后缓存该值，以便后续重绘不会命中数据库.
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.
我对内存使用情况进行了类似的测试.DataGridView 确实分配了一个与列表大小相同的数组(即 1M 行)，但是数组中的每个项目最初都引用一个 DataGridViewRow，因此内存使用是可以接受的.当 VirtualMode 为真时，我不确定行为是否相同.如果行没有被缓存，我们能够通过在 GetValue 方法中立即返回 String.Empty 来消除滚动延迟，然后执行数据库异步查询.当异步请求完成时，您可以引发 IBindingList.ListChanged 事件以向 DataGridView 发出信号，表明它应该重新绘制单元格，但这次从缓存中读取，这是现成的.这样，UI 永远不会被阻塞等待数据库调用.
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.
我们注意到的一件事是，如果您在将 DataGridView 添加到表单之前设置数据源或虚拟行数，性能会显着提高 - 它会将初始化时间缩短一半.此外，请确保您将行和列自动调整大小设置为 None，否则您将遇到其他性能问题.
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.
旁注:我们在 .NET 应用程序中加载"如此大的表的方式是在 SQL 服务器上创建一个临时表，该表以所需的排序顺序列出主键以及 IDENTITY (行号)，然后为后续行请求保持连接.这自然需要时间来初始化(在相当快的 SQL 服务器上大约需要 3-5 秒)，但在不了解可用索引的情况下，我们没有更好的选择.然后，在我们的 ITypedList 实现中，我们以 100 行的页面请求行，其中第 50 行是正在绘制的行，因此我们限制了每次访问索引器时执行的查询次数，并给出外观让我们的应用程序中的所有数据都可用.
进一步阅读:
http://msdn.microsoft.com/en-us/library/ms404298.aspx
http://msdn.microsoft.com/en-us/library/system.componentmodel.ibindinglist.aspx
这篇关于Windows 窗体 DataGridView 是否实现了真正的虚拟模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！