DataView.Sort is a performance bottleneck

Question

I have a performance bottleneck on a DataView.Sort. The code is below.

    /// <summary>
    /// Filters the data table and returns a new data table with only the filtered rows.
    /// </summary>
    /// <param name="dtInput">The dt input.</param>
    /// <param name="filterExpression">The filter expression.</param>
    /// <returns></returns>
    protected virtual DataTable FilterDataTable(DataTable dtInput, string filterExpression)
    {
        DataTable result = dtInput;
        if (!string.IsNullOrEmpty(filterExpression) && filterExpression.Trim().Length > 0)
        {
            DataView view = new DataView(dtInput);
            view.RowFilter = filterExpression;
            view.Sort = HierarchyFieldMap.DisplayedValue;
            result = view.ToTable();
        }
        return result;
    }

Any idea's on how to improve this method?

It takes ~1 second to execute.

EDIT

I found this link on DataView's Poor Peformance with Large RecordSets

Answer 1

It may be quicker to sort in database with all the indexes and stats available, especially if you paginate the result before displaying to the user.

Answer 2

I agree with Sheng here, when you have to sorting 50 - 100k rows it's time to move some logic to the layer that is meant just for that, the database. Create a stored Procedure that takes the rowlimit and current page as parameters, or jsut build your select statement based on those values, .NET is fast, but not optimized for these kind of operations, where as SQL server (or any RDBMS for that matter).

Answer 3

Since you're not returning a DataView but a DataTable, you should be able to get a performance boost - not order-of-magnitude, but 25-30% - by using DataTable.Sort:

private static DataTable SortDataTable(DataTable t, 
   string filterExpression,
   string sortExpression)
{
    DataTable t1 = t.Clone();
    t1.BeginLoadData();
    foreach (DataRow r in t.Select(filterExpression, sortExpression))
    {
        t1.Rows.Add(r.ItemArray);
    }
    t1.EndLoadData();

    return t1;
}

Most of the time that's being taken up there is copying the data into the new table. If you can avoid creating a new table and work with the array of DataRows that DataTable.Select returns you can get a considerable improvement.