Discussion :

[Cyrilange]

Bonjour,

Je veux utiliser SMO pour insérer des données dans une table contenant uniquement 2 champs (commune et codePostal).

Voici le code que j'utilise pour lire mes fichiers scripts :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
...
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.IO
...
 
 Dim scriptDirectory As String = My.Application.Info.DirectoryPath & "\ScriptFile"
            Dim sqlConnectionString As String = "Integrated Security=SSPI;" & "Persist Security Info=True;Initial Catalog=" & _databaseName & ";Data Source=.\SQLEXPRESS;Timeout=120;"
            Dim di As New DirectoryInfo(scriptDirectory)
            Dim rgFiles As FileInfo() = di.GetFiles("*.sql")
            For Each fi As FileInfo In rgFiles
                Dim fileInfo As New FileInfo(fi.FullName)
                Dim script As String = fileInfo.OpenText().ReadToEnd()
                Dim connection As New SqlConnection(sqlConnectionString)
                Dim server As New Server(New ServerConnection(connection))
                server.ConnectionContext.ExecuteNonQuery(script)
            Next

Tout se passe bien jusqu'au moment ou j'arrive sur mon fichier contenant environ 35000 instructions INSERT.

Voici un extrait du script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
....
INSERT [dbo].[Communes] ([commune], [codePostal]) VALUES (N'AAST', N'64460')
INSERT [dbo].[Communes] ([commune], [codePostal]) VALUES (N'ABAINVILLE', N'55130')
INSERT [dbo].[Communes] ([commune], [codePostal]) VALUES (N'ABANCOURT', N'59265')
.... etc.
GO

SMO devient extrèmenent lent à tel point que je n'ai même pas eu la patience d'attendre qu'il ait fini après 10 bonnes minutes d'attente.

Est-il possible d'améliorer les performances de SMO dans ce cas précis ?

Merci

[B.AF]

Ben il faut peut être éviter de mettre ton go après les 35000 instruction alors.

[ctxnop]

Pourquoi utiliser SMO pour faire ca surtout ???
Ca n'apporte absolument rien.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Dim scriptDirectory As String = My.Application.Info.DirectoryPath & "\ScriptFile"
Dim di As New System.IO.DirectoryInfo(scriptDirectory)
Dim rgFiles As System.IO.FileInfo() = di.GetFiles("*.sql")
using cnx As New New SqlConnection(sqlConnectionString)
    cnx.Open()
    For Each fi As System.IO.FileInfo In rgFiles
        Dim script As String = fi.OpenText().ReadToEnd()
            Using cmd As New SqlCommand(script, cnx)
                cmd.ExecuteNonQuery()
            End Using
    Next
End Using

Sortir la connexion du for histoire de pas créer 12 000 connexions si tu as 12 000 fichiers ...
Des using sur tout ca pour libérer les ressources.
Passer par un simple SqlCommand plutôt que de sortir SMO.
J'ai également viré l'instanciation d'un nouveau FileInfo alors que tu en as déjà un.
Tester la réussite de l'ouverture du fichier serait pas un mal non plus.
Éventuellement faire une transaction pour éviter un traitement partiel en cas d'erreur.
D'ailleurs un gestion d'erreur en try/catch devrait être mise en place car on a aucune garantie qu'il n'y aura pas d'exception à la lecture du fichier ni a l'exécution du script.

EDIT :
J'ai oublié de préciser un truc. L'instruction GO n'existe pas. Elle est comprise uniquement par les outils fournis avec SQL Server, elle sert de signal pour indiquer qu'il faut exécuter le script lut jusqu'à cette instruction avant de continuer.
Autrement dit, si tu sépares 2 inserts par un GO dans SQL Management, ca signifie juste qu'en terme de code il faut faire un cmd.Execute() du premier insert, puis dans un second temps un cmd.Execute() du second.
Mais si tu envois l'instruction GO alors tu va te taper une exception (que ton code ne gère pas de toute évidence ce qui bloquera probablement ton interface).

[Cyrilange]

Merci pour ces bons conseils.
J'ai utilisé SMO pour justement éviter les erreurs générés par les instructions GO.
Suite à vos remarques j'ai donc laissé tombé SMO pour mes instructions INSERT.
Voici le code que j'utilise maintenant et qui fonctionne très bien.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Dim sqlConnectionString As String = "Integrated Security=SSPI;" & "Persist Security Info=True;Initial Catalog=" & _databaseName & ";Data Source=.\SQLEXPRESS;Timeout=120;"
        Dim SqlScriptFileName As String = My.Application.Info.DirectoryPath & "\data\Communes.sql"
        Dim sqlScriptLine As String
        Dim sqlScriptStream As StreamReader = New System.IO.StreamReader(SqlScriptFileName)
 
        Using cnx As New SqlConnection(sqlConnectionString)
            cnx.Open()
            Do While sqlScriptStream.Peek > -1
                sqlScriptLine = sqlScriptStream.ReadLine()
                If Not String.Compare(sqlScriptLine.Trim, "GO", True) = 0 Then
                    Using cmd As New SqlCommand(sqlScriptLine, cnx)
                        cmd.ExecuteNonQuery()
                    End Using
 
                End If
            Loop
            cnx.Close()
        End Using
        sqlScriptStream.Close()

