Discussion :

[ATMorphine]

Bonjour à tous.
Domaine : base de données analytiques
SGBD : SQL Server 2005 et SQL Server 2008 (mais si la solution trouvé est que pour 2008 ça m'ira)
Voici mon objectif :
Insérer de nombreuses lignes (Plusieurs millions) dans une table existante le plus rapidement possible.

Option :
en acceptant les insertion concurrente et les accès concurrent à la table via un select (update et delete non utilisé). Sachant qu'on ne peut pas essayer d'inserer deux fois la meme chose (normal car y a la PK) et on ne peut pas essayer de sélectionner des datas qui sont en train d'être insérées.

Table dans laquelle inserer :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Create table maTableDest
(
Champ1 int not null,
Champ2 int not null,
Champ3 int not null,
Champ4 int not null
)
 
PRIMARY KEY CLUSTERED 
(
	[Champ1] ASC,
	[Champ2] ASC,
	[Champ3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Stats de l'index a jour quotidiennement
Fragmentation de l'index : 0 %

Genre de requete pour inserer les donner

Code :

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

1
2
3
4
5
6
7
8
Insert into maTableDest
Select distinct 'valeur en dur' as Champ1
,C2 as Champ2 
,C3 as Champ3
,C4 as Champ4
FROM uneTable with(nolock)
INNER JOIN ....
WHERE ....

Ce que j'ai essayé :
Insert into maTableDest with(tablock)
Select distinct 'valeur en dur' as Champ1
,C2 as Champ2
,C3 as Champ3
,C4 as Champ4
FROM uneTable with(nolock)
INNER JOIN ....
WHERE ....

L'avantage du tablock est que j'augmente de façon importante la vitesse d'insertion (2x plus rapide et l'impact est de plus en plus important avec la volumétrie), les performances sont équivalentes a une insertion dans une table temporaire :

Code :

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

1
2
3
4
5
6
7
8
Select distinct 'valeur en dur' as Champ1
,C2 as Champ2 
,C3 as Champ3
,C4 as Champ4
INTO #Temp
FROM uneTable with(nolock)
INNER JOIN ....
WHERE ....

Mais les accès concurrents sont lockés avec le tablock (c'est le principe d'un côté).

De plus si je fais :

Code :

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

1
2
3
select *
from maTableDest with(nolock)
where ...

Avec en parallèle, une insertion (lancée en 2eme), l'insertion est locké et la les temps explosent :
3 fois plus de temps que si l'insertion était seul. Je me dit que ça vient du fait que le select
pose des lock sur les lignes, requete et enlève les locks (1 à 1 car par ligne)
Je me demandé si le fait d'augmenté la granularité des lock (locké au niveau page voir table) au
lieu de row pouvait être interessant.

J'ai testé le paramètre :

Code :

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

RECOVERY à BULK_LOGGED

qui permet de limité l'écriture de log lors
d'insertion notamment mais l'impact n'est pas présent.
Effectivement, il me semble qu'il faut que la table soit vide pour que le bulk-logged soit vraiment
utilisé. A moins que je me sois trompé dans mon paramètrage.

Donc avez vous d'autres pistes ?

[ATMorphine]

Aucune piste a suggérer ?

[SQLpro]

1) tailler les storages (fichiers) de façon suffisament grande pour 3 à 5 ans d'eploitation
2) multiplier le nombre de fichier de données sur des disques physiques afin de répartir les IO
3) multiplexer le journal de transaction en utilisant du RAID 10 sur 4, 6 ou 8 disques
4) saucissoner vos transactions, en utilisant si possible des blocs de 64 Ko de data
5) éventuellement partitionner votre table sur une colonne critère

A +

[ATMorphine]

Très interessant tous ça.
Je essayer de me document sur les avantages et inconvénient de ce que vous venez de souligner et surtout trouver comment mettre ça en place.

[mikedavem]

En sus de ce que dit SQLPro,

Vous pouvez également voir du côté d'une iinfrastructure SSIS qui permet de charger rapidement les données.

Je sais également qu'avec la version 2008 il est possible d'utiliser le Trace Flag 610 pour pouvoir journaliser au minimum une table déjà remplie avec un index cluster ... (Je n'ai jamais testé personnellement)

++

[ATMorphine]

J'ai essayé le Trace Flag pour éviter un minimum l'écriture de Log en cas d'insertion mais je n'ai vu aucune différence, cela vient peut être aussi d'une mauvaise configuration de la base ou server.

Pour information, je teste dans un environnement vierge, aucune requête extérieur ne perturbe mes tests.

[ATMorphine]

1) tailler les storages (fichiers) de façon suffisament grande pour 3 à 5 ans d'eploitation
2) multiplier le nombre de fichier de données sur des disques physiques afin de répartir les IO
3) multiplexer le journal de transaction en utilisant du RAID 10 sur 4, 6 ou 8 disques
4) saucissoner vos transactions, en utilisant si possible des blocs de 64 Ko de data
5) éventuellement partitionner votre table sur une colonne critère

A +

Ca m'a bien servi merci.