J'ai été confronté à des millions de suppressions de données au jour le jour.
En gros, j'ai 4 tables.
code to create primary keys and constraints
J'essaie de supprimer des données plus de 10 jours dans toutes les tables.
J'en supprimerai probablement environ un million dans chaque table. J'ai créé une procédure stockée pour effectuer ces opérations.
Les étapes que j'ai suivies pour supprimer les données sont
Étape 1: Déplacer les derniers jours (données que je dois conserver) vers une table temporaire
DROP TABLE Table_A_old
Étape 2: Renommer la table principale en ancienne table (table avec toutes les données des jours)
Insert into Table_A select * from Table_A_old
Étape 3: Renommez la table temporaire en table principale (table avec les données entre les derniers jours et jusqu'à la date)
exec sp_rename 'Table_A_temp', 'Table_A'
Étape 4: Interrogez la table temporaire avec la période si de nouvelles données sont inséré pendant le processus de copie
exec sp_rename 'Table_A', 'Table_A_Old'
Étape 5: Supprimer les anciennes tables
select * into Table_A_Temp from Table_A where <<where clause last 10 days to till date>>
Étape 6: Créer des clés et des contraintes dans main table (signifie table renommée)
Table_A Table_B Table_C Table_D
Problème:
Si j'insère continuellement des données dans la table pendant que la procédure stockée est en cours d'exécution , Je perds des données pendant quelques secondes.
Cas 1: lors du changement de nom de la table
lorsque je renomme la table principale en ancienne et la table temporaire en main
J'obtiens une erreur d'objet invalide (cette table est une erreur d'existence)
Cas 2: Deux de mes ta Les bles ont une relation de clé étrangère
Si j'insère des données avant de créer des contraintes et des clés, j'obtiens des erreurs liées.
Comment gérer et supprimer correctement les données sans perdre de données. p >
Veuillez conseiller les meilleures pratiques.
3 Réponses :
Pour éviter de perdre des données, je supprimerais simplement les enregistrements où ils se trouvent plutôt que de créer / renommer des tables. SQL Server s'occupera des insertions et des suppressions pour que vous évitiez la perte de données. Il existe plusieurs façons de procéder. Voici une suggestion.
Étape 1: Obtenez l'ID / les identifiants des lignes que vous souhaitez supprimer et stockez-les dans la table temporaire. P >
DELETE t1 FROM Table_A t1 INNER JOIN #TEMP_Table_A_RowsToDelete t2 ON t1.[Id] = t2.Id
Vous aurez alors une table temporaire #TEMP_Table_A_RowsToDelete contenant les valeurs Id des lignes que vous allez supprimer. p>
Étape 2 ( facultative ): utilisez ce tableau pour supprimer toutes les données associées dans les tables référencées si elles existent.
DELETE t1 FROM ForeignKeyTable t1 INNER JOIN #TEMP_Table_A_RowsToDelete t2 ON t1.[ForeignKeyColumn] = t2.Id
Cela supprimera toutes les données liées dans les tables liées à la clé primaire de votre table principale. Vous répéterez cette opération pour toutes les tables liées pour vous assurer que la contrainte de clé étrangère ne vous empêche pas de supprimer des lignes dans la table parente.
Étape 3: Supprimez les lignes de la table principale.
SELECT Id INTO #TEMP_Table_A_RowsToDelete FROM Table_A WHERE <<your_date_column <= 10 days ago>>
Cela supprimera les lignes datant de plus de 10 jours, en fonction de ce que vous avez ajouté à la table temporaire à l'étape 1. Cela ne devrait pas poser de problèmes si vous avez supprimé toutes les données associées à l'étape 2.
Vous devrez répéter les étapes ci-dessus pour chaque tableau de votre liste.
Il s'agit probablement d'un besoin permanent.
Ce que vous devez faire est de partitionner les tables. L'endroit pour être est d'apprendre le partitionnement dans le documentation .
La méthode la plus simple est probablement la suivante:
Pour la troisième étape, chargez simplement les données les plus récentes souhaitées.
Ensuite, à l'avenir, vous pourrez simplement supprimer la partition la plus ancienne chaque jour. Vous pouvez configurer un travail de l'Agent SQL Server pour ce faire et le système fonctionnera automatiquement.
Je pensais à une option pour faire insert / update et trigger supprimé sur la table_A. Ce déclencheur copie les données dans #TEMP_Table. Vous pouvez renommer les deux tables après 10 jours.
Étape 1: créez un déclencheur pour copier les données dans la nouvelle table "#TEMP_Table"
DROP TABLE Table_A_old
Étape 2 attendez 10 jours. Vérifiez si deux tables ont les mêmes données
Étape 3 Renommer les tables
exec sp_rename 'Table_A', 'Table_A_Old' exec sp_rename '#TEMP_Table', 'Table_A'
Étape 4 supprimer l'ancienne table.
CREATE TRIGGER trig_table_a_copy
ON [Table_A]
AFTER UPDATE
AS BEGIN
(excess code)
END
AFTER INSERT
AS BEGIN
(excess code)
END
AFTER DELETE
AS BEGIN
(excess code)
END
GO
qu'est-ce qui ne va pas avec la commande
DELETE?J'ai tendance à faire quelque chose comme
WITH cte AS (SELECT TOP 1000 * FROM Table_A WHERE NOT (<< where clause last 10 days to till date >>)) DELETE ctedans une boucle pour éviter de verrouiller la table@ RadimBača la suppression de plus de 5000 enregistrements à la fois verrouille la base de données
avez-vous undex pour où la colonne à supprimer ???
@scaisEdge Nous n'avons aucun index pour les champs de la clause where. En fait, j'utilise la date et le guid correspondant dans la clause where. Le problème se produit lors du changement de nom.
si vous ajoutez un index approprié sur la colonne concernée par la suppression, vous pouvez améliorer les performances de suppression et annuler ce complexe créer, déposer, insérer, sélectionner ...
envisager de partitionner la table avec l'utilisation de la fenêtre coulissante
À quelle fréquence les 4 tableaux sont-ils mis à jour / insérés avec de nouvelles données? Je pose cette question pour vérifier si nous pouvons exécuter le SP lorsqu'il n'y a pas d'insertions dans le tableau. Si les tableaux sont mis à jour toutes les 5 minutes, cela devient très délicat
Les données @BlackSwan sont insérées toutes les secondes
@scaisEdge la table que nous supprimons présente une fréquence élevée d'insertions et de mises à jour. L'instruction de suppression, même si elle prend moins de 5 minutes, entraînera toujours des délais d'expiration pour les requêtes d'insertion. C'est pourquoi nous sommes passés de la suppression à cette approche
5 minutes sans index sur millions ou lignes dans la jointure .. .. je vous suggère une bonne indexation pour les performances .. de toute façon .. en utilisant votre méthode lors de l'échange de nom de table, vous pourriez avoir une erreur pour table manquante ..
La suppression de lignes suppose la perte de données supprimées, n'est-ce pas? Vous devez décider si vous allez réellement supprimer les données ou les déplacer quelque part.