Comment optimiser un insert en masse à partir d'une table volumineuse

Version imprimable

16/02/2017, 21h38
jporet

Comment optimiser un insert en masse à partir d'une table volumineuse

bonjour à tous ,

j'ai une table "table_src" avec des index qui fait + 40 millions de lignes et je dois modifier la structure de la table en ajoutant des colonnes.
j'ai de gros problèmes de performance ça met 4 heures et ça plante car je rencontre des problèmes d'espace disque avec la base tempdb

version sql = sql server 2008 R2 , la mémoire allouée à sql est de 2 Go et il reste 20 Go sur le serveur

voici ce que j'ai fait :
- la base est en mode simple
- j'ai créé une nouvelle table table_dest avec la nouvelle structure avec la clé primaire

SET NOCOUNT ON
insert into dbo.table_dest (...) select .... from dbo.table_src

- après l'insert j'ai créé les index sur la table table_dest.

je ne sais pas si par exemple il est préférable de créer les index sur la table finale avant ou après l'insert
j'aurai voulu faire un test en faisant des insertions par bloc pour maîtriser la taille de la base tempdb mais je ne sais pas comment faire.
au final je ne sais pas trop comment m'y prendre , à part sinon de demander à l'infra d'augmenter la mémoire et l'espace disque du serveur mais ça va prendre des semaines.

merci d'avance pour aide
17/02/2017, 08h03
SQLpro

Avec si peu de ram vous n'arriverez jamais. Les sgbdr traitant les données en mémoire il faut que l'intégralité des données de la table puise figurer en mémoire ainsi que la journalisation ... Commencez par mettre au moins 16 go de ramener à votre serveur et vous verrez que vous allez résoudre tous les problèmes : vitesse, temps et crash...
17/02/2017, 09h33
jporet

merci pour votre réponse , j'ai déjà fait la demande à notre infra pour avoir plus de mémoire...
mais avant que ça soit fait ça va prendre du temps
j'essaye de trouver une solution en parallèle
17/02/2017, 13h23
SQLpro

alors il faut le en splittant la table.

A +
17/02/2017, 13h42
jporet

je suis parti sur cette idée

merci pour votre aide

Bonjour,

Effectivement avec 2Go cela va être difficile. Une fois que vous aurez plus de RAM, les performances (outre cet INSERT) s'en ressentiront immédiatement : les applications qui accèdent à la base de données vont "respirer" :D

Vous pouvez procéder au peuplement de la nouvelle table par les lignes de l'ancienne en copiant les lignes par lots de 10 000, par exemple :

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
-- Extraction des valeurs de clé primaire subrogée de la table source
SELECT	id
INTO	dbo.table_auxiliaire
FROM	dbo.table_source
 
-- Ceci crée implicitement un index cluster
-- qui va bien supporter les ORDER BY qui suivent
ALTER TABLE dbo.table_auxiliaire
ADD CONSTRAINT PK_table_auxiliaire PRIMARY KEY(id)
 
------------------------------------------------------------
 
DECLARE @i int = 0
	, @dt datetime
	, @msg varchar(1024)
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.table_auxiliaire
)
BEGIN
	SELECT	@i += 1
		, @dt = GETDATE();
 
	-- On copie les 10000 lignes suivantes dans la table cible ...
	WITH
		CTE AS
		(
			SELECT	TOP 10000 id
			FROM	dbo.table_auxiliaire
			ORDER	BY id
		)
	INSERT INTO dbo.table_cible
	(
		id
		, les_autres_colonnes
	)
	SELECT		S.id
			, S.les_autres_colonnes
	FROM		CTE AS C
	INNER JOIN	dbo.table_source AS S
				ON C.id = S.id;
 
	-- ... une fois qu'elles sont traitées, on peut les supprimer de la table auxiliaire
	WITH
		CTE AS
		(
			SELECT	TOP 10000 id
			FROM	dbo.table_auxiliaire
			ORDER	BY id
		)
	DELETE FROM	dbo.table_auxiliaire
	FROM		CTE AS C
	INNER JOIN	dbo.table_auxiliaire AS A
				ON C.id = A.id;
 
	-- Sortie dans la console (Onglet Messages de SQL Server Management Studio)
	SET @msg = 'Batch #' + CAST(@i AS varchar(10)) + '	 -	' + CAST(DATEDIFF(millisecond, @dt, GETDATE()) AS varchar(20));
	RAISERROR(@msg, 0, 1) WITH NOWAIT;
END

Et pour suivre le déplacement des lignes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT		S.name + '.' + T.name AS table_name
		, PS.row_count
