.net DataView.RowFilter 与 DataTable.Select() 与 DataTable.Rows.Find()

DataView.RowFilter Vs DataTable.Select() vs DataTable.Rows.Find()

提问by Aseem Gautam

Considering the code below:

考虑下面的代码：

Dataview someView = new DataView(sometable)
someView.RowFilter = someFilter;

if(someView.count > 0) {  …. }

Quite a number of articles which say Datatable.Select() is better than using DataViews, but these are prior to VS2008.

很多文章都说 Datatable.Select() 比使用 DataViews 更好，但这些都是在 VS2008 之前的。

Solved: The Mystery of DataView's Poor Performance with Large Recordsets
Array of DataRecord vs. DataView: A Dramatic Difference in Performance

已解决：DataRecord 与 DataView 的大型记录集数组的 DataView 性能不佳之谜：性能上的巨大
差异

Googling on this topic I found some articles/forum topics which mention Datatable.Select() itself is quite buggy(not sure on this) and underperforms in various scenarios.

谷歌搜索这个话题，我发现一些文章/论坛主题提到 Datatable.Select() 本身是相当错误的（不确定）并且在各种情况下表现不佳。

On this(Best Practices ADO.NET) topic on msdn it is suggested that if there is primary key defined on a datatable the findrows() or find() methods should be used insted of Datatable.Select().

在msdn上的这个（Best Practices ADO.NET）主题上，建议如果在数据表上定义了主键，则应该在 Datatable.Select() 中使用 findrows() 或 find() 方法。

This article here(.NET 1.1) benchmarks all the three approaches plus a couple more. But this is for version 1.1 so not sure if these are valid still now. Accroding to this DataRowCollection.Find() outperforms all approaches and Datatable.Select() outperforms DataView.RowFilter.

本文在这里（.NET 1.1）基准所有这三种方法加上一对夫妇更。但这是针对 1.1 版的，所以不确定这些现在是否仍然有效。根据此 DataRowCollection.Find() 优于所有方法，Datatable.Select() 优于 DataView.RowFilter。

So I am quite confused on what might be the best approach on finding rows in a datatable. Or there is no single good way to do this, multiple solutions exist depending upon the scenario?

所以我很困惑什么可能是在数据表中查找行的最佳方法。或者没有单一的好方法可以做到这一点，根据场景存在多种解决方案？

回答by thmshd

You are looking for the "best approach on finding rows in a datatable", so I first have to ask: "best" for what? I think, any technique has scenarios where it might fit better then the others.

您正在寻找“在数据表中查找行的最佳方法”，所以我首先要问：“最佳”用于什么？我认为，任何技术都有可能比其他技术更适合的场景。

First, let's look at DataView.RowFilter: A DataView has some advantages in Data Binding. Its very view-oriented so it has powerful sorting, filtering or searching features, but creates some overhead and is not optimized for performance. I would choose the DataView.RowFilterfor smaller recordsets and/or where you take advantage of the other features (like, a direct data binding to the view).

首先，让我们看看DataView.RowFilter： DataView 在数据绑定方面有一些优势。它非常面向视图，因此具有强大的排序、过滤或搜索功能，但会产生一些开销并且未针对性能进行优化。我会选择DataView.RowFilter较小的记录集和/或您利用其他功能的地方（例如，直接数据绑定到视图）。

Most facts about the DataView, which you can read in older posts, still apply.

您可以在较早的帖子中阅读的有关 DataView 的大多数事实仍然适用。

Second, you should prefer DataTable.Rows.Findover DataTable.Selectif you want just a single hit. Why? DataTable.Rows.Find returns only a single row. Essentially, when you specify the primary key, a binary tree is created. This has some overhead associated with it, but tremendously speeds up the retrieval.

其次，你应该更喜欢DataTable.Rows.Find了DataTable.Select，如果你想只是一个单一的打击。为什么？DataTable.Rows.Find 仅返回单行。本质上，当您指定主键时，会创建一个二叉树。这有一些与之相关的开销，但极大地加快了检索速度。

