Inserción masiva en Oracle usando .NET

¿Cuál es la forma más rápida de hacer inserciones masivas en Oracle utilizando .NET? Necesito transferir aproximadamente 160K loggings usando .NET a Oracle. Actualmente, estoy usando Insertar statement y ejecutarlo 160K veces. Tarda unos 25 minutos en completarse. Los datos de origen se almacenan en una DataTable, como resultado de la consulta desde otra database (MySQL),

¿Hay alguna forma mejor de hacer esto?

EDITAR : actualmente estoy usando System.Data.OracleClient, pero estoy dispuesto a aceptar soluciones usando otro proveedor (ODP.NET, DevArt, etc.)

Estoy cargando 50,000 loggings en 15 segundos aproximadamente usando Array Binding en ODP.NET

Funciona al invocar repetidamente un procedimiento almacenado que usted especifique (y en el que puede hacer actualizaciones / inserciones / eliminaciones), pero transfiere los valores de múltiples parameters de .NET a la database de forma masiva.

En lugar de especificar un único valor para cada parámetro en el procedimiento almacenado, especifica una matriz de valores para cada parámetro.

Oracle pasa las matrices de parameters de .NET a la database de una sola vez, y luego invoca repetidamente el procedimiento almacenado que especifique usando los valores de parameters que ha especificado.

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

/ Damian

Recientemente descubrí una class especializada que es increíble para una inserción masiva (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! Toma una tabla de datos como parámetro, luego llama al método WriteTOServer … es muy rápido y efectivo, ¡buena suerte!

La solución de Rob Stevenson-Legget es lenta porque no enlaza sus valores, pero usa string.Format ().

Cuando le pides a Oracle que ejecute un enunciado sql, comienza con el cálculo del valor de esta statement. Después de eso, busca en una tabla hash si ya conoce esta afirmación. Si ya conoce su statement, puede recuperar su ruta de ejecución desde esta tabla hash y ejecutar esta statement muy rápido porque Oracle ha ejecutado esta statement antes. Esto se denomina memory caching de la biblioteca y no funciona correctamente si no vincula sus instrucciones sql.

Por ejemplo, no hagas:

int n;

for (n = 0; n < 100000; n ++) { mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1); mycommand.ExecuteNonQuery(); } 

pero hazlo

  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(); } 

No usar parameters también puede causar inyección sql.

SQLBulkCopy de SQL Server es deslumbrantemente rápido. Desafortunadamente, descubrí que OracleBulkCopy es mucho más lento. También tiene problemas:

  • Debe estar seguro de que sus datos de input están limpios si planea usar OracleBulkCopy. Si se produce una violación de key principal, se genera un ORA-26026 y parece ser irrecuperable. Intentar rebuild el índice no ayuda y cualquier inserción subsiguiente en la tabla falla, también insertos normales.
  • Incluso si los datos están limpios, descubrí que OracleBulkCopy a veces se queda atascado dentro de WriteToServer. El problema parece depender del tamaño del lote. En mis datos de testing, el problema ocurriría exactamente en el mismo punto de mi testing cuando lo repita. Use un tamaño de lote mayor o menor, y el problema no ocurre. Veo que la velocidad es más irregular en tamaños de lote más grandes, esto apunta a problemas relacionados con la administración de la memory.

En realidad System.Data.OracleClient.OracleDataAdapter es más rápido que OracleBulkCopy si desea llenar una tabla con pequeños loggings pero muchas filas. Sin embargo, necesita ajustar el tamaño del lote, BatchSize óptimo para OracleDataAdapter es más pequeño que para OracleBulkCopy.

Ejecuté mi testing en una máquina con Windows 7 con un ejecutable x86 y el cliente ODP.Net de 32 bits 2.112.1.0. . OracleDataAdapter es parte de System.Data.OracleClient 2.0.0.0. Mi set de testing es de aproximadamente 600,000 filas con un tamaño de logging de máx. 102 bytes (tamaño promedio 43 caracteres). La fuente de datos es un file de text de 25 MB, leído en línea por línea como una secuencia.

En mi testing, construí la tabla de datos de input en un tamaño de tabla fijo y luego utilicé OracleBulkCopy u OracleDataAdapter para copyr el bloque de datos al server. Dejé BatchSize como 0 en OracleBulkCopy (para que el contenido de la tabla actual se copyra como un lote) y lo configuré en el tamaño de la tabla en OracleDataAdapter (nuevamente, eso debería crear un solo lote internamente). Mejores resultados:

  • OracleBulkCopy: tamaño de la tabla = 500, duración total 4'22 "
  • OracleDataAdapter: tamaño de tabla = 100, duración total 3'03 "

