Suppression des doublons problématique

**crahier** · 10/08/2012, 06h37

Bonjour,

Dans une de mes tables, je souhaite supprimer les doublons.

Cette table est composée de 12 colonnes et contient un peu plus de 8 millions de lignes.

Pour cela, j'utilise la commande suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELETE
FROM   matable_01
WHERE  matable_01.id > ANY
   (SELECT id
    FROM   matable_01 T2
    WHERE  matable_01.id <> T2.id
      AND  matable_01.id_partie    = T2.id_partie
      AND  matable_01.ip    = T2.ip
      AND  matable_01.dataset    = T2.dataset
      AND  matable_01.ordre    = T2.ordre
      AND  matable_01.id_question    = T2.id_question
      AND  matable_01.reponse    = T2.reponse
      AND  matable_01.correct    = T2.correct
      AND  matable_01.score    = T2.score)

Le problème, c'est qu'après 40 minutes, l'exécution de cet ordre SQL n'est toujours pas terminé en en attendant, cela bloque plus de 50 process.

Votre aide et vos avis éclairés seraient d'un grand secour car je suis un peu perdu et désemparé

Merci d'avance,

Christophe

**SQLpro** · 10/08/2012, 10h14

Essayez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WHILE EXISTS(SELECT *
             FROM   matable_01 AS T1
                    INNER JOIN matable_01 AS T2
                          ON T1.id <> T2.id
             WHERE  T1.id_partie   = T2.id_partie   
               AND  T1.ip          = T2.ip
               AND  T1.dataset     = T2.dataset
               AND  T1.ordre       = T2.ordre
               AND  T1.id_question = T2.id_question
               AND  T1.reponse     = T2.reponse
               AND  T1.correct     = T2.correct
               AND  T1.score       = T2.score)
DELETE TOP (1000) T 
FROM   matable_01 AS T
       INNER JOIN matable_01 AS T2
                         ON T1.id <> T2.id
             WHERE  T.id_partie   = T2.id_partie   
               AND  T.ip          = T2.ip
               AND  T.dataset     = T2.dataset
               AND  T.ordre       = T2.ordre
               AND  T.id_question = T2.id_question
               AND  T.reponse     = T2.reponse
               AND  T.correct     = T2.correct
               AND  T.score       = T2.score

Ca journalisera moins et vous pouvez interrompre à tout moment

A +

**Sergejack** · 10/08/2012, 11h45

Vous pourriez essayer de procéder en 2 temps.

1)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT
MAX(id) AS id
INTO tmp_tokeep
FROM matable_01
GROUP BY 
 id_partie
 , ip
 , dataset
 , ordre
 , id_question
 , reponse
 , correct
 , score

2)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
DELETE matable_01
WHERE id NOT IN (
	SELECT TK.id
	FROM tmp_tokeep AS TK
)

2')

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
while 1 = 1
begin
	DELETE TOP(100) matable_01
	FROM tmp_tokeep AS TK
	WHERE id NOT IN (
		SELECT TK.id
		FROM tmp_tokeep AS TK
	)
 
 
	if @@rowcount = 0
		break
end

**crahier** · 10/08/2012, 13h13

Bonjour,

Merci à vous deux, je vais tester vos solutions.

Christophe

**crahier** · 13/08/2012, 08h24

Bonjour,

Envoyé par SQLpro

Essayez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

...

Ca journalisera moins et vous pouvez interrompre à tout moment

A +

J'ai essayé le code que vous proposez mais je reçois cette errreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "T1.id" could not be bound.

Merci pour votre aide.

Christophe

**StringBuilder** · 14/08/2012, 15h05

L'erreur est pourtant explicite : ligne 16, il ne comprends pas le T1.
Normal, en regardant en haut et en bas, on voit que l'alias devrait être T.

**crahier** · 15/08/2012, 13h10

Bonjour,

En effet, je n'avais pas fait attention.

Merci,

Christophe

**Sergejack** · 16/08/2012, 11h26

Envoyé par crahier

Bonjour,

En effet, je n'avais pas fait attention.

Merci,

Christophe

Vous avez eu les même problèmes avec la solution que je vous suggérais ?

**crahier** · 16/08/2012, 11h30

Bonjour Sergejack,

Pour l'instant, l'ordre SQL proposé par SQLpro tourne depuis plus de 24h sans erreur.

Je n'ose pas trop toucher, je laisse faire

Merci en tout cas pour votre aide.

Christophe

**Sergejack** · 16/08/2012, 12h16

Envoyé par crahier

Bonjour Sergejack,

Pour l'instant, l'ordre SQL proposé par SQLpro tourne depuis plus de 24h sans erreur.

Je n'ose pas trop toucher, je laisse faire

Merci en tout cas pour votre aide.

Christophe

Vous pouvez l'interrompre et tentez le mien.

Le travail fait ne sera pas perdu.

Voyez combien de temps prend l'étape 1.
Si elle est trop longue, arrêtez la et revennez à la méthode de SQL Pro.

Sinon, passer à l'étape 2'.
Le travail sera fini bcp plus vite.

**Rei Ichido** · 17/08/2012, 16h50

Envoyé par SQLpro

Essayez : [...]

