Moyen le plus rapide pour mettre à jour (peupler) 1 000 000 enregistrements dans une base de données à l'aide de .NET

Question

Je utilise ce code pour insérer 1 million d'enregistrements dans une table vide dans la base de données. Bon, sans beaucoup de code je vais commencer du point où j'ai déjà interagi avec les données, et lu le schéma dans un DataTable:

Alors:

DataTable returnedDtViaLocalDbV11 = DtSqlLocalDb.GetDtViaConName(strConnName, queryStr, strReturnedDtName);

Et maintenant que nous avons returnedDtViaLocalDbV11 créons un nouveau DataTable qui soit un clone de la table de base de données source:

DataTable NewDtForBlkInsert = returnedDtViaLocalDbV11.Clone();

Stopwatch SwSqlMdfLocalDb11 = Stopwatch.StartNew();
NewDtForBlkInsert.BeginLoadData();

for (int i = 0; i < 1000000; i++)
{
   NewDtForBlkInsert.LoadDataRow(new object[] { null, "NewShipperCompanyName"+i.ToString(), "NewShipperPhone" }, false);
}
NewDtForBlkInsert.EndLoadData();

DBRCL_SET.UpdateDBWithNewDtUsingSQLBulkCopy(NewDtForBlkInsert, tblClients._TblName, strConnName);

SwSqlMdfLocalDb11.Stop();

var ResSqlMdfLocalDbv11_0 = SwSqlMdfLocalDb11.ElapsedMilliseconds;

Ce code alimente 1 million d'enregistrements dans une base de données SQL embarquée (localDb) en 5200ms. Le reste du code implémente juste le bulkCopy mais je le posterai quand même.

 public string UpdateDBWithNewDtUsingSQLBulkCopy(DataTable TheLocalDtToPush, string TheOnlineSQLTableName, string WebConfigConName)
 {
    //Ouvrir une connexion à la base de données. 
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[WebConfigConName].ConnectionString))
    {
       connection.Open();

       // Effectuer un décompte initial sur la table de destination.
       SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM "+TheOnlineSQLTableName +";", connection);
       long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());

       var nl = "\r\n";
       string retStrReport = "";
       retStrReport = string.Concat(string.Format("Nombre de départs = {0}", countStart), nl);
       retStrReport += string.Concat("==================================================", nl);
       // Créer une table avec quelques lignes. 
       //DataTable newCustomers = TheLocalDtToPush;

       // Créer l'objet SqlBulkCopy.  
       // Remarquez que les positions de colonne dans le DataTable source  
       // correspondent aux positions de colonne dans la table de destination donc  
       // pas besoin de mapper les colonnes.  
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
       {
          bulkCopy.DestinationTableName = TheOnlineSQLTableName;

          try
          {
             // Écrire depuis la source vers la destination.
             for (int colIndex = 0; colIndex < TheLocalDtToPush.Columns.Count; colIndex++)
             {
                bulkCopy.ColumnMappings.Add(colIndex, colIndex);
             }
             bulkCopy.WriteToServer(TheLocalDtToPush);
          }

          catch (Exception ex)
          {
             Console.WriteLine(ex.Message);
          }
       }

       // Effectuer un décompte final sur la destination  
       // table pour voir combien de lignes ont été ajoutées. 
       long countEnd = System.Convert.ToInt32(
       commandRowCount.ExecuteScalar());

       retStrReport += string.Concat("Fin de décompte = ", countEnd, nl);
       retStrReport += string.Concat("==================================================", nl);
       retStrReport += string.Concat((countEnd - countStart)," lignes ont été ajoutées.", nl);
       retStrReport += string.Concat("Nouveaux Clients a été mis à jour avec succès", nl, "FIN DU PROCESS !");
       //Console.ReadLine();
       return retStrReport;
   }
}

Essayer via une connexion à un serveur SQL était environ 7000ms (au mieux) & en moyenne ~7700ms. Aussi via une base de données NoSQL kv aléatoire a pris environ 40 secondes (vraiment je n'ai même pas gardé les enregistrements car il a dépassé le x2 des variantes sql). Alors... y a-t-il un moyen plus rapide que ce que j'ai testé dans mon code?

Modifier

je utilise Win7 x64 8 Go de RAM et ce qui est le plus essentiel je pense (comme i5 3GHz) n'est pas si génial maintenant Le x3 500 Go Wd sur Raid-0 fait encore mieux le travail mais je dis simplement si vous le vérifiez sur votre pc bien sûr comparez-le à toute autre méthode dans votre configuration

Answer 1

Créez un seul fichier XML pour toutes les lignes que vous souhaitez enregistrer dans la base de données. Passez ce XML à une procédure stockée SQL et enregistrez tous les enregistrements en un seul appel uniquement. Mais votre procédure stockée doit être écrite de manière à pouvoir lire toutes les lignes puis les insérer dans la table.

Answer 2

Si c'est un nouveau projet, je vous recommande d'utiliser Entity Framework. Dans ce cas, vous pouvez créer une List<> avec un objet contenant toutes les données nécessaires, puis l'ajouter entièrement à la table correspondante. De cette façon, vous obtenez rapidement les données nécessaires avant de les envoyer en une seule fois à la base de données.

Answer 3

Je suis d'accord avec Mike sur SSIS mais cela peut ne pas convenir à votre environnement, cependant pour les processus ETL qui impliquent des appels entre serveurs et des processus généraux de flux de données, c'est un excellent outil intégré.

Avec 1 million de lignes, vous devrez probablement effectuer une insertion en bloc. Selon la taille des lignes, vous ne pourriez pas vraiment utiliser de procédure stockée à moins de le faire par lots. Un DataTable remplira rapidement la mémoire, encore une fois en fonction de la taille des lignes. Vous pourriez créer une procédure stockée et lui faire passer un type de table et l'appeler toutes les X lignes mais pourquoi ferions-nous cela alors que vous avez déjà une solution meilleure et plus évolutive. Ces millions de lignes pourraient être 50 millions l'année prochaine.

J'ai un peu utilisé SSIS et si cela convient à l'organisation, je vous suggère d'y jeter un coup d'œil, mais ce ne serait pas une réponse unique, cela ne vaudrait pas la peine des dépendances.