Массовая вставка в Oracle с использованием.NET
Какой самый быстрый способ сделать массовую вставку в Oracle, используя.NET? Мне нужно перенести около 160 тыс. Записей с помощью.NET в Oracle. В настоящее время я использую оператор вставки и выполняю его 160 тыс. Раз. Это займет около 25 минут. Исходные данные хранятся в DataTable в результате запроса из другой базы данных (MySQL),
Есть ли лучший способ сделать это?
РЕДАКТИРОВАТЬ: в настоящее время я использую System.Data.OracleClient, но готов принять решения с использованием другого поставщика (ODP.NET, DevArt и т. Д.)
10 ответов
Я загружаю 50000 записей за 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, она начинается с вычисления значения has этого оператора. После этого он смотрит в хеш-таблицу, знает ли он уже этот оператор. Если он уже знает свой оператор, он может извлечь свой путь выполнения из этой хеш-таблицы и выполнить этот оператор очень быстро, потому что Oracle выполнял этот оператор раньше. Это называется библиотечным кешем, и он не работает должным образом, если вы не связываете свои операторы sql.
Например, не делайте:
int n;
for (n = 0; n < 100000; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
но делать:
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.
SQLBulkCopy для SQL Server невероятно быстр. К сожалению, я обнаружил, что OracleBulkCopy намного медленнее. Также у него есть проблемы:
- Вы должны быть уверены, что ваши входные данные чисты, если вы планируете использовать OracleBulkCopy. Если происходит нарушение первичного ключа, ORA-26026 поднимается, и это кажется неисправимым. Попытка перестроить индекс не помогает, и любая последующая вставка в таблицу завершается неудачно, также обычные вставки.
- Даже если данные чистые, я обнаружил, что OracleBulkCopy иногда застревает внутри WriteToServer. Проблема, похоже, зависит от размера партии. В моих тестовых данных проблема повторялась в той же самой точке моего теста, когда я повторяю это. Используйте больший или меньший размер пакета, и проблема не возникает. Я вижу, что скорость более нерегулярна при больших размерах пакетов, это указывает на проблемы, связанные с управлением памятью.
На самом деле 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 (опять же, это должно создать один пакет внутри). Лучшие результаты:
- OracleBulkCopy: размер таблицы = 500, общая продолжительность 4'22 "
- OracleDataAdapter: размер таблицы = 100, общая продолжительность 3'03"
Для сравнения:
- SqlBulkCopy: размер таблицы = 1000, общая продолжительность 0'15 "
- SqlDataAdapter: размер таблицы = 1000, общая продолжительность 8'05"
Тот же клиентский компьютер, тестовый сервер - SQL Server 2008 R2. Для SQL Server массовое копирование - это, безусловно, лучший способ. Это не только в целом быстрее, но нагрузка на сервер также ниже, чем при использовании адаптера данных. Жаль, что OracleBulkCopy не предлагает такой же интерфейс - API BulkCopy гораздо проще в использовании, чем DataAdapter.
Действительно быстрый способ решить эту проблему - создать ссылку на базу данных из базы данных Oracle на базу данных MySQL. Вы можете создавать ссылки на базы данных, не связанные с Oracle. После того, как вы создали ссылку на базу данных, вы можете извлечь свои данные из базы данных MySQL с помощью... создать таблицу mydata в виде оператора select * from .... Это называется гетерогенной связностью. Таким образом, вам не нужно ничего делать в вашем приложении.net для перемещения данных.
Другой способ - использовать ODP.NET. В ODP.NET вы можете использовать OracleBulkCopy-класс.
Но я не думаю, что вставка 160 тыс. Записей в таблицу Oracle с System.Data.OracleClient должна занять 25 минут. Я думаю, что вы делаете слишком много раз. И вы привязываете свои значения к оператору вставки с параметрами или объединяете свои значения. Связывание намного быстрее.
Находя связанные примеры несколько запутанными, я разработал некоторый код, который демонстрирует вставку рабочего массива в тестовую таблицу (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();
}
}
}
Чтобы продолжить предложение Тео с моими выводами (извинения - в настоящее время у меня недостаточно репутации, чтобы опубликовать это в качестве комментария)
Во-первых, это как использовать несколько именованных параметров:
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();
}
Тем не менее, я не видел изменений в скорости между:
- создание новой commandString для каждой строки (String.Format)
- построение теперь параметризованной commandString для каждой строки
- использование единственной commandString и изменение параметров
Я использую System.Data.OracleClient, удаляя и вставляя 2500 строк внутри транзакции
Oracle говорит ( http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)
SQL * Loader - основной метод быстрого заполнения таблиц Oracle данными из внешних файлов.
Мой опыт показывает, что их загрузчик загружает свои таблицы быстрее, чем что-либо еще.
Если вы используете неуправляемый клиент 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());
10K записей вставляются за 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 = $@"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 для оптимизированного доступа к свойствам (намного быстрее, чем отражение)
Вы не должны фиксировать каждую вставку, потому что фиксация занимает много времени.
Какой провайдер вы используете для подключения вашего приложения.NET к базе данных Oracle? Используете ли вы ODP.NET или поставщика Devart (он же поставщик Corelab) или поставщик Microsoft для Oracle (System.Data.OracleClient)?
Я предполагаю, что 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. Я не получил это работать с использованием связанных серверов ср. вопрос по дба. Я не пытался повторно связать подачу с новым ODAC.