I had a serious performance killer when using SetParentRow in the creation of a DataSet in ADO.NET.
After some trial and error I found out it is very important to call SetParentRow first and add the childRow to the child table later.
In the first case the performance of SetParentRow is linear, in the second case it is exponential when adding more rows. Check out the C# examples and results.
Version 1 = fast
for (j = 0; j < (number of parentROws); j++)
{
parentRow = parent.NewRow();
parentRow["parentID"] = j;
parentRow["Name"] = "Parent"+ j.ToString();
parent.Rows.Add(parentRow);
for (int i = 0; i < 10; i++)
{
childRow = childs.NewRow();
childRow["childName"] = "ABCDFEGHIJKL"
childRow["childId"] = (i+1);
childRow.SetParentRow(parentRow); // 1: set relation
childs.Rows.Add(childRow); // 2: add row
}
}
Version 2 = slow
for
(j = 0; j < (number of parentROws); j++)
{
parentRow = parent.NewRow();
parentRow["parentID"] = j;
parentRow["Name"] = "Parent"+ j.ToString();
parent.Rows.Add(parentRow);
for (int i = 0; i < 10; i++)
{
childRow = childs.NewRow();
childRow["childName"] = "ABCDFEGHIJKL"
childRow["childId"] = (i+1);
childs.Rows.Add(childRow); // 1: add row
childRow.SetParentRow(parentRow); // 2: set relation
}
}
processing time in seconds:
#parentrows v1 v2
1000 0,64 1,20
2000 1,20 4,21
3000 1,87 16,56
4000 2,60 53,86
5000 3,40 116,23
6000 4,28 211,97
7000 5,67 347,43
8000 6,37 527,09
9000 6,92 761,68
10000 7,14 1057,74