Amélioration performance d'insertion
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:
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:
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:
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:
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:
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 ?
