一个数据转换程序
this.sqlDataAdapter1.SelectCommand.CommandText="SELECT * FROM bmk";
this.sqlDataAdapter1.Fill(dataSet11,"bmk");
this.sqlDataAdapter1.SelectCommand.CommandText="SELECT DISTINCT KS_H FROM bmk ORDER BY KS_H";
this.sqlDataAdapter1.Fill(dataSet11,"bmkfilter");
progressBar1.Maximum=dataSet11.Tables["bmkfilter"].Rows.Count;
label1.Refresh();
label1.Text="共有"+dataSet11.Tables["bmk"].Rows.Count+"条数据,将处理成"+dataSet11.Tables["bmkfilter"].Rows.Count+"条数据";
for(int i=0;i<dataSet11.Tables["bmkfilter"].Rows.Count;i++)
{
Application.DoEvents();
this.sqlDataAdapter2.InsertCommand.CommandText="INSERT INTO gkdata(num) VALUES (@num)";
this.sqlDataAdapter2.InsertCommand.Parameters.Add("@num",dataSet11.Tables["bmkfilter"].Rows[i]["KS_H"].ToString());
this.sqlDataAdapter2.InsertCommand.Connection.Open();
this.sqlDataAdapter2.InsertCommand.ExecuteNonQuery();
this.sqlDataAdapter2.InsertCommand.Connection.Close();
this.sqlDataAdapter2.InsertCommand.Parameters.Clear();
dataView1.RowFilter="KS_H='"+dataSet11.Tables["bmkfilter"].Rows[i]["KS_H"].ToString()+"'";
dataView1.Sort="KM_H";
k=1;
foreach(DataRowView myDrv in dataView1)
{
ins_1="a"+k.ToString();
ins_2="a"+Convert.ToString(k+1).ToString();
for(int j=0;j<dataView1.Table.Columns.Count;j++)
{
this.sqlDataAdapter2.UpdateCommand.CommandText="UPDATE gkdata SET "+ins_1+"=@"+ins_1+","+ins_2+"=@"+ins_2+" where num="+myDrv[3].ToString();
this.sqlDataAdapter2.UpdateCommand.Parameters.Add("@"+ins_1+"",myDrv[2].ToString());
this.sqlDataAdapter2.UpdateCommand.Parameters.Add("@"+ins_2+"",myDrv[1].ToString());
this.sqlDataAdapter2.UpdateCommand.Connection.Open();
this.sqlDataAdapter2.UpdateCommand.ExecuteNonQuery();
this.sqlDataAdapter2.UpdateCommand.Connection.Close();
this.sqlDataAdapter2.UpdateCommand.Parameters.Clear();
}
k=k+2;
}
progressBar1.Value++;
label2.Refresh();
label2.Text=Convert.ToString(i+1);
}
label1.Refresh();
label1.Text="处理完毕,已经将"+dataSet11.Tables["bmk"].Rows.Count+"条数据处理成"+dataSet11.Tables["bmkfilter"].Rows.Count+"条数据!";处理10000条数据竟然要差不多1个小时。。是用了dataview速度慢了,还是程序思路有问题,还是要用到线程,请高手指点,,
this.sqlDataAdapter1.SelectCommand.CommandText="SELECT * FROM bmk";
this.sqlDataAdapter1.Fill(dataSet11,"bmk");
this.sqlDataAdapter1.SelectCommand.CommandText="SELECT DISTINCT KS_H FROM bmk ORDER BY KS_H";
this.sqlDataAdapter1.Fill(dataSet11,"bmkfilter");
progressBar1.Maximum=dataSet11.Tables["bmkfilter"].Rows.Count;
label1.Refresh();
label1.Text="共有"+dataSet11.Tables["bmk"].Rows.Count+"条数据,将处理成"+dataSet11.Tables["bmkfilter"].Rows.Count+"条数据";
for(int i=0;i<dataSet11.Tables["bmkfilter"].Rows.Count;i++)
{
Application.DoEvents();
this.sqlDataAdapter2.InsertCommand.CommandText="INSERT INTO gkdata(num) VALUES (@num)";
this.sqlDataAdapter2.InsertCommand.Parameters.Add("@num",dataSet11.Tables["bmkfilter"].Rows[i]["KS_H"].ToString());
this.sqlDataAdapter2.InsertCommand.Connection.Open();
this.sqlDataAdapter2.InsertCommand.ExecuteNonQuery();
this.sqlDataAdapter2.InsertCommand.Connection.Close();
this.sqlDataAdapter2.InsertCommand.Parameters.Clear();
dataView1.RowFilter="KS_H='"+dataSet11.Tables["bmkfilter"].Rows[i]["KS_H"].ToString()+"'";
dataView1.Sort="KM_H";
k=1;
foreach(DataRowView myDrv in dataView1)
{
ins_1="a"+k.ToString();
ins_2="a"+Convert.ToString(k+1).ToString();
for(int j=0;j<dataView1.Table.Columns.Count;j++)
{
this.sqlDataAdapter2.UpdateCommand.CommandText="UPDATE gkdata SET "+ins_1+"=@"+ins_1+","+ins_2+"=@"+ins_2+" where num="+myDrv[3].ToString();
this.sqlDataAdapter2.UpdateCommand.Parameters.Add("@"+ins_1+"",myDrv[2].ToString());
this.sqlDataAdapter2.UpdateCommand.Parameters.Add("@"+ins_2+"",myDrv[1].ToString());
this.sqlDataAdapter2.UpdateCommand.Connection.Open();
this.sqlDataAdapter2.UpdateCommand.ExecuteNonQuery();
this.sqlDataAdapter2.UpdateCommand.Connection.Close();
this.sqlDataAdapter2.UpdateCommand.Parameters.Clear();
}
k=k+2;
}
progressBar1.Value++;
label2.Refresh();
label2.Text=Convert.ToString(i+1);
}
label1.Refresh();
label1.Text="处理完毕,已经将"+dataSet11.Tables["bmk"].Rows.Count+"条数据处理成"+dataSet11.Tables["bmkfilter"].Rows.Count+"条数据!";处理10000条数据竟然要差不多1个小时。。是用了dataview速度慢了,还是程序思路有问题,还是要用到线程,请高手指点,,
this.sqlDataAdapter1.Fill(dataSet11,"bmk");
this.sqlDataAdapter1.SelectCommand.CommandText="SELECT DISTINCT KS_H FROM bmk ORDER BY KS_H";
this.sqlDataAdapter1.Fill(dataSet11,"bmkfilter");
progressBar1.Maximum=dataSet11.Tables["bmkfilter"].Rows.Count;
label1.Refresh();
label1.Text="共有"+dataSet11.Tables["bmk"].Rows.Count+"条数据,将处理成"+dataSet11.Tables["bmkfilter"].Rows.Count+"条数据";
for(int i=0;i<dataSet11.Tables["bmkfilter"].Rows.Count;i++)
{
Application.DoEvents();
this.sqlDataAdapter2.InsertCommand.CommandText="INSERT INTO gkdata(num) VALUES (@num)";
this.sqlDataAdapter2.InsertCommand.Parameters.Add("@num",dataSet11.Tables["bmkfilter"].Rows[i]["KS_H"].ToString());
this.sqlDataAdapter2.InsertCommand.Connection.Open();
this.sqlDataAdapter2.InsertCommand.ExecuteNonQuery();
this.sqlDataAdapter2.InsertCommand.Connection.Close();
this.sqlDataAdapter2.InsertCommand.Parameters.Clear();
dataView1.RowFilter="KS_H='"+dataSet11.Tables["bmkfilter"].Rows[i]["KS_H"].ToString()+"'";
dataView1.Sort="KM_H";
k=1;
foreach(DataRowView myDrv in dataView1)
{
ins_1="a"+k.ToString();
ins_2="a"+Convert.ToString(k+1).ToString();
for(int j=0;j<dataView1.Table.Columns.Count;j++)
{
this.sqlDataAdapter2.UpdateCommand.CommandText="UPDATE gkdata SET "+ins_1+"=@"+ins_1+","+ins_2+"=@"+ins_2+" where num="+myDrv[3].ToString(); this.sqlDataAdapter2.UpdateCommand.Parameters.Add("@"+ins_1+"",myDrv[2].ToString());
this.sqlDataAdapter2.UpdateCommand.Parameters.Add("@"+ins_2+"",myDrv[1].ToString()); this.sqlDataAdapter2.UpdateCommand.Connection.Open(); this.sqlDataAdapter2.UpdateCommand.ExecuteNonQuery(); this.sqlDataAdapter2.UpdateCommand.Connection.Close(); this.sqlDataAdapter2.UpdateCommand.Parameters.Clear();
}
k=k+2;
}
progressBar1.Value++;
label2.Refresh();
label2.Text=Convert.ToString(i+1);
}
label1.Refresh();
label1.Text="处理完毕,已经将"+dataSet11.Tables["bmk"].Rows.Count+"条数据处理成"+dataSet11.Tables["bmkfilter"].Rows.Count+"条数据!";
表bmk
id KH_D KM_H KS_H
1 244 2 1001
2 342 4 1002
3 423 3 1001
4 342 10 1003要把插入表bmkfilter
id num a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 .....
1 1001 2 244 3 423 null null null null null null..
1 1002 4 342 null null null null null null null null.....
..............
id num a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 .....
1 1001 2 244 3 423 null null null null null null..
2 1002 4 342 null null null null null null null null.....id列主键都建立了。其他列的索引倒是没有建
存储过程不太熟。只能用程序做。因为只用一次,但是要求处理时间要几分钟烦啊
如果是读取数据库慢就增加数据库缓存试一试。如果是插入慢,就将主键删除再插入试一试。要是插入10条数据很快就完成了。而后边的插入越来越慢就是数据库主键的问题。每插入一条都要校验是否有重复的。
等全部插入后再对这个表建立主键。(有数据的表估计建立主键速度也不会快)
还有估计就是你的数据库服务器性能比较差。要不然这么点数据,也就几分钟的事情。要是硬件的问题,怎么做都白搭。看看你的数据库服务器的配置,还有终端机的配置。还有服务器内存、CPU使用情况。
我是先把60000万条数据先读入dataset的,这段读取时间很短。也就几秒,读完才用dataview在dataset里面筛选,,这样的话,在原表建立索引有用么?
SELECT DISTINCT KS_H FROM bmk ORDER BY KS_H这部分用datatable来保存外,其他都者可以直接通过数据库统计获得
如果你用的是.NET Framework 2.0 可以利用SqlBulkCopy类批量复制,MSDN帮助文件为:ms-help://MS.MSDNQTR.v80.chs/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/83a7a0d2-8018-4354-97b9-0b1d99f8342b.htm