Je retiens biensur l'idée de faire une Transaction et d'englober tout ça dans un Try Catch.

Si tout de fois quelqu'un sait pourquoi SMO est aussi lent pour faire des INSERT qu'il n'hésite pas à le dire .

Merci à vous

[B.AF]

Parce que SMO c'est comme faire un script dans SQL Manager.

Donc le GO joue son rôle puisque ce que tu as fait reviens à faire un script d'insert de 35000 éléments puis de lancer le commande execute à la fin.

D'où ma réponse d'origine.

[ctxnop]

Au passage, j'ajouterai que ta nouvelle façon de faire n'est pas tout à fait correcte
Si je ne suis pas passé à coté, ton code exécute ton script ligne par ligne.
Or, rien ne garanti une requête par ligne. Tu peux très bien avoir des retours à la ligne en plein milieu d'une requête.

[Cyrilange]

Au passage, j'ajouterai que ta nouvelle façon de faire n'est pas tout à fait correcte
Si je ne suis pas passé à coté, ton code exécute ton script ligne par ligne.
Or, rien ne garanti une requête par ligne. Tu peux très bien avoir des retours à la ligne en plein milieu d'une requête.

Oui tout à fait, mais comme c'est moi qui procure le fichier et non l'utilisateur je sais que son contnu est correcte et qu'il y a bien un retour à la ligne après chaque instruction INSERT. De plus ce fichier script ne sera jamais modifié par l'application. Je n'ai donc pas besoin de faire des vérifications supplémentaires qui ralentirais mon code.

[ctxnop]

Aucune garanti que l'utilisateur n'ira pas fouiner un jour dans ces requêtes.
De plus, prévoir le cas ce coute rien et ca sécurise. Je dirais même que ca va accélérer ton code puisque :

- Au lieu d'instancier un SqlCommand par ligne de script (ce qui fait 35 000 instanciation d'objet pour ton seul script des codes postaux) tu n'instancies qu'un seul SqlCommand par lot.

- Au lieu d'exécuter chaque ligne une à une tu exécutes un lot complet (Sql Server est, je l'espère, prévus pour supporter une charge bien supérieure à une simple requête INSERT).

Le code pour gérer ca n'est franchement pas bien dur, tu fais un stringbuilder et modifie un chouilla ton if pour faire en sorte que :
- Chaque ligne est ajoutée au stringbuilder
- Lorsqu'un Go est découvert, tu le dégage, puis tu exécute le stringbuilder.

Donc en gros tu va avoir une ligne de code en plus et l'exécution du SqlCommand est déplacée à l'intérieur du if, on peut pas dire que ce soit un changement consommateur.

Aller, je te met meme le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
        Dim sqlConnectionString As String = "Integrated Security=SSPI;" & "Persist Security Info=True;Initial Catalog=" & _databaseName & ";Data Source=.\SQLEXPRESS;Timeout=120;"
        Dim SqlScriptFileName As String = My.Application.Info.DirectoryPath & "\data\Communes.sql"
        Dim sqlScriptLine as String
        Dim script As New System.Text.StringBuilder()
        Using sqlScriptStream As New StreamReader(SqlScriptFileName) ' Using sur les streams !
            Using cnx As New SqlConnection(sqlConnectionString)
                cnx.Open()
                Do While (sqlScriptLine = sqlScriptStream.ReadLine()) IsNot Nothing                    
                    If String.Compare(sqlScriptLine.Trim, "GO", True) = 0 Then
                        script.AppendLine(sqlScriptLine) ' Si ce n'est pas un GO on l'ajoute au stringbuilder
                    Else
                        ' Si c'est un go, on exécute le script lut jusqu'ici
                        Using cmd As New SqlCommand(script.ToString(), cnx)
                            cmd.ExecuteNonQuery()
                        End Using
                    End If
                Loop
            End Using
        End Using

Et si tu te soucie véritablement de la vitesse d'exécution au point de prendre en compte le temps que va consommer une vérification simple, alors je te conseil de commencer par simplifier ton code.
Par exemple : Comme tu vois dans mon while je me suis passé du Peek puisque ReadLine retourne nothing quand il n'y a plus rien a lire.
Ou encore, la condition de ton If peut être simplifiée en :
If String.Compare(sqlScriptLine.Trim, "GO", True) <> 0
C'est plus rapide que de faire directement la bonne comparaison plutôt que d'inverser le résultat d'une autre.

Dans tous les cas, l'instanciation d'objet est bien plus consommatrice que quelques if.