Массовая вставка в Oracle с использованием .NET
Мы можем попробовать это
recyclerView.getLayoutManager().smoothScrollToPosition(recyclerView,new RecyclerView.State(), recyclerView.getAdapter().getItemCount());
10 ответов
Я загружаю 50 000 записей за 15 секунд или около того, используя привязку массива в ODP.NET.
Он работает, многократно вызывая указанную вами хранимую процедуру (и в которой вы можете выполнять обновления / вставки / удаления). , но он передает несколько значений параметров из .NET в базу данных в большом количестве.
Вместо указания отдельного значения для каждого параметра хранимой процедуры вы указываете массив значений для каждого параметра.
Oracle передает массивы параметров из .NET в базу данных за один раз, а затем повторно вызывает хранимую процедуру, указанную вами, используя указанные вами значения параметров.
http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html
/ Дамиан
Недавно я обнаружил специализированный класс, который отлично подходит для массовой вставки (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! Он принимает в качестве параметра datatable, затем вызывается метод 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 минут. Я думаю, что вы делаете слишком много раз. И вы связываете свои значения с оператором вставки с параметрами или объединяете свои значения. Связывание намного быстрее.
Чтобы продолжить предложение Тео с моими выводами (извинения - у меня в настоящее время недостаточно репутации, чтобы опубликовать это в качестве комментария)
Во-первых, это как использовать несколько именованных параметров:
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 для каждого row
- с использованием одной commandString и изменением параметров
Я использую System.Data.OracleClient, удаляя и вставляя 2500 строк внутри транзакции
Oracle говорит ( http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html )
Загрузчик SQL * является основной метод быстрого заполнения таблиц 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 = Я думаю, что OracleBulkCopy - один из самых быстрых способов. У меня были некоторые проблемы, чтобы узнать, что мне нужна новая версия ODAC. Ср Где находится тип [Oracle.DataAccess.Client.OracleBulkCopy]?
Вот полный код PowerShell для копирования из запроса в подходящую существующую таблицу Oracle. Я пытался использовать Sql-Server как источник данных, но другие действительные источники OLE-DB перейдут на.
[110] Кстати: я использую это для копирования таблицы со столбцами CLOB. Я не получил это работать с использованием связанных серверов ср. вопрос по дба . Я не пытался повторно связать подачу с новым ODAC.
.Exception
Write-Error "Write-DataTable$($connectionName):$ex.Message"
continue
}
}
Кстати: я использую это для копирования таблицы со столбцами CLOB. Я не получил это работать с использованием связанных серверов ср. вопрос по дба . Я не пытался повторно связать подачу с новым 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());
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 для оптимизированного доступа к свойствам (намного быстрее, чем отражение)