BulkCopy频繁执行产生的性能问题

问题现象：

完整的SQL脚本如下：

select ac.constraint_name key_name, acc.column_name key_col, 1 from all_cons_columns acc, all_constraints ac where acc.owner = ac.owner and acc.constraint_name = ac.constraint_name and acc.table_name = ac.table_name and ac.constraint_type = 'P' and ac.owner = user and ac.table_name = :TableName order by acc.constraint_name

 

查阅咨询显示，使用OracleCommandBuilder对象后会产生该语句，而bulkcopy正是使用了该对象。

The query you have mentioned is done by OracleCommandBuilder to get the schema information from the database.

Once queried, the instance of OracleCommandBuilder creates an internal cache of it and reuses it.

But when you create new instance of OracleCommandBuilder it will run this query again.

 

To avoid it, create OracleCommandBuilder once and reuse it. For example, you can define a static variable to hold the instance,

or you can create it once in caller class and pass it as an argument every use.

https://social.msdn.microsoft.com/Forums/en-us/53defa9c-82f2-48b4-b923-6afbc064928c/oraclecommandbuilder-and-repetitive-internal-query?forum=adodotnetdataproviders

 

按照提示修改每次bulkcopy的批量大小后，问题消失。