Performance regarding return type for LINQ query c

Question

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!

Answer 1

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.