.net sql oracle bulkinsert

Inserción masiva en Oracle usando.NET



sql bulkinsert (11)

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

create table jkl_test (id number(9));

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

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

¿Cuál es la forma más rápida de hacer inserciones masivas en Oracle utilizando .NET? Necesito transferir aproximadamente 160K registros usando .NET a Oracle. Actualmente, estoy usando Insertar declaración 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 base de datos (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 registros en 15 segundos aproximadamente usando Array Binding en ODP.NET

Funciona al invocar repetidamente un procedimiento almacenado que especifique (y en el que puede hacer actualizaciones / inserciones / eliminaciones), pero transfiere los valores de múltiples parámetros de .NET a la base de datos 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 parámetros de .NET a la base de datos de una sola vez, y luego invoca repetidamente el procedimiento almacenado que especifique usando los valores de parámetros que ha especificado.

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

/ Damian

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 declaración. Después de eso, busca en una tabla hash si ya conoce esta afirmación. Si ya conoce su declaración, puede recuperar su ruta de ejecución desde esta tabla hash y ejecutar esta declaración muy rápido porque Oracle ha ejecutado esta declaración antes. Esto se denomina memoria caché 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 parámetros también puede causar inyección SQL.

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

¿Qué proveedor usas para conectar tu aplicación .NET a la base de datos 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)?

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 parámetros 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 comando String y cambiando los parámetros

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

Recientemente descubrí una clase 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!

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 entrada están limpios si planea usar OracleBulkCopy. Si se produce una violación de clave principal, se genera un ORA-26026 y parece ser irrecuperable. Intentar reconstruir 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 prueba, el problema ocurriría exactamente en el mismo punto de mi prueba 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 memoria.

En realidad System.Data.OracleClient.OracleDataAdapter es más rápido que OracleBulkCopy si desea llenar una tabla con pequeños registros 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 prueba 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 conjunto de prueba es de aproximadamente 600,000 filas con un tamaño de registro de máx. 102 bytes (tamaño promedio 43 caracteres). La fuente de datos es un archivo de texto de 25 MB, leído en línea por línea como una secuencia.

En mi prueba, construí la tabla de datos de entrada en un tamaño de tabla fijo y luego utilicé OracleBulkCopy u OracleDataAdapter para copiar el bloque de datos al servidor. Dejé BatchSize como 0 en OracleBulkCopy (para que el contenido de la tabla actual se copiara 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 servidor de prueba es SQL Server 2008 R2. Para SQL Server, la copia masiva es claramente la mejor manera de hacerlo. No solo es más rápido en general, sino que la carga del servidor 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.

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 vinculantes 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 registros 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 paquete Fastmember para un acceso optimizado a las propiedades (mucho más rápido que la reflexión)

Supongo que OracleBulkCopy es una de las formas 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 copiar de una consulta en una tabla Oracle adecuada. Probé Sql-Server un origen de datos, pero otras fuentes 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 copiar la tabla con columnas CLOB. No conseguí que eso funcionara usando servidores vinculados, cf. pregunta sobre dba . No reintenté los servicios vinculados con el nuevo ODAC.

Una forma realmente rápida de resolver este problema es hacer un enlace de base de datos desde la base de datos Oracle a la base de datos 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 base de datos puede recuperar sus datos de la base de datos 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 clase OracleBulkCopy.

Pero no creo que insertar 160k registros 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 parámetros o concatena sus valores? La unión es mucho más rápida.