C#-SQL: Comment exécuter un lot de StoredProcedure?

Edit:
Mon problème n'est plus un problème: je dois refaire mes performances, tests et j'ai fais une fatale erreur stupide: j'avais oublier un x1000 pour obtenir secondes de millisecondes :/
Désolé les gars.

Pour info:

- Je fais un peu 1900 des mises à jour par seconde à partir de mon PC vers le serveur de Base de données sur le réseau local.

- 3.200 mises à jour par seconde si les programmes sont sur la même machine que DB.

- 3.500 mises à jour par seconde à partir de mon PC sur le serveur de Base de données je n'ai pas re-créer et re-ouvrir une nouvelle occurrence de SQLConnection.

- 5.800 mises à jour par seconde, avec un texte de lot.
Pour ma 10.000 lignes, si c'prendre 5 secondes, c'est ok pour mes programmes. Désolé de devoir vous inquiéter.

En fait, j'utilise un stockées SQL prodedure pour créer une ligne dans ma base de données pour éviter les injection SQL. En C#, j'ai la méthode suivante:

public void InsertUser(string userCode)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      sqlCommand.Parameters.Add(new SqlParameter("@UserCode", userCode));
      sqlConnection.Open();
      sqlCommand.ExecuteNonQuery();///0.2 seconds !ERROR HERE! 0.2ms here,NOT 0.2sec!!!
   }
}

Il woks grande quand j'ai une ou deux lignes à insérer. Mais si j'ai besoin de créer 1.000 utilisateurs et 10.000 produits et 5000 animaux de compagnie, ce n'est pas la meilleure solution: je vais perdre un temps énorme dans netwok de transport.

Je crois, sans enregistrement il, que je peux utiliser seulement une quantité limitée de rappel. Donc, je ne veux pas appeler 10.000 fois: 

sqlCommand.BeginExecuteNonQuery()