DataTable.Selectis slower, but can come very handy if you have multiple criteria and don't care about indexed or unindexed rows: It can find basically everything but is not optimized for performance. Essentially, DataTable.Select has to walk the entire table and compare every record to the criteria that you passed in.

DataTable.Select速度较慢，但​​如果您有多个条件并且不关心索引或未索引的行，则可以非常方便：它基本上可以找到所有内容，但未针对性能进行优化。本质上，DataTable.Select 必须遍历整个表并将每条记录与您传入的条件进行比较。

I hope you find this little overview helpful.

我希望你觉得这个小概述很有帮助。

I'd suggest to take a look at this article, it was helpful for me regarding performance questions. This post contains some quotes from it.

我建议看一下这篇文章，它对我关于性能问题有帮助。这篇文章包含了一些引述。

A little UPDATE:By the way, this might seem a little out of scope of your question, but its nearly always the fastest solution to do the filtering and searching on the backend. If you want the simplicity and have an SQL Serveras backend and .NET3+ on client, go for LINQ-to-SQL. Searching Linq objects is very comfortable and creates queries which are performed on server side. While LINQ-to-Objects is also a very comfortable but also slower technique. In case you didn't know already....

一点更新：顺便说一下，这似乎有点超出您的问题范围，但它几乎总是在后端进行过滤和搜索的最快解决方案。如果您想要简单性并在客户端使用SQL Server作为后端和 .NET3+，请选择 LINQ-to-SQL。搜索 Linq 对象非常方便，并且可以创建在服务器端执行的查询。虽然 LINQ-to-Objects 也是一种非常舒适但速度较慢的技术。如果你还不知道......

回答by Paul Williams

Thomashaid's post sums it up nicely:

Thomashaid 的帖子很好地总结了它：

• DataView.RowFilteris for binding.
• DataTable.Rows.Findis for searching by primary key only.
• DataTable.Selectis for searching by multiple columns and also for specifying an order.

• DataView.RowFilter用于绑定。
• DataTable.Rows.Find是按主键搜索只。
• DataTable.Select用于按多列搜索以及指定顺序。

Avoid creating many DataViews in a loop and using their RowFilters to search for records. This will drastically reduce performance.

避免在循环中创建多个 DataView 并使用它们的 RowFilter 来搜索记录。这将大大降低性能。

I wanted to add that DataTable.Selectcan take advantage of indexes. You can create an index on a DataTable by creating a DataView and specifying a sort order:

我想补充一点，DataTable.Select可以利用索引。您可以通过创建 DataView 并指定排序顺序在 DataTable 上创建索引：

DataView dv = new DataView(dt);
dv.Sort = "Col1, Col2";

Then, when you call DataTable.Select(), it can use this index when running the query. We have used this technique to seriously improve performance in places where we use the same query many, many times. (Note that this was before Linq existed.)

然后，当您调用 时DataTable.Select()，它可以在运行查询时使用此索引。在我们多次使用相同查询的地方，我们已经使用这种技术来显着提高性能。（请注意，这是在 Linq 出现之前。）

The trick is to define the sort order correctly for the Selectstatement. So if your query is "Col1 = 1 and Col2 = 4", then you'll want "Col1, Col2" like in the example above.

诀窍是为Select语句正确定义排序顺序。因此，如果您的查询是“Col1 = 1 and Col2 = 4”，那么您将需要“Col1, Col2”，如上例所示。

Note that the index creation may depend on the actual calls to create the DataView. We had to use the new DataView(DataTable dt)constructor, and then specify the Sort property in a separate step. The behavior may change slightly with different .NET versions.

请注意，索引创建可能取决于创建 DataView 的实际调用。我们必须使用new DataView(DataTable dt)构造函数，然后在单独的步骤中指定 Sort 属性。行为可能会随着不同的 .NET 版本而略有变化。