none
Optimisation de l'utilisation de SQLConnection RRS feed

  • Question

  • Bonjour,

    Je travaille sur une application multithread qui utilise intensément des connections SQL, avec en plus, du SQL Service Broker.
    J'ai un problème de "The timeout period elapsed prior to obtaining a connection from the pool" donc j'analyse le code et j'aimerais vous soumettre mon utilisation de SQLConnection pour avoir des avis et des conseils.

    L'utilisation de SQLConnection est centralisé.  Lorsque j'ai besoin d'une connexion, j'utilise un USING, comme ceci:
    Using cnx As New SqlConnection(cnxString.ToString())
    	ExecuteFiller(filler, cnx)
    End Using
    
    

    Ensuite, j'ai crée une petite classe qui crée une catégorie dans le moniteur de performance et j'incrémente un compteur de fermeture de connexions SQL.
    J'incrémente ce compteur après le USING car je n'ai pas trouvé de meilleurs endroits, j'aurais voulus hériter de SQLConnection mais c'est impossble.

    La méthode ExecuteFiller reçoit la connexion, fermée, en paramètre:
    Private Sub ExecuteFiller(ByVal filler As IFiller, ByVal cnx As SqlConnection)
    	Try
    		ConnectionTools.OpenSQLConnection(cnx)
    
    		Using cmd As SqlCommand = CreateCommand(cnx)
    			Using reader As SqlDataReader = cmd.ExecuteReader()
    				filler.FillFromDataReader(reader)
    				FillOutput()
    			End Using
    		End Using
    	Catch ex As Exception
    		Throw ex
    	End Try
    End Sub
    La méthode "OpenSQLConnection" est une méthode statique qui recoit par référence la connexion, l'ouvre et incrémente un compteur dans le moniteur de performances.
    L'objet: "_OpenSQLConnectionLockObject" est un objet statique privé à la classe qui gère le multithread, je ne sais pas si cette méthode est la meilleure façon de faire...
    Public Shared Sub OpenSQLConnection(ByRef cnx As SqlConnection)
    	SyncLock _OpenSQLConnectionLockObject
    		Try
    			If (cnx.State = ConnectionState.Closed) Then
    				cnx.Open()
    				MonitoringTools.WriteToPerfMon(CategoryName, MonitoringTools.PerfCounterName.OpennedSQLConnection,  MonitoringTools.PerfCounterAction.Increase)
    			End If
    		Catch ex As Exception
    			Throw ex
    		End Try
    	End SyncLock
    End Sub
    Je me pose plusieurs questions, l'utilisation des références déjà, je sais que l'objet SQLConnection, de par sa nature est passé par référence, donc, est-ce une erreur de rajouter un ByRef dans l'appel de cette méthode ?  Quel serait la meilleure pratique a utiliser ?

    Ensuite, dans le moniteur de performances, je vois que plusieurs connexions sont crées et il existe toujours un nombre croissant de connexions non-fermées alors que dans le Activity Monitor de SQL Server 2008, je ne vois pas de problème, j'ai donc, sans doute, un problème de trace des fermetures de connexions.

    Quel serait la meilleure méthode pour bien tracer les ouvertures et fermetures de connexions SQL ?  Je ne peux hériter et je n'ai vus aucun evenement OnClose...  des conseils ?

    Merci de me le dire si je ne suis pas clair, merci de toute aide, bonne journée,
    Claude
    vendredi 12 février 2010 10:46

Réponses

  • Bonjour,

    Votre SyncLock ne sert strictement à rien à mon que le paramètre filler est partagé par tous les thread de votre application.
    Pour bien tracer la fermeture de la connexion SQL, vous devez implémenter un design pattern Facade afin d'encapsuler une connexion dans une classe. Cette classe doit implémenter l'interface IDisposable.
    L'implémentation de la méthode Dispose consiste à appeler le dispose de la connexion encapsuler et de journaliser la fermeture de la connexion si celle-ci n'a pas été fermée explictement.
    Ajoutez les méthodes Open et Close dans cette classe afin d'ouvrir la connexion encapsulée.

    Cordialement
    Gilles TOURREAU - MVP C# - Architecte .NET/Consultant/Formateur
    • Marqué comme réponse Alex Petrescu mardi 16 février 2010 10:20
    dimanche 14 février 2010 22:31
    Modérateur

Toutes les réponses


  • L'ouverture de nouvelles connexions est toujours très couteuse en temps et ressources  (réseau, serveur,...).

    Parmis les conseils que je peux te donner:
    - garder la même connexion si plusieurs opérations (ExecuteFiller(),... ) s'enchainent dans ton application
    - s'assurer que tu utilises le système de pooling de connexion (option de la connection string) pour récupérer une des connections bufférisés par ADO.NET 
    vendredi 12 février 2010 16:58
  • Bonjour,

    Votre SyncLock ne sert strictement à rien à mon que le paramètre filler est partagé par tous les thread de votre application.
    Pour bien tracer la fermeture de la connexion SQL, vous devez implémenter un design pattern Facade afin d'encapsuler une connexion dans une classe. Cette classe doit implémenter l'interface IDisposable.
    L'implémentation de la méthode Dispose consiste à appeler le dispose de la connexion encapsuler et de journaliser la fermeture de la connexion si celle-ci n'a pas été fermée explictement.
    Ajoutez les méthodes Open et Close dans cette classe afin d'ouvrir la connexion encapsulée.

    Cordialement
    Gilles TOURREAU - MVP C# - Architecte .NET/Consultant/Formateur
    • Marqué comme réponse Alex Petrescu mardi 16 février 2010 10:20
    dimanche 14 février 2010 22:31
    Modérateur
  • Bonjour, et merci.

    La raison que j'avais d'utiliser un SyncLock est que, l'application lance 10 threads en même temps.
    L'application en est une de workflow où, un message est inscrit dans une table, chaque message est lié a 1 ou plusieurs contacts dans une autre table.

    Les 10 threads lisent ces tables et bien d'autres encore et, j'avais remarqué que parfois, deux threads tentaient d'ouvrir une connexion en même temps. J'ai donc voulus empêcher ce type d'opérations.

    Je n'ai pas encore implémenté le pattern que vous mentionniez mais je vais m'y intéresser, merci beaucoup.
    Dans l'attente, je me suis attaché à l'événement OnDispose :
    cnx.Open()
    AddHandler cnx.Disposed, _OnSQLConnDisposeHandler

    J'avais oublié la syntaxe AddHandler de VB.Net.

    Ensuite, je me désinscris lors de l'évenement.

    Public Shared Sub OnSQLConnDispose(ByVal sender As Object, ByVal e As EventArgs)
    
    	RemoveHandler DirectCast(sender, SqlConnection).Disposed, _OnSQLConnDisposeHandler
    
    	MonitoringTools.WriteToPerfMon()
    End Sub
    Encore merci de votre aide, bonne jounée,
    Claude
    mardi 16 février 2010 13:11
  • Bonjour,

    Cela vous pose problème si l'application ouvre 2 connexions simultanément ? Normalement ca devrait être beaucoup plus performant...

    Cordialement
    Gilles TOURREAU - MVP C# - Architecte .NET/Consultant/Formateur
    lundi 22 février 2010 00:10
    Modérateur