FROM		sys.dm_db_partition_stats AS PS
INNER JOIN	sys.tables AS T
			ON T.object_id = PS.object_id
INNER JOIN	sys.schemas AS S
			ON S.schema_id = T.schema_id
WHERE		S.name = 'dbo'
AND		T.name IN
		(
			'table_source'
			, 'table_cible'
			, 'table_auxiliaire'
		)

Il est préférable de créer les index une fois que la nouvelle table est complètement peuplée.

@++ ;)

20/02/2017, 14h32
jporet

merci bq pour votre réponse

j'ai splité la table , ça dure 3h maintenant

je vais attendre maintenant d'avoir de la ram en plus et du cpu

merci encore à vous tous pour votre aide
22/02/2017, 09h56
rouardg

Bonjour,

Je ne connais vraiment pas grand chose à SQL Server, mais je suis assez surpris par les réponses proposées ici qui ne portent que sur :
- l'augmentation de la RAM
- le traitement par paquet de lignes

Travaillant sur Oracle, j'aurai utilisé les techniques suivantes :

- table cible créée en NOLOGGING pour qu'il n'y ait pas de journalisation. Bien sur, pour sécuriser l'opération, on effectuera une sauvegarde de la base avant de détruire la table source.
- données insérées dans la table cible en mode Bulk et en une seule fois (pas de lotissement donc) pour ne pas consommer d'espace de Rollback
- utilisation du parallélisme à la fois en lecture sur la table source, et aussi sur la table cible. Le parallélisme est d'autant plus utile si les tables source et cible sont partitionnées.

Du coup, en utilisant ces techniques, je n'ai pas besoin d'allouer de la mémoire à mon instance, car je n'ai majoritairement que des I/O disques.

Par exemple, j'ai pu recréer une table (51 millions de lignes et 22 Go) en seulement 30 minutes, incluant la recréation de 36 index (4 index B*Tree et 32 index Bitmap pour des requêtes en étoile).

Si je n'ai pas eu besoin de plus de RAM, c'est que la table cible avait qq nouvelles colonnes, peuplées par la jointure de la table source avec 2 petites tables qui servaient de Lookup et tenaient donc en mémoire. Quant à l'instance Oracle, elle n'avait que 4 Go d'allocation mémoire au total.

Ma question : n'est-il pas possible de faire qq chose de similaire sous SQL Server ?

Merci
22/02/2017, 13h31
SQLpro

Citation:

Envoyé par rouardg

Bonjour,

Je ne connais vraiment pas grand chose à SQL Server, mais je suis assez surpris par les réponses proposées ici qui ne portent que sur :
- l'augmentation de la RAM
- le traitement par paquet de lignes

Travaillant sur Oracle, j'aurai utilisé les techniques suivantes :

- table cible créée en NOLOGGING pour qu'il n'y ait pas de journalisation. Bien sur, pour sécuriser l'opération, on effectuera une sauvegarde de la base avant de détruire la table source.

SQL Server ne permet pas de ne pas journaliser. SQL Server transactionne tout (come le prévoit la norme SQL) y compris le DDL et le DCL

Citation:

- données insérées dans la table cible en mode Bulk et en une seule fois (pas de lotissement donc) pour ne pas consommer d'espace de Rollback

SQL Server étant par défaut en autocommit, toute insertion est faite par défaut en mode "bulk".

Citation:

- utilisation du parallélisme à la fois en lecture sur la table source, et aussi sur la table cible. Le parallélisme est d'autant plus utile si les tables source et cible sont partitionnées.

SQL Server travaille massivement en parallèle de manière automatique, sans même que vous ayez à installer un module (parallel query d'Oracle par exemple) et sans que vous ayez besoin de le préciser dans la requête.

Citation:

Du coup, en utilisant ces techniques, je n'ai pas besoin d'allouer de la mémoire à mon instance, car je n'ai majoritairement que des I/O disques.

Par exemple, j'ai pu recréer une table (51 millions de lignes et 22 Go) en seulement 30 minutes, incluant la recréation de 36 index (4 index B*Tree et 32 index Bitmap pour des requêtes en étoile).

Si je n'ai pas eu besoin de plus de RAM, c'est que la table cible avait qq nouvelles colonnes, peuplées par la jointure de la table source avec 2 petites tables qui servaient de Lookup et tenaient donc en mémoire. Quant à l'instance Oracle, elle n'avait que 4 Go d'allocation mémoire au total.

Ma question : n'est-il pas possible de faire qq chose de similaire sous SQL Server ?

Merci

En journalisant en mode SIMPLE et en découpant votre maj par paquet de lignes.

A +
23/02/2017, 10h06
rouardg
Merci pour ces précisions. Je note donc que SQL Server fait du parallélisme de manière automatique, et que par défaut, il travaille en Bulk.

Maintenant, je ne suis pas sur que la définition du "Bulk" soit la même entre Oracle et SQL Server.

En Oracle, on demande à l'optimiseur SQL de faire du chargement en Bulk en utilisant le Hint APPEND, comme cela :
Code:

1 2 insert /*+ APPEND */ into table_dest (...) select .... from table_src ; commit ;
Par rapport à un chargement conventionnel, cela veut dire 2 choses :

1) les données sont rajoutées à la fin de la table, en utilisant un segment temporaire (lequel contient les blocs de donnnées). Si l'insertion réussie, alors le segment temporaire est rattaché au segment de la table. Mais si l'insertion échoue, on rollbacke. C'est très rapide car il suffit de supprimer ce segment temporaire et on récupère l'espace disque occupé.

