Массовая вставка в Oracle с использованием .NET

Действительно быстрый способ решить эту проблему - установить связь с базой данных Oracle с базой данных MySQL. Вы можете создавать ссылки на базы данных, не относящиеся к Oracle. После того, как вы создали ссылку на базу данных, вы можете получить свои данные из базы данных MySQL с помощью оператора ... create table mydata as select * from ... Это называется гетерогенным подключением. Таким образом, вам не нужно ничего делать в вашем .NET-приложении для перемещения данных.

Другой способ - использовать ODP.NET. В ODP.NET вы можете использовать класс OracleBulkCopy.

Но я не думаю, что вставка 160 тыс. Записей в таблицу Oracle с помощью System.Data.OracleClient должна занять 25 минут. Я думаю, вы совершаете слишком много раз. И вы привязываете свои значения к оператору вставки с параметрами или объединяете свои значения. Связывание происходит намного быстрее.

Решение Роба Стивенсона-Леггета происходит медленно, потому что он не связывает свои значения, а использует 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.

Я загружаю 50000 записей примерно за 15 секунд, используя привязку массива в ODP.NET.

Он работает, многократно вызывая указанную вами хранимую процедуру (и в которой вы можете выполнять обновления / вставки / удаления), но он передает несколько значений параметров из .NET в базу данных массово.

Вместо того, чтобы указывать одно значение для каждого параметра хранимой процедуры, вы указываете множество значений для каждого параметра.

Oracle передает массивы параметров из .NET в базу данных за один раз, а затем многократно вызывает указанную вами хранимую процедуру, используя указанные вами значения параметров.

http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

/ Дамиан

Оракул говорит (http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)

SQL*Loader is the primary method for quickly populating Oracle tables with data from external files

Мой опыт показывает, что их загрузчик загружает их таблицы быстрее, чем что-либо другое.

Чтобы дополнить предложение Тео моими выводами (извинения - у меня в настоящее время недостаточно репутации, чтобы опубликовать это в качестве комментария)

Во-первых, вот как использовать несколько именованных параметров:

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 строк внутри транзакции

Недавно я обнаружил специальный класс, который отлично подходит для массовой вставки (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! Он принимает данные в качестве параметра, затем вы вызываете метод WriteTOServer ... он очень быстрый и эффективный, удачи !!

Я предполагаю, что 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. Я не заставил это работать с использованием связанных серверов ср. вопрос по dba. Я не пытался повторно использовать связанные серверы с новым ODAC.

SQLBulkCopy SQL Server невероятно быстр. К сожалению, я обнаружил, что OracleBulkCopy работает намного медленнее. Также есть проблемы:

• Вы должны быть очень уверены, что ваши входные данные чистые, если вы планируете используйте OracleBulkCopy. Если происходит нарушение первичного ключа, ORA-26026 поднят, и кажется, что его невозможно восстановить. Пытаясь восстановить index не помогает, и любая последующая вставка в таблицу не выполняется, тоже нормальные вставки.
• Даже если данные чистые, я обнаружил, что 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 не предлагает такой же опыт - BulkCopy API намного проще в использовании, чем DataAdapter.

Находя связанные примеры несколько запутанными, я разработал код, демонстрирующий вставку рабочего массива в тестовую таблицу (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();
        }
    }
}

Если вы используете неуправляемый клиент 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К записей вставляются за 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 для оптимизированного доступа к свойствам (намного быстрее, чем отражение)