熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle數據的批量插入

2013-11-13 22:12:39  來源: Oracle 

  前兩天接到一個需求——需要編程將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


From:http://tw.wingwit.com/Article/program/Oracle/201311/18480.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.