Bulk Insert pour Oracle à l'aide .NET

J'ai récemment découvert une classe spécialisée qui est génial pour un bulk insert (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! Il prend un datatable en tant que paramètre, puis vous appelez WriteTOServer méthode...il est très rapide et efficace, bonne chance!!

SQL Server SQLBulkCopy est absolument rapide. Malheureusement, j'ai trouvé que OracleBulkCopy est beaucoup plus lent. Aussi, il a des problèmes:

• Vous devez être très sûr que vos données d'entrée est propre si vous prévoyez d'
  utilisation OracleBulkCopy. Si une violation de clé primaire se produit, un ORA-26026
  est soulevée et il semble irrécupérable. Essayer de reconstruire les
  l'indice n'aide pas et les insérer sur la table échoue,
  aussi normale inserts.
• Même si les données sont propres, j'ai trouvé que
  OracleBulkCopy parfois coincé à l'intérieur de WriteToServer. Le problème
  semble dépendre de la taille du lot. Dans mes données de test, le problème serait
  arriver exactement au même point dans mon test lorsque je le répète, est. L'utilisation d'un
  plus grande ou plus petite taille de lot, et le problème ne se produit pas. Je vois
  que la vitesse est plus irrégulière sur de plus grandes tailles de lots, ce points
  des problèmes liés à la gestion de la mémoire.

Système.Les données.OracleClient.OracleDataAdapter est plus rapide que OracleBulkCopy si vous voulez remplir un tableau avec des petits dossiers, mais de nombreuses lignes. Vous devez ajuster la taille du lot cependant, l'optimum BatchSize pour OracleDataAdapter est plus petite que pour OracleBulkCopy.

J'ai couru mon test sur une machine Windows 7 avec un x86 exécutable 32 bits ODP.Net client 2.112.1.0. . Le OracleDataAdapter est la partie du Système.Les données.OracleClient 2.0.0.0. Mon test est environ 600 000 lignes avec une taille d'enregistrement max. 102 octets (taille moyenne de 43 caractères). Source de données est de 25 MO fichier texte, lire en ligne en ligne sous forme de flux.

Dans mon test, j'ai construit les données d'entrée de la table pour une table fixe à la taille et ensuite utilisé soit OracleBulkCopy ou OracleDataAdapter pour copier le bloc de données pour le serveur. J'ai quitté BatchSize comme 0 dans OracleBulkCopy (façon à ce que le contenu du tableau est copié comme un lot) et le régler à la taille de la table dans OracleDataAdapter (encore une fois qui doit créer un seul lot de fabrication à l'interne).
Les meilleurs résultats:

• OracleBulkCopy: la taille de la table = 500, durée totale de 4'22"
• OracleDataAdapter: la taille de la table = 100, durée totale de 3'03"

Pour la comparaison:

• SqlBulkCopy: la taille de la table = 1000, durée totale 0'15"
• SqlDataAdapter: la taille de la table = 1000, durée totale de 8'05"

Même ordinateur client, serveur de test, SQL Server 2008 R2. Pour SQL Server, bulk copy est le meilleur moyen d'aller. Il est non seulement plus rapide, mais la charge du serveur est également plus faible que lors de l'utilisation de la carte de données. Il est dommage que OracleBulkCopy n'offre pas tout à fait la même expérience - la BulkCopy API est beaucoup plus facile à utiliser que DataAdapter.

Pour le suivi de Théo suggestion avec mes conclusions (excuses - je n'aie pas assez de réputation pour ce post un commentaire)

Première, c'est comment utiliser plusieurs paramètres nommés:

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

Cependant, je n'ai vu aucune variation de vitesse entre:

• la construction d'une nouvelle commandString pour chaque ligne (Chaîne de caractères.Format)
• la construction d'un maintenant paramétrée commandString pour chaque ligne
• à l'aide d'un seul commandString et de l'évolution des paramètres

Je suis en utilisant le Système.Les données.OracleClient, la suppression et l'insertion de 2500 lignes à l'intérieur d'une transaction

Trouver liés exemples un peu confus, j'ai travaillé sur certains de code qui montre un travail tableau insérer dans une table de test (jkl_test). Voici le tableau:

create table jkl_test (id number(9));

Est ici .Net code pour une simple application Console qui se connecte à Oracle à l'aide de ODP.Net et insère un tableau de 5 entiers:

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

Si vous utilisez non géré client oracle (Oracle.DataAccess), alors le moyen le plus rapide est d'utiliser OracleBulkCopy, comme il a été indiqué par Tarik.

Si vous êtes en utilisant les dernières géré client oracle (Oracle.ManagedDataAccess), alors le moyen le plus rapide est d'utiliser la matrice de liaison, comme cela a été signalé par Damien. Si vous souhaitez conserver votre code d'application propre de la matrice de liaison de détails, vous pouvez écrire votre propre mise en œuvre de OracleBulkCopy à l'aide de la matrice de liaison.

Ici est exemple d'utilisation de projet réel:

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 enregistrements sont insérés dans 500ms!

Ici est la mise en œuvre:

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

REMARQUE: cette application utilise Fastmember package pour l'optimisation de l'accès aux propriétés(beaucoup plus rapide que la réflexion)