Améliorer .NET / MSSQL sélectionner et mettre à jour les performances

Je souhaite augmenter les performances des requêtes de sélection et de mise à jour très simples de .NET et MSSQL 2k8. Mes requêtes sélectionnent ou mettent à jour une seule ligne. Les tables DB ont des index sur les colonnes sur lesquelles je request. Mon code test .NET ressemble à ceci:

public static MyData GetMyData(int accountID, ssortingng symbol) { using (var cnn = new SqlConnection(connectionSsortingng)) { cnn.Open(); var cmd = new SqlCommand("MyData_Get", cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(CreateInputParam("@AccountID", SqlDbType.Int, accountID)); cmd.Parameters.Add(CreateInputParam("@Symbol", SqlDbType.VarChar, symbol)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var MyData = new MyData(); MyData.ID = (int)reader["ID"]; MyData.A = (int)reader["A"]; MyData.B = reader["B"].ToSsortingng(); MyData.C = (int)reader["C"]; MyData.D = Convert.ToDouble(reader["D"]); MyData.E = Convert.ToDouble(reader["E"]); MyData.F = Convert.ToDouble(reader["F"]); return MyData; } } }

et la procédure stockée correspondante ressemble à ceci:

  PROCEDURE [dbo].[MyData_Get] @AccountID int, @Symbol varchar(25) AS BEGIN SET NOCOUNT ON; SELECT p.ID, pA, pB, pC, pD, pE, pF FROM [MyData] AS p WHERE p.AccountID = @AccountID AND p.Symbol = @Symbol END

Ce que je vois si je lance GetMyData dans une boucle, en interrogeant des objects MyData, je ne dépasse pas ~ 310 transactions / sec. J'espérais réaliser plus de 1000 transactions / sec.

Du côté de SQL Server, pas vraiment sûr de ce que je peux améliorer pour une requête aussi simple. ANTS profiler me montre que sur le côté .NET, comme prévu, le goulot d'étranglement est cnn.Open et cnn.ExecuteReader, mais je n'ai aucune idée de la façon dont je pourrais améliorer de manière significative mon code .NET?

J'ai vu des benchmarks mais où les gens semblent facilement atteindre 10s de milliers de transactions / sec.

Tout conseil sur la façon dont je peux améliorer de manière significative la performance de ce scénario serait grandement apprécié!

Merci,

À M

MODIFIER:

Selon la recommandation de MrLink, l'ajout de "TOP 1" à la requête SELECT a amélioré les performances à environ 585 transactions / seconde par rapport à 310

EDIT 2:

Arash N a suggéré d'avoir la requête select "WITH (NOLOCK)" et cela a considérablement amélioré les performances! Je vois maintenant environ 2500 transactions / sec

EDIT 3:

Une autre légère optimization que je viens de faire sur le côté .NET m'a aidé à gagner encore 150 transactions / sec. Changer while (reader.Read ()) en if (reader.Read ()) a étonnamment fait toute une différence. Sur moy. Je vois maintenant 2719 transactions / sec

Essayez d'utiliser WITH (NOLOCK) dans votre instruction SELECT pour augmenter les performances. Cela sélectionnerait la ligne sans la verrouiller.

 SELECT p.ID, pA, pB, pC, pD, pE, pF FROM [MyData] WITH(NOLOCK) AS p WHERE p.AccountID = @AccountID AND p.Symbol = @Symbol

Certaines choses à considérer.

D'abord, vous ne fermez pas la connection au server. (cnn.Close ();) Finalement, il sera fermé par le garbage collector. Mais jusqu'à ce que cela se produise, vous créez une nouvelle connection à la database à chaque fois plutôt que de la retirer du pool de connections.

Deuxièmement, avez-vous un set d'index dans Sql Server couvrant les colonnes AccountID et Symbol?

Troisièmement, while accountId étant et int est gentil et rapide. La colonne Symbol étant varchar (25) sera toujours beaucoup plus lente. Pouvez-vous changer cela en drapeau int?

Assurez-vous que vos connections à la database sont en train de se mettre en pool. Si vous voyez un goulot d'étranglement dans cnn.Open, il semble qu'il y ait de bonnes chances qu'ils ne soient pas regroupés.

J'espérais réaliser plus de 1000 transactions / sec [lors de l'exécution de GetMyData en boucle]

Ce que vous requestz, c'est que GetMyData s'exécute en less de 1ms – c'est juste une optimization inutile ! Au ssortingct minimum, cette méthode implique un aller-return vers le server de database (impliquant éventuellement un access réseau) – vous ne seriez pas en mesure de rendre cette méthode beaucoup plus rapide si votre requête était SELECT 1 .

Si vous avez vraiment besoin de faire plus de requêtes par seconde, alors la réponse est d'utiliser plusieurs threads ou d'acheter un PC plus rapide.

Il n'y a absolument rien de mal avec votre code – je ne sais pas où vous avez vu des personnes gérant plus de 10 000 transactions par seconde, mais je suis sûr que cela a impliqué plusieurs clients simultanés accédant au même server de database plutôt qu'un seul thread exécuter des requêtes en less d'un dixième de ms!

Est-ce que votre méthode est appelée fréquemment? Pourriez-vous traiter vos requests par lots afin de pouvoir ouvrir votre connection, créer vos parameters, get le résultat et les réutiliser pour plusieurs requêtes avant de refermer le tout?

Si datatables ne sont pas fréquemment invalidées (mises à jour), j'implémenterais une couche de cache. C'est l'un des moyens les plus efficaces (si utilisé correctement) pour gagner en performance.

Vous pouvez utiliser des parameters de sortie au lieu d'un select car il y a toujours une seule ligne.

Vous pouvez également créer le SqlCommand à l'avance et le réutiliser. Si vous exécutez beaucoup de requêtes dans un seul thread, vous pouvez continuer à l'exécuter sur la même connection. Si ce n'est pas le cas, vous pouvez créer un pool d'entre eux ou faire cmdTemplate.Clone () et définir la connection.

Essayez de réutiliser la command et de la préparer en premier.

Je ne peux pas dire que cela va certainement aider, mais cela vaut la peine d'essayer.

Dans aucun ordre particulier…

Avez-vous (ou vos administrateurs de database) examiné le plan d'exécution que votre procédure stockée obtient? SQL Server a mis en cache un plan d'exécution fictif (soit en raison de parameters obsolètes ou de vieilles statistics).
À quelle fréquence les statistics sont-elles mises à jour dans la database?
Utilisez-vous des tables temporaires dans votre procédure stockée? Si oui, sont-ils créés en amont. Si ce n'est pas le cas, vous devrez effectuer de nombreuses recompilations car la création d'une table temporaire invalide le plan d'exécution.
Utilisez-vous le pool de connection? L'ouverture / fermeture d'une connection au server SQL est une opération coûteuse.
Votre table est-elle en cluster sur accountID et Symbol?

Finalement…

Y a-t-il une raison pour laquelle vous utilisez ce count et ce symbole plutôt que, disons, simplement récupérer toutes datatables d'un count entier d'un seul coup?