Par: Daniel Farina | mise à jour: 2019-09-12 | Commentaires (2) | connexes: Plus > T-SQL

problème

Vous avez toujours entendu dire que vous devriez éviter les curseurs dans votre code T-SQL en tant que meilleure pratique performanceand parfois causer des problèmes. Mais parfois, il est nécessaire de parcourir ledonnées une ligne à la fois, donc dans cette astuce, nous allons regarder une comparaison de la façon de faireune boucle sans utiliser le curseur.,

Solution

Nous savons tous que SQL Server, comme toute base de données relationnelle, permet à l’utilisateur de former des opérations basées sur des ensembles. En outre, comme le font de nombreux fournisseurs de bases de données, SQL Server comprendune extension procédurale qui est le langage T-SQL. Il ajoute des constructions trouvées dans les langages de procédure permettant un codage plus simple pour les développeurs. Ces constructions ont été ajoutées pour une raison et c’est parfois la seule approche de la tâche à accomplir.,

utilisation D’une boucle While au lieu de curseurs dans SQL Server

Si vous avez déjà travaillé avec des curseurs, vous pouvez trouver ce titre un peu déroutant car après tout, curseurs utilise des constructions while pour itérer entre les lignes. Mais en plus, je veux vous montrer que dans certaines circonstances, lorsque nous utilisons un curseur pour parcourir un ensemble de lignes, nous pouvons le changer en boucle while. Dans de tels cas, le seul défi sera de choisir une condition de sortie appropriée.,

avantages et inconvénients de L’utilisation de curseurs pour parcourir les lignes de Table dans SQL Server

tout ne va pas avec les curseurs, ils ont également des avantages sur d’autres techniques de bouclage.

  • Les curseurs peuvent être mis à jour: lorsque vous créez un curseur, vous utilisez une requête pour définir à l’aide de L’instruction DECLARE CURSOR. En utilisant L’option UPDATE dans l’instruction cursorcreation, vous pouvez mettre à jour les colonnes du curseur.,
  • Vous pouvez avancer et reculer dans un curseur: en utilisant L’option de défilement dans L’instruction DECLARE CURSOR, vous pouvez naviguer à travers les enregistrements du curseur dans les deux directions avec les options de récupération FIRST, LAST, PRIOR, NEXT, RELATIVE andABSOLUTE. Gardez à l’esprit que L’option SCROLL est incompatible avec les options FORWARD_ONLYand FAST_FORWARD.
  • Les curseurs peuvent être transmis aux procédures stockées: si vous utilisez L’option globale pour créer un curseur, il peut être utilisé dans n’importe quelle procédure stockée ou lot exécuté dans la même connexion. Cela vous permet d’utiliser des curseurs sur des procédures stockées imbriquées.,
  • Les curseurs ont beaucoup d’options différentes: avec les curseurs, vous avez la chancepour utiliser différentes options qui affectent leur comportement en ce qui concerne le verrouillage.
  • Les curseurs n’ont pas besoin de condition: en utilisant les curseurs, vous gérez un ensemble de lignes en tant qu’enregistrement. Cela vous permet de vous déplacer à travers le curseur sansla nécessité d’avoir une condition booléenne. Par exemple, vous pouvez créer un cursorwith le nom des bases de données résidant sur une instance SQL Server sans besoin d’une clé de substitution pour fonctionner comme une condition de test comme sur une boucle WHILE.,

Il y a aussi certains aspects négatifs que vous devez être conscient lors de l’utilisation de cursorsinau lieu d’autres options de bouclage.

  • Si vous utilisez des curseurs globaux dans votre code, vous prenez le risque de faire face à des erreurs en raison de la fermeture d’un curseur par une procédure stockée imbriquée dans votre code.
  • Les curseurs ont généralement moins de performances qu’une boucle équivalente utilisant un WHILEloop ou un CTE.

avantages et inconvénients de L’utilisation d’une boucle While pour parcourir les lignes de Table dans SQL Server

Il existe également des avantages à utiliser une boucle WHILE par rapport à un curseur.,

  • Tandis que les boucles sont plus rapides que les curseurs.
  • Les boucles While utilisent moins de verrous que les curseurs.
  • moins d’utilisation de Tempdb: les boucles While ne créent pas de copie de Data intempdb comme le fait un curseur. Rappelez-vous que les curseurs, en fonction des options vousutiliser pour les créer peut provoquer la création de tables temporaires.

la liste suivante détaille les aspects négatifs des boucles WHILE.

  • avancer ou reculer est complexe: pour avancer ou reculer dans aloop, vous devez modifier dynamiquement la condition d’itération à l’intérieur de la boucle.,Cela nécessite des soins supplémentaires; sinon vous pouvez vous retrouver dans une boucle infinie.
  • le risque d’une boucle infinie: par rapport à un curseur, vous n’avez pasun ensemble fixe de données à Boucler (c’est-à-dire les données renvoyées par la déclaration SELECT dans la déclaration cursor), à la place lorsque vous utilisez une boucle WHILE, vous devez définir une boundarywith une expression évaluée à true ou false.

construire l’environnement de Test pour les curseurs et les boucles

