DataTable.AddRange Slow

  • Thread starter Thread starter Matthew Wieder
  • Start date Start date
M

Matthew Wieder

I'm finding that the AddRange call in the following code takes several
seconds to execute (approx. 4 seconds).

DataColumn[] adcCols = new DataColumn[3000];
for (int i=0; i<adcCols.Length; i++)
{
adcCols = new DataColumn();
}
myDataTable.Columns.AddRange(adcCols);

Is there some way for me to speed this up? I tried using
myDataTable.BeginLoadData and EndLoadData but it didn't help any. The
end goal is adding 3000 columns to an existing datatable, so if there is
some other way to do that, please let me know.

thanks!
 
Hi Matthew,

Thank you for posting in the community!

I will help you on this issue again:-)

I have tested your code, on my machine, it costs over 30 seconds :-(

Actually, I still did not find a good way to greatly improve the
performance of your issue. The only workaround I can think of is first
generate the schema and write it to the disk. Then, when you want to use it
at runtime, just load the schema dynamicly. Like this:

private void button1_Click(object sender, System.EventArgs e)
{
DataTable myDataTable=new DataTable();
DataColumn[] adcCols = new DataColumn[3000];
for (int i=0; i<adcCols.Length; i++)
{
adcCols = new DataColumn();
}
myDataTable.Columns.AddRange(adcCols);

DataSet ds=new DataSet();
ds.Tables.Add(myDataTable);
ds.WriteXmlSchema(@"C:\test.xml");
MessageBox.Show("succeed");
}

private void button2_Click(object sender, System.EventArgs e)
{
DateTime dt=DateTime.Now ;
DataSet ds=new DataSet();
ds.ReadXmlSchema(@"C:\test.xml");
TimeSpan ts=DateTime.Now- dt;
Console.WriteLine(ts.Seconds.ToString());
}

This workaround can improve a little performance on your issue.

Actually I think your design of adding so many columns to a datatable is
not a good one. Can you show the community why you want to add so many
columns to a datatable?

=================================
Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
The scenario is that I have a Datatable such as:
A B C
1 21 12 32
2 1 43 12
3 32 12 35

and I need to produce:
A B C D E F G
1 21 12 32 12 32 12 32
2 1 43 12 43 12 43 12
3 32 12 35 12 35 12 35

which copies some columns and their data from the initial datatable n
times into a new datatable. As you have said, the code is exceedingly
slow. There must be some way to make it faster.
thanks,
-Matthew
 
Hi Matthew,

Thanks very much for your feedback.

I already understand what you want to do. I just do not understand why you
need so many datacolumns in your datatable?

Actually, for about 3000 columns to be added into the datatable, I think
the time for you(Only 2 seconds) is not a bad performance. The slow is
because of the High load charge.

I can not find a better way to greatly way to improve the performance(After
all, the performance can not be improve unconditionally). I think you
should change your design to add less columns to your datatable. This will
greatly increase your performance.

If you have any concern that can not decrease the column number, please
feel free to tell me, I will help you.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
The number of columns is not under my control, so "decreasing the column
number" is not a solution, it just avoids the issue. If there is no way
to speed this up then we will have to live with the performance.
thanks,
-Matthew
 
Hi Matthew,

Oh, I see your concern. I will spend some time to do some research and
consulting to see if there is a way to improve the performance.

Thanks for your understanding.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Matthew,

Oh, I see your concern. I will spend some time to do some research and
consulting to see if there is a way to improve the performance.

Thanks for your understanding.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Matthew,

Sorry for letting you wait for so long time.

After do research and consulting, I think the only way to improve the
performance is adding the datacolumn in the loop instead of using
DataTable.AddRange. Do like this:

DataTable myDataTable=new DataTable();
DataColumn[] adcCols = new DataColumn[3000];
for (int i=0; i<3000; i++)
{
adcCols = new DataColumn();
myDataTable.Columns.Add(adcCols);
}

Note: In the code, I also replace the "adcCols.Length" count with "3000",
which may also save a little time.

This can improve a little performance. But only a little. Almost 6%----7%
on my machine.

I think there is not a better way to greatly improve the performance, hope
this helps.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Matthew,

Does my reply make sense to you?

If you still have any concern, please feel free to feedback, thanks

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Back
Top