我们如何像计算列一样快地更新DataTable列值？ [英] How can we update a DataTable column values as fast as calculated columns does?
问题描述
我有一个小型测试程序，可以创建一个包含1M行的DataTable。
然后我添加一个计算列（表达式列），它就像一秒钟一样超快。
然后我只是尝试添加一个新列并为其设置值，它需要超过10秒。
DataTable更新速度很慢。如何使其与计算列一样快？
10秒似乎可以接受 但这只是一个显示差异的测试程序。
这可能会变得很糟糕，具体取决于行数，列数等等。
无论如何，如果计算列能够快速完成，希望我们可以通过正常更新获得相同的性能。
似乎计算列可以关闭一些通知 或其他事情。我不确定。
有什么想法吗？建议吗？也许我在更新DataTable时遗漏了一些东西。
下面是我正在使用的代码示例：
使用System;
使用System.Data;
命名空间Test1
{
课程计划
{
static void Main（string [] args）
{
试试
{
System.Diagnostics.Stopwatch观看=新System.Diagnostics.Stopwatch（）;
                 watch.Start（）;&
Console.WriteLine（"DataTable creation started ..."）;
   DataTable数据=新的DataTable（）;
int nbrCols = 50;
对（INT I = 0; I< nbrCols;我++）
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP; {
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; data.Columns.Add（string.Concat（" COL_"，（i + 1）），typeof（double））;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; }
System.Random R =新System.Random（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; data.BeginLoadData（）;
int nbrows = 1000000;
for（int i = 0; i< nbrows; i ++）
&NBSP; {
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;的DataRow NEWROW = data.NewRow（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;对（INT J = 0; J< nbrCols; J ++）
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; {
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; NEWROW [J] = r.NextDouble（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP; }
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; data.Rows.Add（newRow）;
}
data.EndLoadData（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; watch.Stop（）;&
Console.WriteLine（QUOT;数据表创建= QUOT; + watch.ElapsedMilliseconds / 1000.0）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; INT colIndexFlow1 = data.Columns.IndexOf（QUOT; COL_1"）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; INT colIndexFlow2 = data.Columns.IndexOf（QUOT; COL_2"）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; / *添加计算列* / $
watch.Restart（）;
data.Columns.Add（QUOT; CALC_1" ;,的typeof（双），" COL_1 + COL_2"）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; data.AcceptChanges（）;
watch.Stop（）;&
Console.WriteLine（QUOT;计算的列创建= QUOT; + watch.ElapsedMilliseconds / 1000.0）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; / *添加新列* / $
watch.Restart（）;
data.BeginLoadData（）;
data.Columns.Add（QUOT; NEW_1" ;, typeof运算（双））;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; INT colIdx = data.Columns.IndexOf（QUOT; NEW_1"）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; foreach（data.Rows中的DataRow行）
{
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; //双VAL = row.Field<双>（" COL_1"）+ row.Field<双>（" COL_2"）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; //row.SetField<double>(colIdx，VAL）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; //大部分时间用于实际设置数据
;&NBSP;&NBSP;&NBSP;&NBSP; //问题是我们如何在使用计算列时与更新一样快地进行更新。
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; row.SetField<双>（colIdx，2.0）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; }
data.AcceptChanges（）;
data.EndLoadData（）;
watch.Stop（）;&
Console.WriteLine（QUOT;新列创建= QUOT; + watch.ElapsedMilliseconds / 1000.0）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; }
catch（例外情况）
{
Console.WriteLine（" Error =" + ex.Message）;
}
最后是
{
Console.ReadLine（）;
Console.WriteLine（"按任意键退出"）;
}
}
} b $ b}
提前致谢
您好dtf017 ，
感谢您的发帖。根据您的描述，您的问题与数据开发更相关。我们将您的主题移至
ADO.NET DataSet 论坛以获得更好的支持。
最好的问候，
李旺
Hi,
I have a small test program that creates a DataTable with 1M rows.
Then I add a calculated column (Expression column), it's super fast like a second.
Then I just try to add a new column and set values to it, it takes more than 10 seconds.
DataTable update is slow. How can I make it as fast as calculated column?
10 seconds does seem acceptable but that is just a test program to show the difference.
This can become lot worst depending on how many rows, columns, etc.
And anyway if calculated column is able to do it that fast, hopefully we can get same performance with normal updates.
It seems calculated columns is able to turn off some notifications or other things. I'm not sure.
Any idea? Advice? Maybe I'm missing something when updating the DataTable.
Below is the code sample I'm using:
using System;
using System.Data;
namespace Test1
{
class Program
{
static void Main(string[] args)
{
try
{
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
Console.WriteLine("DataTable creation started ...");
DataTable data = new DataTable();
int nbrCols = 50;
for (int i = 0; i < nbrCols; i++)
{
data.Columns.Add(string.Concat("COL_", (i + 1)), typeof(double));
}
System.Random r = new System.Random();
data.BeginLoadData();
int nbrows = 1000000;
for (int i = 0; i < nbrows; i++)
{
DataRow newRow = data.NewRow();
for (int j = 0; j < nbrCols; j++)
{
newRow[j] = r.NextDouble();
}
data.Rows.Add(newRow);
}
data.EndLoadData();
watch.Stop();
Console.WriteLine("DataTable creation = " + watch.ElapsedMilliseconds / 1000.0);
int colIndexFlow1 = data.Columns.IndexOf("COL_1");
int colIndexFlow2 = data.Columns.IndexOf("COL_2");
/* Add a calculated columns */
watch.Restart();
data.Columns.Add("CALC_1", typeof(double), "COL_1 + COL_2");
data.AcceptChanges();
watch.Stop();
Console.WriteLine("Calculated Column creation = " + watch.ElapsedMilliseconds / 1000.0);
/* Add a new column */
watch.Restart();
data.BeginLoadData();
data.Columns.Add("NEW_1", typeof(double));
int colIdx = data.Columns.IndexOf("NEW_1");
foreach (DataRow row in data.Rows)
{
//double val = row.Field<double>("COL_1") + row.Field<double>("COL_2");
//row.SetField<double>(colIdx, val);
// Most of the time is spent to actually set the data
// Question is how can we make the update happening as fast as the update done when using a calculated column.
row.SetField<double>(colIdx, 2.0);
}
data.AcceptChanges();
data.EndLoadData();
watch.Stop();
Console.WriteLine("New Column creation = " + watch.ElapsedMilliseconds / 1000.0);
}
catch (Exception ex)
{
Console.WriteLine("Error=" + ex.Message);
}
finally
{
Console.ReadLine();
Console.WriteLine("Press any key to exit");
}
}
}
}
Thanks in advance
Hi dtf017,
Thank you for your post. Based on your description, your question is more related to data development. We will move your thread to ADO.NET DataSet forum for better support.
Best Regards,
Li Wang
这篇关于我们如何像计算列一样快地更新DataTable列值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
