[2005] Refaire plusieurs fois la même table, bonne idée ?

[Sergejack]

Bonjour.

J'ai plusieurs lignes qui sont regroupées par une clé indexée dans une table.
Ces lignes ont une durée d'existante très courte dans le système, et les INSERT et DELETE se succèdent rapidement.
Comme ces deux opérations causent le recalcule de l'index, je me demandait si plutôt que de regroupé via cette clé, je faisais une table propre par groupe, alors j'obtiendrai un meilleur résultat.

TABLE Base
cleGroupe INT (index non cluster)
, valeur INT

(1, 1)
(1, 2)
(1, 3)
(2, 1)
(2, 3)

Deviendrait =>

TABLE Resultat_1
valeur INT

(1)
(2)
(3)

TABLE Resultat_2
valeur INT

(1)
(3)


Cela ne causerait plus de recalcule d'index.
Par contre cela causerait la création et la suppression fréquentes de nouvelles tables.

Est-ce une approche judicieuse selon vous ? Que me conseillez-vous ?

[SQLpro]

Jamais !
1) comment rajouter un groupe ?
2) sauf si l'insertion est monotone, l'index sera de toutes façon recalculé
3) pour les procédures de maintenance ou la modif, vous serez obliger de passer en revue toutes les tables.

Mais pour minimiser l'impacte de la fragmentation, vous pouvez introduire un fill factor de 80% par exemple, dans votre table.

A +

[Sergejack]

Envoyé par SQLpro

Jamais !
1) comment rajouter un groupe ?
2) sauf si l'insertion est monotone, l'index sera de toutes façon recalculé
3) pour les procédures de maintenance ou la modif, vous serez obliger de passer en revue toutes les tables.

Mais pour minimiser l'impacte de la fragmentation, vous pouvez introduire un fill factor de 80% par exemple, dans votre table.

A +

Hé bien en fait, un groupe n'a d'intérêt que dans le contexte du groupe même.
Ç'est à dire qu'à aucun moment j'ai besoin de récupérer ou modifier la moindre information sur plus d'un groupe à la fois.
Pour le rajout, il s'agit de la création d'une nouvelle table avec une syntaxe de nommage rigoureuse.

Les seules choses qui devraient m'éloigner de la décision de travailler par tables seraient :
1) la création et la suppression d'une table auraient un surcoût important (je ne leur en connais pas) comparativement à un insert+delete avec index.
2) Ils existent des mécanismes (que j'ignorerais) pour fragmenter (au sens non technique) virtuellement une seule table.

Je vais faire quelques tests et exploser les résultats.

[Sergejack]

Voilà ce que ça donne.

D'abord, l'opération en DELETE + INSERT pures.
Ensuite, l'opération avec des UPDATE (champ erased) qui remplacent jusqu'au dernier DELETE (exclu) pour limiter la mise à jour de l'index.
Finalement (le plus rapide), l'opération sur une table propre au traitement et sans index.

Code :

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test56]') AND type IN (N'U'))
DROP TABLE [dbo].[test56]
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test56_alt]') AND type IN (N'U'))
DROP TABLE [dbo].[test56_alt]
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test56_1]') AND type IN (N'U'))
DROP TABLE [dbo].[test56_1]
GO
 
CREATE TABLE test56 (
	cle INT
	, valeur INT
)
GO
 
CREATE TABLE test56_alt (
	cle INT
	, valeur INT
	, erased BIT NOT NULL
)
GO
 
CREATE NONCLUSTERED INDEX IDX_cle ON test56(cle)
GO
 
DECLARE @D DATETIME;
DECLARE @DD DATETIME;
SET @D = GETDATE();
SET @DD = @D;
 
INSERT test56 (
	cle
	, valeur
)
SELECT
	1
	, n
FROM MyGolf.dbo.v_VIRTUAL_NUMBERS AS N
WHERE n BETWEEN 1 AND 100000
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'INSERT 1';
SET @D = GETDATE();
 
DELETE TOP(33) PERCENT test56
WHERE cle = 1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 1';
SET @D = GETDATE();
 
DELETE TOP(55) PERCENT test56
WHERE cle = 1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 2';
SET @D = GETDATE();
 
DELETE test56
WHERE cle = 1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 3';
SELECT DATEDIFF(ms, @DD, GETDATE()), 'TOTAL';
SET @D = GETDATE();
SET @DD = @D;
 
INSERT test56_alt (
	cle
	, valeur
	, erased
)
SELECT
	1
	, n
	, 0
FROM MyGolf.dbo.v_VIRTUAL_NUMBERS AS N
WHERE n BETWEEN 1 AND 100000
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'INSERT 1';
SET @D = GETDATE();
 
UPDATE TOP(33) PERCENT test56_alt
SET erased = 1
WHERE cle = 1
AND erased = 0
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 1';
SET @D = GETDATE();
 
UPDATE TOP(55) PERCENT test56_alt
SET erased = 1
WHERE cle = 1
AND erased = 0
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 2';
SET @D = GETDATE();
 
DELETE test56_alt
WHERE cle = 1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 3';
SELECT DATEDIFF(ms, @DD, GETDATE()), 'TOTAL';
SET @D = GETDATE();
SET @DD = @D;
 
SELECT
	n
INTO test56_1
FROM MyGolf.dbo.v_VIRTUAL_NUMBERS AS N
WHERE n BETWEEN 1 AND 100000
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'INSERT 1';
SET @D = GETDATE();
 
DELETE TOP(33) PERCENT test56_1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 1';
SET @D = GETDATE();
 
DELETE TOP(55) PERCENT test56_1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 2';
SET @D = GETDATE();
 
