数据表选择具有多个AND条件的实现 [英] DataTable Select Implementation with Multiple AND Conditions

问题描述

我有一个DataTable，大约有50,000行，我使用 DataTable.Select 来检索行。 选择需要多个 AND 条件，包括通配符匹配。我玩过，发现通过执行相同的多个步骤选择，执行时间可以大大降低，但是更改 AND 语句不影响它。

I have a DataTable with about 50,000 rows that I'm using DataTable.Select to retrieve rows from. The Select requires multiple AND conditions including a wildcard match. I've played around and found that by doing the same Select in multiple steps, the execution time can be greatly reduced, but changing the order of the AND statements doesn't affect it.

//This takes ~ 750 ms
DataRow[] results = myDataTable.Select("Field1 LIKE '*" + term1 + "*'" +
"AND Field2 = '" + term2 + "'" +
"AND Field3 = '" + term3 + "'");

//This also takes ~750 ms
DataRow[] results2 = myDataTable.Select("Field3 = '" + term3 + "'" +
"AND Field2 = '" + term2 + "'" +
"AND Field1 LIKE '*" + term1 + "*'");  

//This takes 0.415 ms
DataTable table1 = myDataTable.Select("Field3 = '" + term3+ "'").CopyToDataTable();
DataTable table2 = table1.Select("Field2 = '" + term2 + "'").CopyToDataTable();
DataRow [] results3 = table2.Select("Field1 LIKE '*" + term1 + "*'");  

我的问题是，有没有办法总是让 SELECT 操作按照从左到右的顺序评估 AND 条件，以便在步骤之间减少搜索记录数量？看起来这可能是一个很好的节省时间。谢谢你的想法

My question is, is there a way to always make the SELECT operation evaluate AND conditions in a left-to-right order so that number of records searched would be reduced between steps? It would seem this could be a good time saver. Thank you for your ideas.

推荐答案

你可以使用 Linq （注意，最慢的条件是最后）：

You could use Linq (note that the slowest condition is the last):

IEnumerable<DataRow> rows = myDataTable.AsEnumerable()
    .Where(r => r.Field<string>("Field2") == term2
            &&  r.Field<string>("Field3") == term3
            &&  r.Field<string>("Field1").Contains(term1));

使用 CopyToDataTable 如果要创建来自结果的新DataTable， ToArray 创建一个 DataRow [] 或保留并使用 foreach 可以枚举结果而不创建新的集合。

Use CopyToDataTable if you want to create a new DataTable from the result, ToArray to create a DataRow[] or leave it and use foreach to enumerate the result without creating a new collection.

这篇关于数据表选择具有多个AND条件的实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！