Para comparacion:

  • SqlBulkCopy: tamaño de la tabla = 1000, duración total 0'15 "
  • SqlDataAdapter: tamaño de la tabla = 1000, duración total 8'05 "

La misma máquina cliente, el server de testing es SQL Server 2008 R2. Para SQL Server, la copy masiva es claramente la mejor manera de hacerlo. No solo es más rápido en general, sino que la carga del server también es menor que cuando se usa el adaptador de datos. Es una lástima que OracleBulkCopy no ofrezca la misma experiencia: la API de BulkCopy es mucho más fácil de usar que DataAdapter.

Una forma realmente rápida de resolver este problema es hacer un enlace de database desde la database Oracle a la database MySQL. Puede crear enlaces de bases de datos a bases de datos que no sean de Oracle. Después de haber creado el enlace de la database, puede recuperar sus datos de la database MySQL con una … create table mydata como select * from … statement. Esto se llama conectividad heterogénea. De esta forma, no tiene que hacer nada en su aplicación .net para mover los datos.

Otra forma es usar ODP.NET. En ODP.NET puede usar la class OracleBulkCopy.

Pero no creo que insert 160k loggings en una tabla de Oracle con System.Data.OracleClient debería tomar 25 minutos. Creo que te comprometes muchas veces. ¿Y vincula sus valores a la instrucción de inserción con parameters o concatena sus valores? La unión es mucho más rápida.

No debe comprometer cada inserción porque comprometer lleva mucho time.

¿Qué proveedor usas para conectar tu aplicación .NET a la database Oracle? ¿Utiliza ODP.NET o el proveedor Devart (también conocido como proveedor de corelab) o utiliza el proveedor de Microsoft para Oracle (System.Data.OracleClient)?

Oracle dice ( http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html )

SQL * Loader es el método principal para llenar rápidamente las tablas de Oracle con datos de files externos

Mi experiencia es que su cargador carga sus tablas más rápido que cualquier otra cosa.

Para dar seguimiento a la sugerencia de Theo con mis hallazgos (disculpas: actualmente no tengo suficiente reputación para publicar esto como un comentario)

Primero, así es como usar varios parameters nombrados:

 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(); } 

Sin embargo, no vi ninguna variación en la velocidad entre:

  • construyendo un nuevo commandString para cada fila (String.Format)
  • construyendo un commandString ahora parametrizado para cada fila
  • usando un solo command String y cambiando los parameters

Estoy usando System.Data.OracleClient, eliminando e insertando 2500 filas dentro de una transacción

Al encontrar los ejemplos enlazados algo confusos, ideé un código que muestra una inserción de matriz en funcionamiento en una tabla de testing (jkl_test). Aquí está la tabla:

 create table jkl_test (id number(9)); 

Aquí está el código .Net para una aplicación de console simple que se conecta a Oracle usando ODP.Net e inserta una matriz de 5 integers:

 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(); } } } 

Supongo que OracleBulkCopy es una de las forms más rápidas. Tuve algunos problemas para aprender, que necesitaba una nueva versión de ODAC. Cf. ¿Dónde está el tipo [Oracle.DataAccess.Client.OracleBulkCopy]?

Aquí está el código completo de PowerShell para copyr de una consulta en una tabla Oracle adecuada. Probé Sql-Server una fuente de datos, pero otras fonts válidas OLE-DB irán a.

 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 } } 

Por cierto: yo uso esto para copyr la tabla con columnas CLOB. No conseguí que eso funcionara usando serveres vinculados, cf. pregunta sobre dba . No reintenté los services vinculados con el nuevo ODAC.

Si está utilizando un cliente Oracle no gestionado (Oracle.DataAccess), entonces la forma más rápida es utilizar OracleBulkCopy, como señaló Tarik.

Si está utilizando el último cliente de Oracle administrado (Oracle.ManagedDataAccess), la forma más rápida es usar el enlace de matriz, como señaló Damien. Si desea mantener el código de su aplicación limpio de los detalles bindings de la matriz, puede escribir su propia implementación de OracleBulkCopy utilizando el enlace de matriz.

Aquí está el ejemplo de uso del proyecto real:

 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 loggings se insertan en 500ms!

Aquí está la implementación:

 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); } } } } 

NOTA: esta implementación utiliza el package Fastmember para un acceso optimizado a las properties (mucho más rápido que la reflexión)