DROP TABLE test56_1
 
SELECT DATEDIFF(ms, @D, GETDATE()), 'DELETE 3';
SELECT DATEDIFF(ms, @DD, GETDATE()), 'TOTAL';
SET @D = GETDATE();
SET @DD = @D;

Résultats :

Code :

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
 
 
(100000 row(s) affected)
2210 INSERT 1
 
(33000 row(s) affected)
966 DELETE 1
 
(36850 row(s) affected)
910 DELETE 2
 
(30150 row(s) affected)
470 DELETE 3
4556 TOTAL
 
(100000 row(s) affected)
1090 INSERT 1
 
(33000 row(s) affected)
820 DELETE 1
 
(36850 row(s) affected)
520 DELETE 2
 
(100000 row(s) affected)
1193 DELETE 3
3623 TOTAL
 
(100000 row(s) affected)
113 INSERT 1
 
(33000 row(s) affected)
480 DELETE 1
 
(36850 row(s) affected)
493 DELETE 2
0 DELETE 3
1086 TOTAL

La performance y est reste à savoir s'il n'y a pas mieux à faire.

[iberserk]

Détail des index présents?
Vous ne gérez pas ici l'ajout dynamique de la table ainsi que l'impact de sa création sur le temps total...

Si ce n'est pas indiscret quel est le contexte d'utilisation de ce 'modèle'?

[Sergejack]

Envoyé par iberserk

Détail des index présents?

Tout est dans le code*.

* À l'exception (oubli) de l'index pour test56_alt qui est le même que pour test56.

Envoyé par iberserk

Vous ne gérez pas ici l'ajout dynamique de la table ainsi que l'impact de sa création sur le temps total...

Ben si, je prends mes mesures de temps avant la création et après la suppression de la table (cela ne concerne que la table test56_1).

Envoyé par iberserk

Si ce n'est pas indiscret quel est le contexte d'utilisation de ce 'modèle'?

C'est à voir comme des données temporaires dont l'usage est fait par une série de queries depuis ASP.Net.
Ces queries vont éliminer des valeurs au fur et à mesure (il n'y a qu'un seul insert initial) selon des mécanismes divers et complexes.

[iberserk]

Ben si, je prends mes mesures de temps avant la création et après la suppression de la table

Autant pour moi j'avais cherché un CREATE TABLE... zappant de fait le SELECT... INTO.

Ce qui suit sort du cadre de SQL SERVER mais quel intérêt avez vous à stocker cela en base? Ces données doivent'elles forcement être persistées?

Ceci dit pour moi la solution que vous présentez, si elle peut marcher induit des mécanismes qui s’éloignent de ma conception de l'utilisation d'un SGBDR (fourre tout servant de stockage d'appoint) et une complexité (détection de la présence d'une table, création de cette dernière avec nom dynamique... suppression etc) qui ne me parait pas justifié ici...

[Waldar]

Est-ce qu'à ce moment-là l'index se justifie ?
Sur mon environnement :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Alimentation de test56_1
 
16	INSERT 1
13	DELETE 1
33	DELETE 2
0	DELETE 3
63	TOTAL
 
-- Alimentation de test56 (sans index, sans PK)
 
16	INSERT 1
30	DELETE 1
30	DELETE 2
0	DELETE 3
76	TOTAL

Pas de réelle différence.
À évaluer sur votre environnement bien entendu.

[Sergejack]

Les données bien que temporaires sont stockées en DB car, elle n'ont d'intérêt que pour les requêtes et qu'elles peuvent représenter un nombre important de lignes (trop important que pour tenir dans une clause IN*).

* la forme la plus brève pour faire un test sur une valeur.

[Sergejack]

Envoyé par Waldar

Est-ce qu'à ce moment-là l'index se justifie ?
Sur mon environnement :

Code :

Pas de réelle différence.
À évaluer sur votre environnement bien entendu.

L'index est utile lorsque ces données servent à faire certains SELECT.
Car on pourrait simultanément avoir plusieurs groupe de centaines de milliers de lignes chacun.

D'ailleurs, à priori ces SELECT eux aussi profiteraient de se faire sur des table test56_1, test56_2, test56_3, ...
Un table scan sera plus rapide sur la table test56_1 (dont toutes les valeurs devront quand même être lues) plutôt qu'une recherche sur index et lecture de la ligne sur la table test56 pour cle = 1.

Donc d'un point de vue performance, je suis confiant que ce soit un plus.
Mais je me demande quand même s'il n'y a pas d'autres méthodes que j'ignorerais.

[Waldar]

Envoyé par Sergejack

L'index est utile lorsque ces données servent à faire certains SELECT.
Car on pourrait simultanément avoir plusieurs groupe de centaines de milliers de lignes chacun.

Quelques groupes avec des centaines de milliers de lignes, au contraire je dirai que l'index n'est pas assez sélectif pour être utile.
Si ce sont des milliers de groupes, c'est une autre affaire.

Peut-être une solution du côté du partitionnement, si vous avez une idée à l'avance des groupes ?

[Sergejack]

Envoyé par Waldar

Quelques groupes avec des centaines de milliers de lignes, au contraire je dirai que l'index n'est pas assez sélectif pour être utile.
Si ce sont des milliers de groupes, c'est une autre affaire.

C'est vrai mais je ne comprends pas pourquoi.

Envoyé par Waldar

Peut-être une solution du côté du partitionnement, si vous avez une idée à l'avance des groupes ?

Non, ces groupes sont totalement dynamiques (j'utilise d'ailleurs une autre table pour en gérer les identifiants (autoinc)).