Personnellement je ferai juste une petite modification, pour :
1) Rendre plus lisible
2) Eviter de refaire le EXISTS (ok c'est pas forcément crucial en perf, mais ça dépend un peu de la sous-requête quand même)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT 1 -- pour initier ROWCOUNT à 1
 
WHILE @@ROWCOUNT > 0
DELETE TOP (1000) T 
FROM   matable_01 AS T
       INNER JOIN matable_01 AS T2
                         ON T.id <> T2.id
             WHERE  T.id_partie   = T2.id_partie   
               AND  T.ip          = T2.ip
               AND  T.dataset     = T2.dataset
               AND  T.ordre       = T2.ordre
               AND  T.id_question = T2.id_question
               AND  T.reponse     = T2.reponse
               AND  T.correct     = T2.correct
               AND  T.score       = T2.score

**fsmrel** · 19/08/2012, 13h04

Bonjour,

Comme votre DELETE est coûteux, essayez une tactique qui peut rendre service dans ce genre d’exercice : définissez une table ayant la même structure que la table matable et procédez par INSERT des seules lignes qui doivent être retenues pour le résultat.

**crahier** · 21/08/2012, 07h06

Bonjour fsmrel,

J'avais pensé à cela aussi mais cela ne risque-t-il pas de générer beaucoup d'i/o ?

Qu'en pensez-vous ?

Merci,

Christophe

**Sergejack** · 21/08/2012, 09h07

Si vous avez d'autres tables liées (foreign key) à celle-ci, vous aurez du mal à faire ce qui vient de vous être proposé.

**crahier** · 21/08/2012, 11h27

Envoyé par Sergejack

Si vous avez d'autres tables liées (foreign key) à celle-ci, vous aurez du mal à faire ce qui vient de vous être proposé.

Bonjour,

Je n'ai pas de foreign key dans cette table.

Je continue à chercher.

Merci à tout le monde pour l'aide apportée !

Christophe

**Waldar** · 21/08/2012, 12h18

Vu le temps que ça prend, vous avez une combinaison d'éléments parmi cette liste (j'ai essayé d'être exhaustif, mais si quelqu'un a une autre idée) :

pas d'index unique sur votre colonne id
pas d'index du tout sur cette même colonne
beaucoup d'index sur les autres colonnes de la table
des clefs étrangères dans d'autres tables qui pointent sur celle-ci
des triggers on delete
un très vieil ordinateur

Envoyé par crahier

J'avais pensé à cela aussi mais cela ne risque-t-il pas de générer beaucoup d'i/o ?

En fonction de la volumétrie à supprimer (en pourcentage) la solution de fsmrel est souvent la plus rapide, mais elle nécessite de repositionner tous les droits, objets et contraintes référentielles.
Cette solution demande donc un peu plus de préparation.

Envoyé par crahier

Je n'ai pas de foreign key dans cette table.

Attention, on cherche des clef étrangères dans d'autres table qui référence celle-ci, pas le contraire.

**fsmrel** · 21/08/2012, 13h48

J'ai été de temps en temps confronté à des problèmes du genre de celui avec lequel crahier est aux prises et j’ai déjà fait part ici de deux expériences caractéristiques.

A vue de nez, dans le cas de crahier, on peut essayer de recopier dans une table temporaire T (par utilitaire pour aller plus vite) les seules lignes à retenir (sur la base du SELECT proposé par crahier), et recharger matable (toujours par utilitaire) à partir de T. C’est bœuf mais généralement efficace.

Si ça ne suffit pas, on creusera...

**crahier** · 21/08/2012, 19h20

Merci à tout les deux pour votre aide.

En cherchant un peu plus loin, sur les 18 millions de rows, il y avait pas loin de 9 millions de "parasites".

Dans la DB, je stocke également l'adresse IP du joueur et j'ai pu supprimer une grande quantité de records.

Demain matin, je relancerai une réindexation pour voir si les performances sont améliorées.

Au niveau des index, je ai plusieurs sur la table, je peux vous copier sa structure si cela vous avez l'occasion d'y jeter un oeil, toute amélioration sera la bienvenue.

Christophe

**crahier** · 21/08/2012, 19h26

Voici la structure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[matable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[id_jeu] [varchar](36) NOT NULL,
	[id_partie] [varchar](35) NOT NULL,
	[ip] [varchar](16) NULL,
	[dataset] [int] NOT NULL,
	[ordre] [int] NOT NULL,
	[id_question] [int] NOT NULL,
	[reponse] [varchar](255) NULL,
	[correct] [bit] NULL,
	[score] [int] NULL,
	[date_maj] [datetime] NOT NULL,
	[reponse_libre] [nvarchar](4000) NULL,
 CONSTRAINT [PK_id_logs_matable] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [_dta_index_matable] ON [dbo].[matable] 
(
	[reponse] ASC,
	[id_jeu] ASC,
	[id_question] ASC,
	[id] ASC,
	[dataset] ASC,
	[ordre] ASC,
	[correct] ASC,
	[score] ASC,
	[id_partie] ASC,
	[ip] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_correct] ON [dbo].[matable] 
(
	[correct] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_dataset] ON [dbo].[matable] 
(
	[dataset] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_doublons] ON [dbo].[matable] 
(
	[id_jeu] ASC
)
INCLUDE ( [id],
[id_partie],
[ip],
[dataset],
[ordre],
[id_question],
[reponse],
[correct],
[score]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_id_question] ON [dbo].[matable] 
(
	[id_question] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ordre] ON [dbo].[matable] 
(
	[ordre] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_score] ON [dbo].[matable] 
(
	[score] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

**Waldar** · 21/08/2012, 20h54

Rendez-vous compte qu'à chaque ligne supprimée SQL-Server va mettre à jour tous les index.
Supprimez vos index (sauf la clef primaire), faites la suppression des données, recréez vos index.

Ce sera nettement plus rapide.

Suppression des doublons problématique

MS SQL Server

Discussions similaires

Partager

Partager