关于大数据量异构数据间数据传输及简单的多线程
这几天写东西遇到了大量数据从informix导出到sql的问题,开始的反映就是使用DTS,但是需要在程序中每天重复这个动作,而且有些表是要覆盖导出的,有些表则是增量导出的,在DTS中不好控制,而且客户也不希望使用DTS来实现,所以就直接把数据取到内存中的DataSet,使用DataAdapter来insert到sql中也不错,开始测试了一万条左右的数据,列数为100列左右,竟然使用了差不多一分种的时间,这样算起来的话,我岂不是要导个200分钟?而且这是取出来放到内存中的,数据不大还可以,否则再多的内存也不够用的。
否定DataSet,那就使用DataReader来实现吧,这样内存中只会存放当前一条记录,而且DataReader是向后只读的,速度很快,把每读出来的记录Insert到Sql中,这样速度还是和使用DataAdapter差不多,大概能快那么一点。
这样虽然实现了,速度还是不能让人满意,于是使用了多线程,因为是从多个表中取数据,insert到sql对应的表中,所以对于每个表开了一个线程,由于使用的是oledb连接的informix数据库,使用了事务,而提示不支持并行事务,所以对于每个线程创建了一个informix的连接,最后速度当然是大大的提高了。
因为在所有线程执行完毕后会有其他的操作,所以需要使用join()方法来等待线程结束。
补充一点,不要忘记使用lock(this),来进行同步。
搞定,不过最后不要忘记关闭informix的连接。
否定DataSet,那就使用DataReader来实现吧,这样内存中只会存放当前一条记录,而且DataReader是向后只读的,速度很快,把每读出来的记录Insert到Sql中,这样速度还是和使用DataAdapter差不多,大概能快那么一点。
1
public void InsertIIM(OleDbTransaction tIfx,SqlTransaction tSql)
2
{
3
//string strSql = "select * from IIM where serialcolumn not in (select pk from tabdtsc where tab='IIM')";
4
string strSql = "select * from IIM ";
5![](/Images/OutliningIndicators/InBlock.gif)
6
OleDbCommand olecmd = new OleDbCommand(strSql,this.cnnIfx,tIfx);
7![](/Images/OutliningIndicators/InBlock.gif)
8
System.Data.OleDb.OleDbDataReader oledr;
9
oledr = olecmd.ExecuteReader();
10
11
SqlCommand cmd = new SqlCommand();
12
cmd.Transaction = tSql;
13
cmd.Connection = this.cnn;
14![](/Images/OutliningIndicators/InBlock.gif)
15
cmd.CommandText = "truncate table IIM ";
16
cmd.ExecuteNonQuery ();
17![](/Images/OutliningIndicators/InBlock.gif)
18
string strIntSql = "";
19
object[] objColumn = new object[oledr.FieldCount];
20
while (oledr.Read())
21
{
22
strIntSql ="insert into iim values(";
23
oledr.GetValues(objColumn);
24
for(int i=0;i<objColumn.Length-1;i++)
25
{
26
if(objColumn[i].GetType().ToString()=="System.DBNull")
27
strIntSql+=" null,";
28
else
29
strIntSql+="'"+objColumn[i].ToString()+"',";
30
}
31![](/Images/OutliningIndicators/InBlock.gif)
32
33
if(objColumn[objColumn.Length-1].GetType().ToString()=="System.DBNull")
34
strIntSql+=" null)";
35
else
36
strIntSql+="'"+objColumn[objColumn.Length-1].ToString()+"')";
37![](/Images/OutliningIndicators/InBlock.gif)
38
cmd.CommandText = strIntSql;
39
cmd.ExecuteNonQuery();
40
}
41
oledr.Close();
42
}
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/InBlock.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/InBlock.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/InBlock.gif)
24
![](/Images/OutliningIndicators/InBlock.gif)
25
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
26
![](/Images/OutliningIndicators/InBlock.gif)
27
![](/Images/OutliningIndicators/InBlock.gif)
28
![](/Images/OutliningIndicators/InBlock.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
31
![](/Images/OutliningIndicators/InBlock.gif)
32
![](/Images/OutliningIndicators/InBlock.gif)
33
![](/Images/OutliningIndicators/InBlock.gif)
34
![](/Images/OutliningIndicators/InBlock.gif)
35
![](/Images/OutliningIndicators/InBlock.gif)
36
![](/Images/OutliningIndicators/InBlock.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
Thread[] m_thdX = new Thread[5];
2![](/Images/OutliningIndicators/None.gif)
3
m_thdX[0] = new Thread(new ThreadStart(InsertFSO));
4
m_thdX[0].Start();
5
6
m_thdX[1] = new Thread(new ThreadStart(InsertILI));
7
m_thdX[1].Start();
8![](/Images/OutliningIndicators/None.gif)
9
m_thdX[2] = new Thread(new ThreadStart(InsertMBM));
10
m_thdX[2].Start();
11
12
m_thdX[3] = new Thread(new ThreadStart(InsertIIM));
13
m_thdX[3].Start();
14![](/Images/OutliningIndicators/None.gif)
15
m_thdX[4] = new Thread(new ThreadStart(InsertITH));
16
m_thdX[4].Start();
17![](/Images/OutliningIndicators/None.gif)
18
m_thdX[0].Join();
19
m_thdX[1].Join();
20
m_thdX[2].Join();
21
m_thdX[3].Join();
22
m_thdX[4].Join();
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
补充一点,不要忘记使用lock(this),来进行同步。
搞定,不过最后不要忘记关闭informix的连接。
posted on 2005-07-20 11:18 一切为了DotNet 阅读(792) 评论(0) 编辑 收藏 举报