pour tester cela, j’utiliserai une table avec une colonne identity (CursorTestID),une colonne varchar (Filler) et une colonne bigint (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO

l’idée est de boucler les lignes de la table ordonnées par la colonne CursorTestID et de mettre à jour la colonne RunningTotal avec la somme de la valeur de la colonne CursorTestID et la valeur de la colonne RunningTotal de la ligne précédente.

Mais avant de commencer, nous devons d’abord générer des lignes de test avec le script suivant.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

Sur le script ci-dessus, vous remarquerez que je n’ai utilisé qu’une seule instruction insert et que j’ai profité du séparateur de lots (la commande GO 500000) comme raccourci pour exécuter cette instruction insert 500000 fois. Vous pouvez en savoir plus sur cette méthodepour répéter l’exécution par lots sur cette astuce:exécuter un lot T-SQL plusieurs fois à L’aide de GO.

exemple de curseur de base pour parcourir les lignes de Table dans SQL Server

créons un curseur pour remplir la colonne RunningTotal. Notez sur le nextscript que j’ai déclaré le curseur avec l’option FAST_FORWARD., Ceci est fait inorder pour améliorer les performances du curseur car selon Microsoft, L’argument FAST_FORWARD « spécifie un curseur FORWARD_ONLY, READ_ONLY avec performanceoptimizations activées ». En d’autres termes, nous demandons à SQL Server d’utiliserun curseur en lecture seule qui ne peut avancer et faire défiler de la première à la dernière ligne.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

L’image suivante est une capture d’écran montrant l’exécution du script above.As vous pouvez le voir, il a fallu trois minutes et cinq secondes pour mettre à jour les 500 000 lignes de notre table de test.,

Exemple de Boucle While pour parcourir les Lignes de la Table dans SQL Server

Maintenant, je vais réécrire le script précédent en évitant l’utilisation d’un curseur. Vous remarquerez qu’il contient une boucle While qui est presque identique à celle du script cursor. C’est, comme je l’ai dit précédemment, parce que même lorsque vous travaillez avec des curseursvous devez utiliser une structure de contrôle itérative.

La prochaine image est une capture d’écran de l’exécution du script ci-dessus. Il a pris moins de temps pour exécuter la boucle while que le curseur.,

un Autre Curseur SQL Server Exemple

prenons par exemple le curseur dans la tipStandardize de données SQL Server avec le texte de recherche et de la fonction remplacer. Un mot de conseil, afin d’exécuter ce code, vous devez suivre les étapes de la pointe pour créer l’environnement de test.

Et voici le code du curseur:

Si nous disséquons ce code, nous pouvons voir qu’il y a un curseur qui traverse les produits de la table que j’ai copiés ci-dessous.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products

exemple de curseur SQL Server converti en boucle While

afin de remplacer ce curseur par une boucle WHILE, nous devons créer une table temporarytable pour implémenter une table tally. Pour tous ceux qui ne savent pas ce qu’est un tallytable, nous pouvons le Définir comme une table qui contient une paire de colonnes consistant en une clé et sa valeur. Dans notre cas particulier, nous utiliserons un integerkey séquentiel à partir de 1, Nous pouvons donc l’utiliser comme itérateur. Cette clé sera associée à un ProductID de la table Products.,

Au début, puisque la table Products a la clé ProductID définie comme une identityvous pouvez être tenté de contourner cette étape, mais vous devez considérer que dans un cas réelune ligne aurait pu être supprimée, donc vous ne pourrez pas utiliser identitycolumn comme itérateur. De plus, une ligne peut être supprimée pendant que nous exécutons notre code et cela pourrait entraîner des erreurs d’exécution. Pour éviter cela, nous allons ajouter aTRY-CATCHblock. Je vais aller dans cette plus loin.

avant de démarrer la boucle WHILE, nous devons définir ses conditions de démarrage et d’arrêt., Pour cette matière, j’ai ajouté deux nouvelles variables entières nommées @Iterator et @MaxIterator.La variable @ MaxIterator est utilisée pour conserver le nombre d’éléments dans #TallyTabletable et nous ne définissons sa valeur qu’une seule fois avant de démarrer la boucle. La variable @ Iterator est initialisée à 1, Comme nous l’avons défini comme le nombre de départ de la séquence etnous allons incrémenter sa valeur à chaque itération.

prochaines étapes
  • êtes-vous nouveau sur les curseurs et avez-vous besoin de pratique? Dans la prochaine astuce, vous trouverez une explication, un exemple de curseur facile à comprendre et des lectures plus recommandées:exemple de curseur SQL Server.,
  • Si vous souhaitez convertir les curseurs existants dans votre code pour définir des requêtes basées,jetez un œil à ce chapitre Server Convert Cursorto Set basé sur le tutoriel de meilleures pratiques de conception de base de données de serveur SQL.
  • avez-vous besoin d’un autre exemple sur l’utilisation d’une boucle While? Jetez un oeil à cette astuce qui vous montrera comment diviser les instructions DML en lots:optimiser les grands serveurs SQL, mettre à jour et supprimer des processus à l’aide de lots.
  • au cas où vous ne sauriez pas utiliser TRY…CATCH exceptionhandling, jetez un oeil à cette astuce:SQL Server essayez D’attraper la gestion des exceptions.,
  • Restez à l’écoute de Thesql Server T-SQL Tipscategory pour obtenir plus d’idées de codage.

Dernière mise à Jour: 2019-09-12

a Propos de l’auteur
Daniel Farina est né à Buenos Aires, en Argentine. Autodidacte, depuis son enfance, il a montré une passion pour l’apprentissage.
voir tous mes conseils
Ressources connexes

  • Plus de conseils pour les développeurs de bases de données…

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *