Applications slow down when getting distinct of 50K+ records

Navin Subramanian

I am having Application slowness when i suppose to get distinct out a DataTable which has 50k+ records. i am using the below code to do it

///
/// Generate Distinct Data Table from the Data Table Generated from server.
///
Func GetDistinctTable =
(cancellationToken, DqmDataGridCollectionView, ResultDataTable) =>
{
var distinctCol = DqmDataGridCollectionView.SortDescriptions.ToList();
if (distinctCol.Any())
{
var _distinctColumns = from n in distinctCol
select new { col = n.PropertyName.Split('_')[1] }.col;
var dtLinq20 = new DataTable();
foreach (var item in _distinctColumns)
{
var existingColumn = ResultDataTable.Columns[item];
var _dColumn = new DataColumn { ColumnName = existingColumn.ColumnName, DataType = existingColumn.DataType, DefaultValue = existingColumn.DefaultValue, AllowDBNull = existingColumn.AllowDBNull, Caption = existingColumn.Caption };
dtLinq20.Columns.Add(_dColumn);
}
DataTable DistinctDataTable = null;
try
{
DistinctDataTable = ResultDataTable.AsEnumerable().AsParallel().WithCancellation(cancellationToken.Token)
.Select(row =>
{
var newRow = dtLinq20.NewRow();
foreach (var item in _distinctColumns)
{
newRow[item] = row[item];
}
return newRow;
})
.Distinct(DataRowComparer.Default).CopyToDataTable();
}
catch (Exception)
{
return null;
}
DistinctDataTable.TableName = "Distinct_TBL";
return DistinctDataTable;
}
return null;
};

But for me it's taking too much of my applications load time. can any one suggest me a best solution?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bikesh Srivastava10y ago
Hi,
yes definatly if you will 50k records at a timer it will goes down,
you can make seprate method or thread.
Navin Subramanian10y ago
HiTejas Trivedi,
the logic here is after generating distinct datatable from the actual datatable from the database. we have to apply coloring,foreground,background, font size on the datagrid for each cell.
Some what related to HeatMap in datagrid .
Tejas Trivedi10y ago
Hello Navin,

Here my suggestion is to get better performance, move some of the logic to stored procedure which is related to filter the records from the whole.

I am not able to imagine the what exact complex logic can't move to stored procedure.

Even dataview, or any approach at application layer would take longer time than the simple query as it is optimized and can further be optimized using database or qurey tunning.

Please don't forget to mark as answer if this helps.
Navin Subramanian10y ago
Hi Midhun T P
the ToTable method takes more time than what was there in my code. And i don't think ToTablewill be more faster than AsParallel .
Navin Subramanian10y ago
The Application that i was working on was a pretty complicated thing. And as you said i am using the SP to get all the records for my calculation and it's necessary to get all the records before beginning the calculation. before putting this code i was using the DataTable.DefaultView.ToTable that takes more time than this so i have taken this rout.
Midhun Tp10y ago
Hi,
You can use dataview to get distinct values from datatable -
DataView view = new DataView(dt);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);
Tejas Trivedi10y ago
Hi Navin,

it is taking time as you are fetching 50 k record in DataTable and after that using row comparer you are performing the distinct operation.

I suggest rather than fetching all 50 k and performing distinct operation on application side, just write use stored procedure and in database query perform the same operation will result in better performance.

Hope, this will help you out. Feel free to write if you need more information.

Please don't forget to mark as answer if this helps.