Réponse.

L'archivage des données est l'une des tâches les plus importantes pour les systèmes OLTP à fort trafic avec de grandes tables. Historiquement, les premières tentatives de mise en œuvre de cela étaient très simples : les données étaient supprimées ou copiées dans des tables séparées manuellement via des scripts ou des applications. Plus tard, des approches plus systématiques sont apparues, tenant compte de l'intégrité transactionnelle et de l'impact minimal sur le fonctionnement principal de la base de données.

Le problème ici réside non seulement dans le transfert physique de l'information, mais aussi dans le maintien de la cohérence, la minimisation des blocages et l'assurance d'une haute performance. Les erreurs lors de l'archivage peuvent entraîner des pertes de données, des blocages d'utilisateurs ou une augmentation significative de la charge.

La solution consiste à utiliser des opérations par lots avec contrôle transactionnel, ainsi qu’à créer des tables d'archives spéciales avec une structure identique, ou à automatiser via un planificateur de tâches et des procédures.

Exemple de code :

-- Transférer 5000 enregistrements de plus d'un an vers la table d'archive
INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < DATEADD(year, -1, GETDATE())
    AND id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);
DELETE FROM orders WHERE id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);

Caractéristiques clés :

Transfert des anciennes données par lots pour réduire la charge.
Coordination des opérations d'insertion et de suppression via des transactions.
Planification de l'automatisation via un calendrier de tâches et des procédures.

Questions pièges.

Quel est le risque d'une suppression massive d'anciennes enregistrements et comment l'éviter ?

Une suppression massive peut entraîner une escalade des blocages et un ralentissement de l'ensemble de la base. Cela peut être évité en procédant à la suppression par petites portions à l'intérieur d'une boucle ou en utilisant LIMIT/TOP, si pris en charge par le SGBD.

WHILE 1=1
BEGIN
  DELETE TOP (1000) FROM orders WHERE order_date < '2023-01-01';
  IF @@ROWCOUNT = 0 BREAK;
END

Peut-on utiliser TRUNCATE pour supprimer les données archivées ?

TRUNCATE supprime toutes les lignes d'une table et ne convient pas pour un nettoyage conditionnel de certaines lignes. Il ne déclenche pas de triggers, ne supporte pas WHERE et est utilisé uniquement pour un nettoyage complet, et non pour une archivage sélective.

Comment garantir que le transfert s'est effectué correctement si la suppression et l'insertion sont effectuées dans des transactions différentes ?

Il vaut mieux effectuer le transfert de lignes dans une seule transaction : d’abord copier dans l'archive, puis supprimer de la principale. Sinon, vous pouvez obtenir une incohérence en cas d'échec entre les opérations.

BEGIN TRANSACTION
INSERT INTO archive_orders SELECT * FROM orders WHERE ...
DELETE FROM orders WHERE ...
COMMIT

Erreurs typiques et anti-patterns

Suppression de gros volumes de données par une seule requête, provoquant des blocages.
Archivage sans vérifier que toutes les lignes ont bien été transférées.
Utilisation de TRUNCATE au lieu de DELETE — entraîne la perte de toutes les données de la table.

Exemple de la vie

Cas négatif

Un ingénieur exécute un script sur un million d'enregistrements DELETE FROM logs WHERE event_date < '2022-01-01' pendant une journée de travail.

Avantages :

Le script est simple.

Inconvénients :

La table entière est verrouillée, les utilisateurs ne peuvent pas travailler, le processus dure des heures, il est impossible de revenir en arrière sans sauvegarde.

Cas positif

Planification du transfert de 5000 lignes via une procédure stockée et un travail programmé la nuit, avec journalisation du succès de chaque portion.