Каков самый быстрый способ вставки Bulk в Oracle с помощью .NET? Мне нужно передать около 160 тыс. Записей, используя .NET для Oracle. В настоящее время я использую инструкцию insert и выполняю ее в 160K раз. Это займет около 25 минут. Исходные данные хранятся в DataTable, в результате запроса из другой базы данных (MySQL),
Есть ли лучший способ сделать это?
EDIT: я использую System.Data.OracleClient, но согласен принимать решения с использованием другого провайдера (ODP.NET, DevArt и т.д.)
Я загружаю 50 000 записей за 15 или около того секунд, используя привязку массива в ODP.NET
Он работает, повторно вызывая хранимую процедуру, которую вы указываете (и в которой вы можете делать обновления/вставки/удаления), но она передает многочисленные значения параметров из .NET в базу данных в массовом порядке.
Вместо указания единственного значения для каждого параметра хранимой процедуры вы указываете массив значений для каждого параметра.
Oracle передает массивы параметров из .NET в базу данных за один раз, а затем повторно вызывает хранимую процедуру, указанную вами, используя указанные значения параметров.
http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html
/Damian
Недавно я обнаружил специализированный класс, который является удивительным для массовой вставки (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! Он принимает данные как параметр, затем вы вызываете метод WriteTOServer... это очень быстро и эффективно, удачи!
Решение Роба Стивенсона-Леггета медленное, потому что он не связывает свои ценности, но использует string.Format().
Когда вы просите Oracle выполнить SQL-запрос, он начинается с вычисления значения этой инструкции. После этого он выглядит в хеш-таблице, знает ли он это утверждение. Если он уже знает эту инструкцию, он может получить свой путь выполнения из этой хеш-таблицы и выполнить эту инструкцию очень быстро, потому что Oracle выполнил это выражение раньше. Это называется кэшем библиотеки, и он не работает должным образом, если вы не привязываете свои SQL-операторы.
Например, не делайте:
int n;
for (n = 0; n < 100000; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
но do:
OracleParameter myparam = new OracleParameter();
int n;
mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
mycommand.Parameters.Add(myparam);
for (n = 0; n < 100000; n ++)
{
myparam.Value = n + 1;
mycommand.ExecuteNonQuery();
}
Неиспользуемые параметры также могут вызывать SQL-инъекцию.
SQL Server SQLBulkCopy работает быстро. К сожалению, я обнаружил, что OracleBulkCopy намного медленнее. Также у него есть проблемы:
На самом деле System.Data.OracleClient.OracleDataAdapter быстрее, чем OracleBulkCopy, если вы хотите заполнить таблицу небольшими записями, но много строк. Вам нужно настроить размер партии, но оптимальный BatchSize для OracleDataAdapter меньше, чем для OracleBulkCopy.
Я провел тест на компьютере под управлением Windows 7 с исполняемым файлом x86 и 32-разрядным ODP.Net-клиентом 2.112.1.0., OracleDataAdapter является частью System.Data.OracleClient 2.0.0.0. Мой тестовый набор составляет около 600 000 строк с максимальным размером записи. 102 байта (средний размер 43 символа). Источник данных - это текстовый файл размером 25 МБ, который читается в строке за строкой как поток.
В моем тесте я создал таблицу входных данных в фиксированный размер таблицы, а затем использовал OracleBulkCopy или OracleDataAdapter для копирования блока данных на сервер. Я оставил BatchSize как 0 в OracleBulkCopy (так что текущее содержимое таблицы копируется как одна партия) и установите его в размере таблицы в OracleDataAdapter (опять же, чтобы создать единую партию внутри). Лучшие результаты:
Для сравнения:
Такая же клиентская машина, тестовый сервер SQL Server 2008 R2. Для SQL Server объемная копия - это, безусловно, лучший способ. Это не только самый быстрый, но и загрузка сервера также ниже, чем при использовании адаптера данных. Жаль, что OracleBulkCopy не предлагает такого же опыта - API BulkCopy намного проще в использовании, чем DataAdapter.
Очень быстрый способ решить эту проблему - сделать ссылку базы данных из базы данных Oracle в базу данных MySQL. Вы можете создавать ссылки на базы данных для баз данных, отличных от Oracle. После того, как вы создали ссылку на базу данных, вы можете получить данные из базы данных MySQL с помощью... create table mydata как select * from... statement. Это называется гетерогенной связностью. Таким образом, вы не должны ничего делать в своем приложении .net для перемещения данных.
Другой способ - использовать ODP.NET. В ODP.NET вы можете использовать класс OracleBulkCopy.
Но я не думаю, что вставка записей 160k в таблицу Oracle с System.Data.OracleClient займет 25 минут. Я думаю, что вы совершаете слишком много раз. И связываете ли вы свои значения с инструкцией insert с параметрами или объединяете свои значения. Связывание происходит намного быстрее.
Следить за предложением Тео с моими выводами (извинения - в настоящее время у меня нет достаточной репутации, чтобы опубликовать это как комментарий)
Во-первых, это использование нескольких именованных параметров:
String commandString = "INSERT INTO Users (Name, Desk, UpdateTime) VALUES (:Name, :Desk, :UpdateTime)";
using (OracleCommand command = new OracleCommand(commandString, _connection, _transaction))
{
command.Parameters.Add("Name", OracleType.VarChar, 50).Value = strategy;
command.Parameters.Add("Desk", OracleType.VarChar, 50).Value = deskName ?? OracleString.Null;
command.Parameters.Add("UpdateTime", OracleType.DateTime).Value = updated;
command.ExecuteNonQuery();
}
Однако я не видел изменения скорости между:
Я использую System.Data.OracleClient, удаляя и вставляя 2500 строк внутри транзакции
Вы не должны делать каждую вставку, потому что фиксация занимает много времени.
Какой провайдер вы используете для подключения своего приложения .NET к базе данных oracle? Используете ли вы ODP.NET или поставщика Devart (он же провайдер Corelab), или вы используете поставщика Microsoft для Oracle (System.Data.OracleClient)?
Oracle говорит (http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)
SQL * Loader - основной метод для быстрое заполнение таблиц Oracle данные из внешних файлов
Мой опыт заключается в том, что их загрузчик загружает свои таблицы быстрее, чем что-либо еще.
Поиск связанных примеров несколько сбивает с толку, я разработал некоторый код, который демонстрирует вставку рабочего массива в тестовую таблицу (jkl_test). Здесь таблица:
create table jkl_test (id number(9));
Вот .Net-код для простого консольного приложения, которое подключается к Oracle с использованием ODP.Net и вставляет массив из 5 целых чисел:
using Oracle.DataAccess.Client;
namespace OracleArrayInsertExample
{
class Program
{
static void Main(string[] args)
{
// Open a connection using ODP.Net
var connection = new OracleConnection("Data Source=YourDatabase; Password=YourPassword; User Id=YourUser");
connection.Open();
// Create an insert command
var command = connection.CreateCommand();
command.CommandText = "insert into jkl_test values (:ids)";
// Set up the parameter and provide values
var param = new OracleParameter("ids", OracleDbType.Int32);
param.Value = new int[] { 22, 55, 7, 33, 11 };
// This is critical to the process; in order for the command to
// recognize and bind arrays, an array bind count must be specified.
// Set it to the length of the array.
command.ArrayBindCount = 5;
command.Parameters.Add(param);
command.ExecuteNonQuery();
}
}
}
Я предполагаю, что OracleBulkCopy является одним из самых быстрых способов. Мне было трудно узнать, что мне нужна новая версия ODAC. Ср Где находится тип [Oracle.DataAccess.Client.OracleBulkCopy]?
Вот полный код PowerShell для копирования из запроса в подходящую существующую таблицу Oracle. Я попробовал Sql-Server источник данных, но другие действующие источники OLE-DB перейдут к.
if ($ora_dll -eq $null)
{
"Load Oracle dll"
$ora_dll = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
$ora_dll
}
# sql-server or Oracle source example is sql-server
$ConnectionString ="server=localhost;database=myDatabase;trusted_connection=yes;Provider=SQLNCLI10;"
# Oracle destination
$oraClientConnString = "Data Source=myTNS;User ID=myUser;Password=myPassword"
$tableName = "mytable"
$sql = "select * from $tableName"
$OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$OLEDBConn.open()
$readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
$readcmd.CommandTimeout = '300'
$da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)
$OLEDBConn.close()
#Write-Output $dt
if ($dt)
{
try
{
$bulkCopy = new-object ("Oracle.DataAccess.Client.OracleBulkCopy") $oraClientConnString
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.BatchSize = 5000
$bulkCopy.BulkCopyTimeout = 10000
$bulkCopy.WriteToServer($dt)
$bulkcopy.close()
$bulkcopy.Dispose()
}
catch
{
$ex = $_.Exception
Write-Error "Write-DataTable$($connectionName):$ex.Message"
continue
}
}
Кстати: я использую это для копирования таблицы с столбцами CLOB. Я не получил этого, чтобы работать с использованием связанных серверов cf. вопрос о dba. Я не возвращал связанные ссылки с новым ODAC.
Если вы используете неуправляемый клиент oracle (Oracle.DataAccess), самым быстрым способом является использование OracleBulkCopy, как указал Тарик.
Если вы используете последний управляемый клиент oracle (Oracle.ManagedDataAccess), самым быстрым способом является использование привязки массива, как указал Дамиен. Если вы хотите, чтобы ваш код приложения очищался от специфики привязки к массиву, вы можете написать собственную реализацию OracleBulkCopy с использованием привязки массива.
Вот пример использования из реального проекта:
var bulkWriter = new OracleDbBulkWriter();
bulkWriter.Write(
connection,
"BULK_WRITE_TEST",
Enumerable.Range(1, 10000).Select(v => new TestData { Id = v, StringValue=v.ToString() }).ToList());
Записи в 10 000 вставляются в 500 мс!
Вот реализация:
public class OracleDbBulkWriter : IDbBulkWriter
{
public void Write<T>(IDbConnection connection, string targetTableName, IList<T> data, IList<ColumnToPropertyMapping> mappings = null)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (string.IsNullOrEmpty(targetTableName))
{
throw new ArgumentNullException(nameof(targetTableName));
}
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
if (mappings == null)
{
mappings = GetGenericMappings<T>();
}
mappings = GetUniqueMappings<T>(mappings);
Dictionary<string, Array> parameterValues = InitializeParameterValues<T>(mappings, data.Count);
FillParameterValues(parameterValues, data);
using (var command = CreateCommand(connection, targetTableName, mappings, parameterValues))
{
command.ExecuteNonQuery();
}
}
private static IDbCommand CreateCommand(IDbConnection connection, string targetTableName, IList<ColumnToPropertyMapping> mappings, Dictionary<string, Array> parameterValues)
{
var command = (OracleCommandWrapper)connection.CreateCommand();
command.ArrayBindCount = parameterValues.First().Value.Length;
foreach(var mapping in mappings)
{
var parameter = command.CreateParameter();
parameter.ParameterName = mapping.Column;
parameter.Value = parameterValues[mapping.Property];
command.Parameters.Add(parameter);
}
command.CommandText = [email protected]"insert into {targetTableName} ({string.Join(",", mappings.Select(m => m.Column))}) values ({string.Join(",", mappings.Select(m => $":{m.Column}")) })";
return command;
}
private IList<ColumnToPropertyMapping> GetGenericMappings<T>()
{
var accessor = TypeAccessor.Create(typeof(T));
var mappings = accessor.GetMembers()
.Select(m => new ColumnToPropertyMapping(m.Name, m.Name))
.ToList();
return mappings;
}
private static IList<ColumnToPropertyMapping> GetUniqueMappings<T>(IList<ColumnToPropertyMapping> mappings)
{
var accessor = TypeAccessor.Create(typeof(T));
var members = new HashSet<string>(accessor.GetMembers().Select(m => m.Name));
mappings = mappings
.Where(m => m != null && members.Contains(m.Property))
.GroupBy(m => m.Column)
.Select(g => g.First())
.ToList();
return mappings;
}
private static Dictionary<string, Array> InitializeParameterValues<T>(IList<ColumnToPropertyMapping> mappings, int numberOfRows)
{
var values = new Dictionary<string, Array>(mappings.Count);
var accessor = TypeAccessor.Create(typeof(T));
var members = accessor.GetMembers().ToDictionary(m => m.Name);
foreach(var mapping in mappings)
{
var member = members[mapping.Property];
values[mapping.Property] = Array.CreateInstance(member.Type, numberOfRows);
}
return values;
}
private static void FillParameterValues<T>(Dictionary<string, Array> parameterValues, IList<T> data)
{
var accessor = TypeAccessor.Create(typeof(T));
for (var rowNumber = 0; rowNumber < data.Count; rowNumber++)
{
var row = data[rowNumber];
foreach (var pair in parameterValues)
{
Array parameterValue = pair.Value;
var propertyValue = accessor[row, pair.Key];
parameterValue.SetValue(propertyValue, rowNumber);
}
}
}
}
ПРИМЕЧАНИЕ. В этой реализации используется пакет Fastmember для оптимизации доступа к свойствам (намного быстрее, чем отражение).