Dans un chargement conventionnel, le Rollback est "lent", car il faut aller supprimer chaque ligne insérée. De plus, on ne récupère pas l'espace disque consommée par l'injection des nouvelles données.

Je pense que SQL Server fonctionne de manière similaire.

2) le chargement en Bulk veut dire aussi que les données (enfin plutôt les blocs de données qui font 8 Ko par défaut, ce que vous devez appeler des pages sous SQL Server), sont directement écrits dans les fichiers physique de la base, sans passer par le cache de données.

Au final, Oracle sait faire de la lecture et de l'écriture dans les tables, sans avoir parfois besoin de passer par la mémoire allouée à l'instance. C'est ce qu'on appelle du "Direct Path Read" et "Direct Path Write", et c'est notamment utilisé dans les opération de CTAS (Create Table As Select) ou bien d'Insert /*+ APPEND */, lorsque l'on veut réorganiser des tables par exemple.

Je me demande donc si SQL Server bénéficie d'un mécanisme similaire. A vous lire, j'ai l'impression que non, puisque vous parler d'augmenter la mémoire de l'instance.

Mais je me trompe surement.
23/02/2017, 15h33
SQLpro

Citation:

Envoyé par rouardg

Merci pour ces précisions. Je note donc que SQL Server fait du parallélisme de manière automatique, et que par défaut, il travaille en Bulk.

Maintenant, je ne suis pas sur que la définition du "Bulk" soit la même entre Oracle et SQL Server.

Tout dépend du mode de journalisation : si SIMPLE ou BULK_LOGGED alors un INSERT INTO ... SELECT ... n'est journalisé que pour la requête et pas pour les données. Il y a écriture minimale dans le journal de transaction (juste la commande SQL, pas les données) et les nouvelles pages sont mise en mémoire (donc un seul accès disque pour journaliser la commande INSERT... Mais en mode FULL, l'intégralité des données de l'INSERT est journalisé dans le JT.

Citation:

Je pense que SQL Server fonctionne de manière similaire.

En fait non, voir ci-avant.

Citation:

2) le chargement en Bulk veut dire aussi que les données (enfin plutôt les blocs de données qui font 8 Ko par défaut, ce que vous devez appeler des pages sous SQL Server), sont directement écrits dans les fichiers physique de la base, sans passer par le cache de données.

C'est l'inverse. Les données sont oujours mise en cache. En cas de pression mémoire (pas assez de cache), SQL Server déclenche une opération CHECKPOINT pour forcer l'écriture des données. C'est cela qui vous arrive...

Citation:

Au final, Oracle sait faire de la lecture et de l'écriture dans les tables, sans avoir parfois besoin de passer par la mémoire allouée à l'instance. C'est ce qu'on appelle du "Direct Path Read" et "Direct Path Write", et c'est notamment utilisé dans les opération de CTAS (Create Table As Select) ou bien d'Insert /*+ APPEND */, lorsque l'on veut réorganiser des tables par exemple.

Je me demande donc si SQL Server bénéficie d'un mécanisme similaire. A vous lire, j'ai l'impression que non, puisque vous parler d'augmenter la mémoire de l'instance.

Mais je me trompe surement.

Effectivement c'est l'inverse.

A +
23/02/2017, 15h41
rouardg

Un grand merci SQLpro.

C'est très clair.

Oracle et SQL Server fonctionnent de manière très différente sur ce sujet, et je comprends pourquoi une telle allocation mémoire est demandée et nécessaire, et pourquoi on fait une MAJ par paquets en attendant de disposer de tant de mémoire.

Encore merci.