Comment correctement et efficacement la réutilisation d'une déclaration rédigée en C# .NET (SQL Server)?

J'ai regardé beaucoup de questions, mais évidemment mon SOI-fu n'est pas à la hauteur, alors je suis ici. Je suis en train d'essayer d'optimiser l'utilisation des requêtes préparées, et je ne parle pas simplement de paramétrer un seul état, mais la compilation de l'un pour les réutiliser plusieurs fois. Ma question se situe autour des paramètres et de la réutilisation et de la façon de mettre en œuvre correctement.

Généralement j'ai suivi cette procédure (exemple artificiel):

SqlConnection db = new SqlConnection(...);
SqlCommand s = new SqlCommand("select * from foo where a=@a", db);
s.Parameters.Add("@a", SqlDbType.VarChar, 8);
s.Prepare();
...
s.Parameters["@a"] = "bozo";
s.Execute();

Super, ça marche. Cependant, je ne veux pas faire toutes ces étapes (ou les quatre derniers) à chaque fois que j'exécute cette requête. Qui semble comme il est de contrecarrer l'idée de déclarations préparées à l'avance. Dans mon esprit, je ne devrait avoir à modifier les paramètres et ré-exécuter, mais la question est comment le faire?

J'ai essayé s.Parameters.Clear(), mais cela supprime les paramètres eux-mêmes, et pas seulement les valeurs, donc, je voudrais essentiellement besoin de re-Add les paramètres et re-Prepare encore une fois, qui semble rompre le point de l'ensemble. Non merci.

À ce point, je suis parti avec une itération à travers s.Parameters et les configurer à la valeur null ou une autre valeur. Est-ce correct? Malheureusement, dans mon projet actuel, j'ai des requêtes avec ~15 paramètres qui doivent être exécutées ~10 000 fois par run. Je peux shunt cette itération dans une méthode, mais je me demandais si il ya une meilleure façon de le faire (sans stockées procs).

Ma solution actuelle est une méthode d'extension, SqlParameterCollection.Nullify, qui définit tous les paramètres à la valeur null, ce qui est bien pour mon cas. Je viens de courir après une exécution.

J'ai trouvé quelques pratiquement identiques, mais (à mon humble avis) des questions sans réponse:

Préparées et de l'intégré dans le pool de connexion .NET

SQLite/C# Regroupement de connexions et de l'Instruction Préparée Confusion (Serge était si près à répondre!)

La meilleure réponse que j'ai pu trouver est (1) le bon sens ci-dessus et (2) de cette page:

http://msdn.microsoft.com/en-us/magazine/cc163799.aspx

Étrange, j'ai eu un saut de ligne après le premier lien dans la boîte d'édition, mais il n'a pas le faire dans le rendu de la sortie... Fixe.
Le mettre sur une méthode, passer une collection de SqlParameters et réutiliser le reste. 🙂
Lire ce msdn.microsoft.com/en-us/magazine/ee236412.aspx et assurez-vous de définir avec précision la taille des paramètres. Cela devrait vous aider à améliorer les performances. (Sauf storedprocedure bien sûr)
Êtes-vous de l'exécution de cette dans une boucle? Sinon, j'ai soupçonnez que vous pourriez être dans un scénario où Prepare() est idéal. De ma compréhension, pour Prepare() être utile, vous devez garder votre SqlCommand objet, ce qui pourrait conduire à des problèmes d'élimination lorsque vous avez enfin fini avec elle.
Bonne idée, une autre façon de faire de mon Annuler. La question est plus autour de la façon de ré-utiliser une instruction préparée.

