Optimiser les opérations SQL avec le traitement par lot

Lorsqu'il s'agit de gérer une application avec un grand nombre de connexions simultanées, il est essentiel de minimiser le temps pendant lequel la base de données est verrouillée. Les opérations courantes telles que les mises à jour (UPDATE) ou les suppressions (DELETE) peuvent entraîner un verrouillage prolongé des tables critiques, ce qui peut avoir un impact significatif sur les performances de l'application. Une solution à ce problème consiste à utiliser le traitement par lot en SQL.

J'ai appris cette technique avec une équipe formidable à qui je passe un Hello ;)

Qu'est-ce que le Traitement par Lot en SQL ?

C'est une technique SQL avancée visant à optimiser les opérations de base de données en traitant les données par lots plutôt en une seule fois. Cette approche permet de réduire le verrouillage des tables et d'améliorer les performances globales de l'application. J'explorerai le concept du traitement par lot en utilisant un exemple de script T-SQL.

Le Script de Traitement par Lot

Le script suivant est une base solide pour mettre en œuvre le traitement par lot en T-SQL. Il est composé de plusieurs étapes clés, chacune jouant un rôle essentiel dans le processus global.

-- Définir la taille du lot de traitement
DECLARE @batchToProcess INT = 500;

-- Table temporaire contenant les données à traiter
DROP TABLE IF EXISTS #LoopTable;
CREATE TABLE #LoopTable (
     IdInfos INT NOT NULL
);

-- Remplir la table temporaire avec les données à traiter
INSERT INTO #LoopTable (IdInfos)
SELECT what.Id
FROM dbo.UneTable what WITH (NOLOCK)
WHERE what.Statut = 123;

-- Table temporaire pour stocker les données du lot en cours
-- Assurez-vous que les types de données correspondent à ceux de #LoopTable
DROP TABLE IF EXISTS #CurrentAction;
CREATE TABLE #CurrentAction (
     IdInfos INT NOT NULL
);

-- Début de la boucle de traitement par lot
WHILE (EXISTS(SELECT TOP 1 1 FROM #LoopTable))
BEGIN
  -- Supprimer les premières lignes du lot et les stocker dans #CurrentAction
  DELETE TOP(@batchToProcess) FROM #LoopTable
  OUTPUT DELETED.* INTO #CurrentAction;

  -- Exécuter l'action souhaitée sur le lot de données (par exemple, une mise à jour)
  UPDATE act
  SET act.[Statut] = 456
  FROM dbo.UneAutreTable act WITH (NOLOCK)
  INNER JOIN #CurrentAction monLot
  ON monLot.IdInfos = act.Id;

  -- Effacer le lot de données traitées
  DELETE FROM #CurrentAction;

  -- Attendre un court laps de temps pour permettre le traitement d'autres requêtes
  WAITFOR DELAY '00:00:00.200';
END

Explication du Script

1. Définir la taille du lot (@batchToProcess) : Vous pouvez ajuster la taille du lot en fonction de vos besoins. Un lot plus grand peut améliorer l'efficacité, mais il peut également entraîner un verrouillage plus long de la table. Il s'agit d'un compromis à prendre en compte.

2. Création des tables temporaires : Deux tables temporaires, #LoopTable et #CurrentAction, sont créées pour stocker temporairement les données à traiter et les données du lot en cours, respectivement.

3. Remplir la table #LoopTable : Cette étape remplit la table temporaire #LoopTable avec les données à traiter. Dans cet exemple, nous sélectionnons les lignes de la table UneTable avec un statut spécifique (123).

4. Boucle de traitement par lot : La boucle WHILE continue d'exécuter tant qu'il reste des données dans #LoopTable.

5. Suppression des données du lot : Les premières lignes du lot, définies par @batchToProcess, sont supprimées de #LoopTable et stockées dans #CurrentAction. Cela permet d'isoler un lot de données pour traitement.

6. Exécution de l'action souhaitée : Dans cette section, vous pouvez spécifier l'action à effectuer sur le lot de données. Par exemple, nous utilisons une mise à jour (UPDATE) pour modifier le statut des lignes correspondantes dans UneAutreTable.

7. Effacement du lot : Une fois l'action effectuée, le lot de données traitées est effacé de #CurrentAction.

8. Attente courte : Une petite attente est introduite (WAITFOR DELAY) pour donner la possibilité au système de traiter d'autres requêtes pendant une courte période.

Conclusion

Le traitement par lot en SQL est une technique puissante pour optimiser les opérations de base de données, en particulier dans les applications à forte concurrence. En utilisant des tables temporaires et une approche par lots, vous pouvez minimiser les verrouillages de table prolongés et améliorer les performances globales de votre application. N'hésitez pas à personnaliser ce script en fonction de vos besoins spécifiques pour tirer le meilleur parti du traitement par lot.