Datatable add new column and values speed issue
- by Cine
I am having some speed issue with my datatables. In this particular case I am using it as holder of data, it is never used in GUI or any other scenario that actually uses any of the fancy features.
In my speed trace, this particular constructor was showing up as a heavy user of time when my database is ~40k rows. The main user was set_Item of DataTable.
protected myclass(DataTable dataTable, DataColumn idColumn)
{
this.dataTable = dataTable;
IdColumn = idColumn ?? this.dataTable.Columns.Add(string.Format("SYS_{0}_SYS", Guid.NewGuid()), Type.GetType("System.Int32"));
JobIdColumn = this.dataTable.Columns.Add(string.Format("SYS_{0}_SYS", Guid.NewGuid()), Type.GetType("System.Int32"));
IsNewColumn = this.dataTable.Columns.Add(string.Format("SYS_{0}_SYS", Guid.NewGuid()), Type.GetType("System.Int32"));
int id = 1;
foreach (DataRow r in this.dataTable.Rows)
{
r[JobIdColumn] = id++;
r[IsNewColumn] = (r[IdColumn] == null || r[IdColumn].ToString() == string.Empty) ? 1 : 0;
}
Digging deeper into the trace, it turns out that set_Item calls EndEdit, which brings my thoughts to the transaction support of the DataTable, for which I have no usage for in my scenario.
So my solution to this was to open editing on all of the rows and never close them again.
_dt.BeginLoadData();
foreach (DataRow row in _dt.Rows)
row.BeginEdit();
Is there a better solution? This feels too much like a big giant hack that will eventually come and bite me.
You might suggest that I dont use DataTable at all, but I have already considered that and rejected it due to the amount of effort that would be required to reimplement with a custom class. The main reason it is a datatable is that it is ancient code (.net 1.1 time) and I dont want to spend that much time changing it, and it is also because the original table comes out of a third party component.