OriginalL'auteur Josh | 2014-01-07

  1. 11

    Lors de la ré-utilisation d'un prêt SqlCommand, sûrement tout ce que vous devez faire est de définir les valeurs des paramètres pour les nouveaux? Vous n'avez pas besoin de les effacer après usage.

    Pour moi, je n'ai pas vu un SGBD produit dans les 10 dernières années, qui a obtenu notable bénéficier de la préparation d'une déclaration (je suppose que si le Serveur de base de données a été à la limite de son PROCESSEUR, il pourrait, mais ce n'est pas typique). Êtes-vous sûr que la Préparation est-elle nécessaire?

    Exécuter la même commande "~10 000 fois par run" sent un peu à moi, à moins que vous téléchargez à partir d'une source externe. Dans ce cas, le Chargement en Vrac pourrait aider? Qu'est-ce que chaque course est en train de faire?

    Hourras

    Il est fondamentalement un périodique de la charge externe à partir de diverses sources (SQL, LDAP, SAVON, etc.) avec le traitement de transformer les données d'entrée dans une nouvelle structure. Elle est plus complexe qu'une colonne de la carte ou select/insert, malheureusement. J'ai vu d'autres réponses, d'ailleurs qui ne jurent que par la préparation, et après avoir lu beaucoup de choses en ligne, je ne suis pas convaincu que la plupart des gens utilisent des requêtes préparées correctement de toute façon (pour cette raison). La compensation est juste une précaution, la question est de savoir vraiment si c'est (réinitialisation des paramètres uniquement) est la bonne façon de ré-utiliser la commande pour préserver l'compilé déclaration.
    Merci. Vous avez seulement besoin de réinitialiser les paramètres aux valeurs nouvelles (je viens de le tester et il fonctionne). Vous n'avez pas besoin de les mettre à null ou quoi que ce soit. Donc oui, garder la connexion ouverte, garder le SqlCommands autour de, et de réinitialiser les valeurs des paramètres comme nécessaire.
    Merci de noter à garder la connexion ouverte. J'ai fini par faire une statique de la classe DB et "enregistrement" commandes avec elle, de sorte que je n'avais qu'une connexion pour la vie de l'import réutilisé les commandes de plus de et plus. Heureusement, c'est un single-threaded app. J'ai aussi fait en sorte que j'avais à présenter des paramètres lors de l'exécution d'une commande enregistrée, et fait en sorte qu'ils étaient tous présents donc pas de valeurs précédentes sont restés (ils ne s'attardent entre l'exécute, j'ai découvert).

    OriginalL'auteur simon at rcl

  2. 5

    À ajouter à Simon réponse, avant Sql 2005 Command.Prepare() aurait amélioré plan de requête de mise en cache de requêtes ad-hoc (SPROCs serait généralement être compilé). Cependant, dans les plus récentes Versions Sql, à condition que votre requête est paramétrée, des requêtes ad-hoc, qui sont également paramétrés peuvent également être mis en cache, réduisant le besoin de Prepare().

    Voici un exemple de conserver une SqlParameters collection de changer juste la valeur de ces paramètres des valeurs qui varient, pour empêcher la création répétée de l'Paramètres (paramètre enregistrement de création de l'objet et de la collection): 

    using (var sqlConnection = new SqlConnection("connstring"))
 {
    sqlConnection.Open();
    using (var sqlCommand = new SqlCommand
       {
          Connection = sqlConnection,
          CommandText = "dbo.MyProc",
          CommandType = CommandType.StoredProcedure,
       })
    {
       //Once-off setup per connection
       //This parameter doesn't vary so is set just once
       sqlCommand.Parameters.Add("ConstantParam0", SqlDbType.Int).Value = 1234;
       //These parameters are defined once but set multiple times
       sqlCommand.Parameters.Add(new SqlParameter("VarParam1", SqlDbType.VarChar));
       sqlCommand.Parameters.Add(new SqlParameter("VarParam2", SqlDbType.DateTime));

       //Tight loop - performance critical
       foreach(var item in itemsToExec)
       {
         //No need to set ConstantParam0
         //Reuses variable parameters, by just mutating values
         sqlParameters["VarParam1"].Value = item.Param1Value; //Or sqlParameters[1].Value
         sqlParameters["VarParam2"].Value = item.Param2Date; //Or sqlParameters[2].Value
         sqlCommand.ExecuteNonQuery();
       }
    }
}

    Notes:

    • Si vous insérez un grand nombre de lignes, et la concurrence avec d'autres habitants de la base de données est important, et si une transaction ACID frontière n'est pas important, vous pourriez envisager de dosage, et de commettre des mises à jour telles que moins de 5000 ligne verrous sont placés sur une table à la fois, afin de se prémunir contre l'escalade de verrous de table.
    • Selon le travail de votre proc est en train de faire, il y a peut être une possibilité de paralléliser la boucle, par exemple avec le TPL. Évidemment, la connexion et les commandes ne sont pas thread-safe pour chaque Tâche nécessite sa propre connexion et Réutilisables de la Commande - la localInit surcharge de Parallèle.ForEach est idéal pour ce.

    OriginalL'auteur StuartLC