前两天接到一个需求——需要编程将SQL Server中的数据插入至Oracle数据大约有多万条记录开始的时候我采取了直接构建SQL插入的方式结果耗时太长为了提高性能我上网找了资料最终采用DataAdapter批量插入至Oracle提高了性能
代码如下
一直接构建SQL语句插入
VBNET
swStart()
Read ZJ from SQL Server
Dim sqlCmd As New SqlCommand()
sqlCmdConnection = sqlConnection
sqlCmdCommandText = SELECT * FROM ZJ
Dim sqlDr As SqlDataReader
sqlDr = sqlCmdExecuteReader()
Dim cmdInsertZJ As New OracleCommand()
cmdInsertZJConnection = oraConnection
cmdInsertZJCommandText = BuildSQLStatement(SQLTypeInsertzj)
Dim plantLever material oldMaterialNum materialDescription As Object
While sqlDrRead()
plantLever = ReadSqlDataReader(sqlDr )
material = ReadSqlDataReader(sqlDr )
oldMaterialNum = ReadSqlDataReader(sqlDr )
materialDescription = ReadSqlDataReader(sqlDr )
Insert to Oracle table ZJ
cmdInsertZJParametersAddWithValue(:plantLever plantLever)
cmdInsertZJParametersAddWithValue(:material material)
cmdInsertZJParametersAddWithValue(:oldMaterialNum oldMaterialNum)
cmdInsertZJParametersAddWithValue(:materialDescription materialDescription)
cmdInsertZJExecuteNonQuery()
End While
swStop()
LogerInfo(Reading zj form sql sever used swElapsedTotalSecondsToString())
二采用DataAdapter实现批量插入
VBNET
swStart()
Read ZJ from SQL Server
Dim sqlCmd As New SqlCommand()
sqlCmdConnection = sqlConnection
sqlCmdCommandText = SELECT * FROM ZJ
Dim sqlDr As SqlDataReader
sqlDr = sqlCmdExecuteReader()
Dim cmdInsertZJ As New OracleCommand()
cmdInsertZJConnection = oraConnection
cmdInsertZJCommandText = BuildSQLStatement(SQLTypeInsertzj)
Dim dtSqlZJ As New DataTable
dtSqlZJColumnsAdd(plantLever)
dtSqlZJColumnsAdd(material)
dtSqlZJColumnsAdd(oldMaterialNum)
dtSqlZJColumnsAdd(materialDescription)
Dim plantLever material oldMaterialNum materialDescription As Object
While sqlDrRead()
plantLever = ReadSqlDataReader(sqlDr )
material = ReadSqlDataReader(sqlDr )
oldMaterialNum = ReadSqlDataReader(sqlDr )
materialDescription = ReadSqlDataReader(sqlDr )
dtSqlZJRowsAdd(plantLever material oldMaterialNum materialDescription)
End While
swStop()
LogerInfo(Reading zj form sql sever used swElapsedTotalSecondsToString())
swStart()
Dim oraDa As New OracleDataAdapter()
oraDaInsertCommand = cmdInsertZJ
oraDaInsertCommandParametersAdd(:plantLever OracleTypeChar plantLever)
oraDaInsertCommandParametersAdd(:material OracleTypeChar material)
oraDaInsertCommandParametersAdd(:oldMaterialNum OracleTypeChar oldMaterialNum)
oraDaInsertCommandParametersAdd(:materialDescription OracleTypeChar materialDescription)
oraDaInsertCommandUpdatedRowSource = UpdateRowSourceNone
oraDaUpdateBatchSize = Adjust the batch size based on testing result
oraDaUpdate(dtSqlZJ)
swStop()
LogerInfo(Insert to oracle used swElapsedTotalSecondsToString())
在我的环境中批量插入万笔记录用时大约s左右
貌似SQL Server中net驱动程序提供了SqlBulkCopy类来提高大量数据导入的性能有需要的朋友可以查下MSDN