Une autre façon de créer un texte de lot, mais il y a une Injection SQL de risque (et c'est moche).

N', il est un "SqlCommandList' objet que gérer le .Net? Comment dois-je faire de gros de l'écriture dans la base de données? Ce que le bon patern pour qui?

  • Je vous recommande d'utiliser "sqlCommand.Prepare();" avec le type de paramètre, ce qui devrait améliorer le temps d'exécution de la deuxième requête au moins.
InformationsquelleAutor Olivier de Rivoyre | 2008-10-14
  1. 10

    Cela devrait courir un peu plus vite:

    public void InsertUser(IEnumerable<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString), 
             SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection))
   {
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
      sqlConnection.Open();

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();///0.2 seconds
      }
   }
}

    Qui ne s'ouvrent qu'une seule connexion et ne peut créer qu'une seule commande, même si vous passer les 1000 utilisateurs. Il va encore faire de chaque pièce séparément, s'. Et bien sûr, si le code utilisateur n'est pas une chaîne, que vous aurez envie de re-facteur de manière appropriée. Vous pouvez également regarder dans SQL Server BULK INSERT commande.

    InformationsquelleAutor Joel Coehoorn
  2. 3

    Ce sujet UpdateBatchSize de la SQLDataAdaptor?

    Notre front de fin gars de ce lot quelques de 10 000 proc appels en morceaux

    L'Article

    MSDN

    Notre environnement n'autorise pas les "bulkadmin" droits donc on ne peut pas utiliser BULKINSERT/bcp etc

    InformationsquelleAutor gbn
  3. 2

    Personnellement, si j'ai régulièrement s'attendre à faire assez grands inserts (10 000 lignes serait certainement qualifier...), je pourrais envisager d'avoir un tableau distinct pour l'entrée de données, et l'utilisation SqlBulkCopy pour remplir ce tableau. Alors que vous venez d'exécuter une procédure stockée unique qui déplace les données dans la table réelle.

    Une autre approche est de faire descendre le xml de la base de données, et l'utilisation sqlxml à analyser que (beaucoup plus facile avec SQL2005 et versions ci-dessus) -, mais cela met de travail supplémentaire sur le serveur de base de données.

    InformationsquelleAutor Marc Gravell
  4. 2

    Si vous avez vraiment été intéressé à ce sujet, vous pouvez (comme vous l'avez dit) par lot les commandes dans les chaînes comme:

    var cmd = new SqlCommand();
cmd.Connection = sqlConnection;
for (int i = 0; i < batchSize; i++) {
    cmd.CommandText += String.Format("EXEC InsertUser @UserCode{0};", i);
    cmd.Parameters.AddWithValue("@UserCode" + i.ToString(), XXXXX);
    //... etc ...
}

    Parce que dans ce schéma, vous seriez à l'aide d'un paramètre, vous n'avez plus de risques d'injection SQL que si vous avez utilisé une procédure stockée. Mais je me demande si oui ou non vous allez vraiment économiser une quantité appréciable de temps à faire cela. OMI, vous devriez juste garder les choses simples et faire de la manière dont vous le faites maintenant.

    • Il y a BEAUCOUP de frais généraux dans la re-création de la commande et de la constante d'ouverture et de fermeture de nouvelles connexions.
    • Aussi, si vous êtes va faire ça comme ça, utiliser un stringbuilder pour assembler la commande.
    • Joel, je pense que vous trouverez que le regroupement de connexions vitesses considérablement les choses. Et comme pour StringBuilder, c'est vrai que ça va accélérer les choses et provoquer moins de l'utilisation de la mémoire, mais tout dépend du nombre de commandes que vous voulez dans votre lot...
    InformationsquelleAutor Dave Markle
  5. 2

    Basé sur Joel réponse, c'est la solution la plus rapide à court d'utiliser SqlBulkCopy ou la création de grandes chaînes de désordre SQL et de l'exécution. (J'ai ajouté une transaction qui permettra d'améliorer les performances tout à fait beaucoup)

    public void InsertUser(IEnumerabler<string> userCodes)
{
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      sqlConnection.Open();
      SqlTransaction transaction = connection.BeginTransaction();
      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.Transaction = transaction;
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);

      foreach(string code in userCodes)
      {
          param.Value = code;
          sqlCommand.ExecuteNonQuery();
      }      
      transaction.Commit();
   }
}
    • Opérations impliquent de verrouillage... si cela s'exécute dans l'isolement, il sera plus rapide. Mais si il y a autre activité sur le serveur, il peut même ralentir les choses vraiment mauvais. Même si j'avoue que de ne faire que des inserts est peu susceptible de conduire à un conflit.
    InformationsquelleAutor Sam Saffron
  6. 1

    Je devine que c'est une assez vieille question.

    Avec SQL Server 2008, la réponse est maintenant d'utiliser un Tableau de Valeur de Paramètre. En bref, passer toutes vos variables dans une de type défini (tableau).

    En SQL, vous pouvez maintenant traiter tous les dossiers en tant qu'éléments individuels...en Fait utiliser jeu de logique et d'obtenir réelle de la performance.

    InformationsquelleAutor Ben Taylor
  7. 0

    Avez-vous envisagé de passer d'un document XML à une procédure stockée, puis itérer que pour trouver les données à insérer?

    InformationsquelleAutor Danimal
  8. 0

    "ce n'est pas la meilleure solution: je vais perdre un temps énorme dans netwok de transport"
    Pouvez-vous vivre avec la perte?

    Si c'est quelque chose que vous ne faites pas souvent, alors est-il question?
    Mesurer d'abord, si c'est un problème puis le fixer, personnellement, sans doute, j'irais avec Marc Gravells table pour la réception des inserts.
    Une autre option est de tirer de l'insère de manière asynchrone, alors vous n'êtes pas d'attente sur chaque pour finir avant de commencer la suivante.

    Il m'a fallu des années, mais finalement j'ai compris que je ne devais pas perdre de temps à l'optimisation de code qui n'en a pas besoin.

    Espérons que cette aide (même si je ne pense pas que ça, désolé).

    InformationsquelleAutor Binary Worrier
  9. 0

    Que par certaines des réponses ci-dessus, le plus notable augmentation des performances pour le moins d'effort implique 2 modifications de votre code existant:

    1. Envelopper les mises à jour d'une transaction
    2. L'ouverture d'une seule connexion et l'appel de la procédure plusieurs fois avec des paramètres différents.

    Insertions sont une option, mais probablement excessif pour ce que vous voulez faire.

    InformationsquelleAutor