与自动排序兼容的LINQ查询的返回类型有关的性能 [英] Performance regarding return type for LINQ query compatible with automatic sorting
问题描述
这并不是真正的问题，但我希望您能提出一些建议.
使用Linq2Sql(更具体地说是 PLINQO 的Winforms C#.net3.5 [sp1] Visual Studio 2008.顺便说一句！)我有一个结果集返回+/- 19000行数据(每行大约80字节数据)，并选择将数据检索方法推到后台并相应地更新UI.效果很好.
但是，当我为Ling数据检索方法使用不同的返回类型时，我注意到一些性能差异.我知道每个人都建议返回List<T>或IEnumarable<T>，然后将DataGridView的数据源设置为该值，但是不幸的是，它不支持对对象进行本机排序.经过一番挖掘后，我在 MSDN此处上找到了SortableBindingList<T>.我应用了它，并且网格花了不到一秒钟的时间来填充它自己-但是，当我单击一列对其进行排序时，实现排序花费了大约一秒钟的时间.
然后我决定走DataTable路线，但是发现ToDataTable方法已被删除，但是经过更多挖掘之后，找到了一种在此 MSDN文章.应用后，我发现检索花费了大约2秒钟来填充Grid，但是此后即刻进行了排序(在19000行中！)！自然，我坚持使用这种方法.
还请记住，我已禁用任何网格内编辑/添加/删除操作.网格仅用于显示数据.对话框形式会根据当前选定的行(隐藏的主键列)提供其他任何CRUD操作.
这是我用于这两种方法的代码:
1)SortableBindingList
//declare private member
private SortableBindingList<PdtAllocation> listAlloc = null;
private void RefreshData() {
bcsDataContext ctx = new bcsDataContext();
try {
listAlloc = new SortableBindingList<PdtAllocation>(ctx.PdtAllocation.ToList());
}
catch (Exception) {
throw;
}
finally {
ctx.Dispose();
}
dataGridView1.DataSource = listAlloc;
}
2)CopyToDatatable
//declare private member
private DataTable dt = null;
private void RefreshData() {
dt = new DataTable();
bcsDataContext ctx = new bcsDataContext();
try {
ctx.PdtAllocation.CopyToDataTable(dt, LoadOption.PreserveChanges);
}
catch (Exception) {
throw;
}
finally {
ctx.Dispose();
}
dataGridView1.DataSource = dt;
}
现在，我知道这似乎是一个"询问并回答"的情况，但是我非常感谢您的投入以及使用CopyToDataTable()路线时遇到的任何已知问题. /p>
感谢您...为歉意的查询表示歉意！
这是我对您的问题的看法(采用SortableBindingList路线).
您是否使用了基于反射的通用排序算法?我起初是这样做的，但性能确实很差.最后，我提出了以下解决方案:在SortableBindingList<T>中提供默认排序，但也可以在派生类中实现专门的排序.
这是一些代码.
在SortableBindingList<T>.ApplySortCore()中:
protected override void ApplySortCore(PropertyDescriptor prop, ListSortDirection direction)
{
// Check if the sorted property implements IComparable
...
List<T> itemsList = (List<T>)this.Items;
Comparison<T> comparer = GetComparer(prop);
itemsList.Sort(comparer);
if (direction == ListSortDirection.Descending)
{
itemsList.Reverse();
}
...
// Set sort state (is sorted, sort property, sort dir)
}
通用SortableBindingList<T>提供了一个基于反射的基本排序器:
protected virtual Comparison<T> GetComparer(PropertyDescriptor prop)
{
return new Comparison<T>(delegate(T x, T y)
{
if (prop.GetValue(x) != null)
return ((IComparable)prop.GetValue(x)).CompareTo(prop.GetValue(y));
else if (prop.GetValue(y) != null)
return -1 * ((IComparable)prop.GetValue(y)).CompareTo(prop.GetValue(x));
else
return 0;
});
}
如您所见，GetComparer()是虚拟的，因此可以在从SortableBindingList<T>派生的类中覆盖它，以提供一个快得多的比较器，并根据属性的类型进行了调整.实际排序.例如，当通用比较器在4秒内(通过String属性对10000条记录进行排序)时，专用比较器在70毫秒内完成了相同的工作.
class CustomerList : SortableBindingList<Customer>
{
protected override Comparison<Customer> GetComparer(PropertyDescriptor prop)
{
Comparison<Customer> comparer = null;
switch (prop.Name)
{
case "FirstName":
comparer = new Comparison<Customer>(delegate(Customer x, Customer y)
{
string xx = (null == x) ? null : x.FirstName;
string yy = (null == y) ? null : y.FirstName;
return String.Compare(xx, yy);
});
break;
...
}
return comparer;
}
}
最后一点:发布的许多代码是从其他来源(如SO或Microsoft)复制/启发而来的，因此功劳不是我的.我唯一的贡献就是虚拟化了比较器，但是我敢肯定，基于相同的想法，使用谷歌搜索会浮出更好的，更早的解决方案.
this isn't really an issue, but more of a concern that I would appreciate some input on please.
Winforms C# .net3.5[sp1] Visual Studio 2008 using Linq2Sql (more specifically PLINQO...which is fantastic btw!). I have a resultset returning +/- 19000 rows of data (with about 80bytes of data per row) and opted to push the data retrieval method to the background and update the UI accordingly. This works fine.
However, I've noticed some performance differences when using different return-types for my Ling data retrieval method. I know everyone suggests returning a List<T> or IEnumarable<T>, and set the DataGridView's datasource to that, but unfortunately it doesn't support sorting natively for objects. After some digging around I found the SortableBindingList<T> on MSDN here. I applied it, and the Grid took under a second to populate itself - however when I click on a column to sort it, it took a little over a second or so to implement the sort.
I then decided to go the DataTable route, but found out that the ToDataTable method has been removed, but after more digging found a way to implement it on this MSDN article. After applying it, I discovered that the retrieval took about 2 seconds to populate the Grid, but thereafter sorting (on 19000 rows!) was instantaneous!! Naturally I stuck with this approach.
Also keep in mind that I have disabled any in-grid-editing/adding/deleting. The grid is purely for displaying data. Any other CRUD operations are provided by dialog forms according to the current selected row (hidden primary key column).
Here is the code I used for both methods:
1) SortableBindingList
//declare private member
private SortableBindingList<PdtAllocation> listAlloc = null;
private void RefreshData() {
bcsDataContext ctx = new bcsDataContext();
try {
listAlloc = new SortableBindingList<PdtAllocation>(ctx.PdtAllocation.ToList());
}
catch (Exception) {
throw;
}
finally {
ctx.Dispose();
}
dataGridView1.DataSource = listAlloc;
}
2) CopyToDatatable
//declare private member
private DataTable dt = null;
private void RefreshData() {
dt = new DataTable();
bcsDataContext ctx = new bcsDataContext();
try {
ctx.PdtAllocation.CopyToDataTable(dt, LoadOption.PreserveChanges);
}
catch (Exception) {
throw;
}
finally {
ctx.Dispose();
}
dataGridView1.DataSource = dt;
}
Now I know this probably seems like an "asked-and-answered" case, but I would really appreciate your input, as well as any known issues with going the CopyToDataTable() route.
Thank u....and apologies for the looong query!
here's my take on your question (going the SortableBindingList route).
Did you use a generic, reflection based sorting algorithm? I did this at first and the performance was really bad. Finally I came up with the following solution: provide default sorting in SortableBindingList<T> but also leave open the possibility to implement specialized sorting in derived classes.
Here's some code.
In SortableBindingList<T>.ApplySortCore():
protected override void ApplySortCore(PropertyDescriptor prop, ListSortDirection direction)
{
// Check if the sorted property implements IComparable
...
List<T> itemsList = (List<T>)this.Items;
Comparison<T> comparer = GetComparer(prop);
itemsList.Sort(comparer);
if (direction == ListSortDirection.Descending)
{
itemsList.Reverse();
}
...
// Set sort state (is sorted, sort property, sort dir)
}
The generic SortableBindingList<T> provides a basic, reflection based sorter:
protected virtual Comparison<T> GetComparer(PropertyDescriptor prop)
{
return new Comparison<T>(delegate(T x, T y)
{
if (prop.GetValue(x) != null)
return ((IComparable)prop.GetValue(x)).CompareTo(prop.GetValue(y));
else if (prop.GetValue(y) != null)
return -1 * ((IComparable)prop.GetValue(y)).CompareTo(prop.GetValue(x));
else
return 0;
});
}
As you can see, GetComparer() is virtual, so one can override it in a class derived from SortableBindingList<T> in order to provide a much faster comparer, tweaked to the type of the property being actually sorted. For example, while the generic comparer sorted (by a String property) 10000 records in 4 seconds, the specialized comparer did the same job in 70ms.
class CustomerList : SortableBindingList<Customer>
{
protected override Comparison<Customer> GetComparer(PropertyDescriptor prop)
{
Comparison<Customer> comparer = null;
switch (prop.Name)
{
case "FirstName":
comparer = new Comparison<Customer>(delegate(Customer x, Customer y)
{
string xx = (null == x) ? null : x.FirstName;
string yy = (null == y) ? null : y.FirstName;
return String.Compare(xx, yy);
});
break;
...
}
return comparer;
}
}
A final note: much of the code posted was copied/inspired from other sources, like SO or Microsoft, so the credit is not mine. My only contribution was virtualizing the comparer but I'm sure a little googling would surface better, earlier solutions based on the same idea.
这篇关于与自动排序兼容的LINQ查询的返回类型有关的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
