Avancement d'un script SQL

Version imprimable

Salut à tous.

Dans le cadre du développement d'un outil de grosse maintenance ponctuelle de bases de données client, j'ai un script SQL (SQLServer 2017) que j'appelle depuis un code C# :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 using (SqlConnection cnx = CommonTools.GetSqlConnection(databaseNameCible)) { string scriptFilePath = System.IO.Path.Combine(Environment.CurrentDirectory, "SqlScripts", "mon_script.sql"); string sqlScript = System.IO.File.ReadAllText(scriptFilePath); using (SqlCommand cmd = new SqlCommand(sqlScript, cnx)) { cmd.Parameters.Add("@BddCible", SqlDbType.VarChar); cmd.Parameters["@BddCible"].Value = databaseNameCible; cmd.Parameters......... cnx.Open(); cmd.ExecuteNonQuery(); } }
Ce script contient plus de 300 requêtes encadrées par une transaction (elle est gérée directement dans le script pour le moment, mais si nécessaire, je devrais pouvoir la déplacer dans le C# au besoin). Sur de très grosses bases il peut prendre plusieurs 10ènes de minutes à s'exécuter entièrement.
NOTA : Je suis pour le moment sur du code synchrone, mais je m'interdis pas de passer sur de l'async/await, voire explicitement des task si besoin.

Afin de faire patienter l'utilisateur et éventuellement alimenter un log, je souhaiterais que ce script puisse me renvoyer régulièrement une information d'avancement.
- Existe-il des instructions à rajouter dans le script sql pour qu'il renvoie régulière, mais immédiatement, pas à la fin de l'exécution, une information pouvant indiquer l'avancement
- comment, coté code C# récupérer cette info (callback, peut-être ?), en temps réel, et non pas uniquement une fois que ExecuteNonQuery rend la main.

Est-ce simplement possible ?

Bon, j'ai toujours une solution un peu barbare, qui serait de découper automatiquement le script et de ne l'exécuter que par blocs de 10% des lignes, en prenant garde à avoir 1 requête=1ligne. Mais je trouve pas ça très "pro", c'est un peu bricole

13/09/2022, 14h15
popo

Le script SQL va s'exécuter d'un bout à l'autre sans notifier l'appelant.
Si tu veux que ton IHM soit au courant de l'avancement, tu n'a d'autre choix que d'utiliser une mécanique indépendante de ton script mais que ton script pourra appeler au besoin.

La première idée qui me viens à l'esprit est de créer une table temporaire servant à décrire l'avancement d'une tâche.
Ton script alimenterai régulièrement cette table temporaire.
Et ton application C# pourrai la lire à intervalle régulier pour notifier au niveau de l'IHM.

C'est une piste effectivement.

Par contre, il me semblait bien que j’avais déjà mis en place un tel mécanisme. Je viens de retrouver le code. Mais c'était sur les 2 instructions particulière BACKUP et RESTORE, en utilisant ce bout de code (en vb celui-ci)

Code:

1
2
3
4
5
6
7
8
9
10
11
               Using Command = New SqlCommand("RESTORE DATABASE [" + databaseName + "] FROM DISK='" + filePath + "' WITH REPLACE, STATS = 1", connection)
                    connection.FireInfoMessageEventOnUserErrors = True
                    AddHandler connection.InfoMessage, AddressOf InfoMessageHandler
                    Command.CommandTimeout = dureeTrait
 
                    ' L'option FireInfoMessageEventOnUserErrors transforme les exceptions concernant les backups en simple message d'information
                    ' Il faut donc les filtrer, s'ils apparaissent, pour recréer manuellement une exception
                    ProcessAsyncError = String.Empty
                    Await Command.ExecuteNonQueryAsync()
                    RemoveHandler connection.InfoMessage, AddressOf InfoMessageHandler
                End Using

Je sais pas si ça marcherait dans mon cas, il va falloir que je fasse quelques tests demain, pour voir ce que je récupère.

14/09/2022, 12h08
popo

SqlDataReader n'est pas adapté puisqu'il s'agit de notifier de l'avancement d'un script complet.
14/09/2022, 12h31
sevyc64

effectivement mon script ne renvoie pas de données, c'est une collection de requêtes update, delete et insert.
14/09/2022, 12h45
Mat.M

Citation:

Envoyé par sevyc64

effectivement mon script ne renvoie pas de données, c'est une collection de requêtes update, delete et insert.

et en mettant des compteurs dans le script SQL retournés à chaque exécution de requête c'est pas une solution ?
Sinon en faisant des Insert Delete en C# donc en déplaçant le script évidemment cela résoudra le problème.
Par contre le code va s'exécuter côté programme et non SGBDR.
Bref faut voir
15/09/2022, 08h41
François DORIN

Bonjour,

Je pense que ce que tu recherches peut se faire via l'événement SqlConnection.InfoMessage.

En gros, dans ton script SQL, tu peux simplement mettre des PRINT 'Processing... 5%' par exemple. Les PRINT devraient déclencher l'événement InfoMessage. Il te suffit donc de parser le message dans cet événement pour avoir l'état d'avancement.

C'est moins lourd que de passer par une table temporaire, et fonctionne également dans le cas où plusieurs requêtes simultanées (via des connexions différentes bien sûr) s'exécutent.

[EDIT]
alternative au PRINT : utiliser un ~~SET COUNT ON~~ SET NOCOUNT OFFdans le script SQL. Ainsi, à chaque fois qu'il y a une exécution d'une instruction, tu as une ligne du style "xxx lignes affectées". Si le nombre d'instruction INSERT/SELECT/UPDATE/DELETE est fixe (pas de boucle ni de conditionnel côté SQL), alors tu peux exécuter une fois le script afin de déterminer le nombre de ligne. Puis, lors des exécutions suivantes, tu comptes le nombre de ligne "xxx lignes affectées" pour savoir où tu en es exactement...

Après, dans un cas comme dans l'autre, l'hypothèse pour que cela fonctionne correctement, c'est que dans ton script, la durée d'exécution soit répartie sur l'ensemble des requêtes, et qu'il n'y ait pas une requête qui à elle seule corresponde à 99% du temps d'exécution de ton script...

[EDIT2]
Dans le EDIT, j'ai modifié le SET COUNT ON en SET NOCOUNT OFF. S'ils sont sémantiquement équivalent, le premier n'existe... pas :aie:
16/09/2022, 11h58
sevyc64

Petit retour, j'ai pu dégager un peu de temps pour faire quelques tests.

J'ai mis en place un code équivalent à celui que j'ai donné plus haut, utilisant InfoMessage.
Effectivement l'instruction print pourrait m'aider, sans doute.

Par contre, j'ai l'impression que SET NOCOUNT OFF rallonge de manière significative le temps d’exécution.

Mais tout ça ne marche pas comme je veux. Je ne récupère l’ensemble des messages qu'à la fin de l’exécution d'un seul coup, plutôt que de les récupérer au fil de l'eau.

Il faut que j'avance sur le projet, donc je vais laisser tomber cette partie-là pour le moment, j'y reviendrais plus tard.

Citation:

Envoyé par sevyc64

Par contre, j'ai l'impression que SET NOCOUNT OFF rallonge de manière significative le temps d’exécution.

Significative, je ne sais pas, mais en tout cas, oui, cela à un impact sur les performances.[/QUOTE]

Citation:

Envoyé par sevyc64

Mais tout ça ne marche pas comme je veux. Je ne récupère l’ensemble des messages qu'à la fin de l’exécution d'un seul coup, plutôt que de les récupérer au fil de l'eau.

Effectivement, je viens de faire un test, et tout est envoyé à la fin. J'ai trouvé une méthode pour passer outre, mais attention, elle peut avoir des effets de bords ! L'idée c'est d'utiliser "RAISERROR" avec la directive WITH NOWAIT.

Pour que cela fonctionne, il faut "activer" la gestion des erreurs asynchrones via la propriété FireInfoMessageEventOnUserErrors de la connexion.

Enfin, au niveau des effets de bords, une erreur "sévère" générant normalement une exception SqlException ne le fera plus et devra être traité au niveau de l'événement InfoMessage. Par contre, une erreur "très sévère" (level supérieur à 17) continuera de générer une exception :aie:

Voici un exemple de code qui marche (il faudra sans doute adapter la constante CONNECTION_STRING) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 using System.Data.SqlClient; using System.Text; namespace ConsoleApp15 { public class Program { private static readonly string CONNECTION_STRING = "Data Source=.\\MSSQLSERVER2017;Integrated Security=true"; public static void Main() { using (SqlConnection connection = new SqlConnection(CONNECTION_STRING)) { StringBuilder builder = new StringBuilder(); for(int i = 0; i <= 100; i ++) { //builder.AppendLine($"PRINT 'Processing... {i}%';"); builder.AppendLine($"RAISERROR('{i}',10,1) WITH NOWAIT;"); builder.AppendLine("WAITFOR DELAY '00:00:01';"); } connection.Open(); connection.InfoMessage += Connection_InfoMessage; connection.FireInfoMessageEventOnUserErrors = true; using(SqlCommand cmd = new SqlCommand(builder.ToString(), connection)) { cmd.CommandTimeout = 600; cmd.ExecuteNonQuery(); } } } private static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e) { Console.WriteLine(e.Message); } } }