Discussion :

[jobe3141]

Bonjour,

Je suis novice sous sql server 2000 et j'aurai besoin d'aide pour l'optimisation de ma procédure stockée qui met plusieurs heures à s exécuter.

Je reçois mensuellement un fichier csv (20 million de lignes) contenant les soldes des contrats par mois. les champs sont séparés par des ';' et sont délimités par des doubles cotes.

Voici la procédure que j'ai créé et qui mérite d'être largement optimisé.
- J’insère via un bulk insert les données dans une table source
- je met à jour les 1er et le dernier champ de la table en supprimant les doubles cotes.
- J’insère les nouveaux contrats dans la tables de destination
- j'identifie sur quel mois porte les soldes (forcément 1 pas plus)
- Je mets à jour la colonne mois avec les soldes du fichier source.

Script table source et destination

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
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_CONTRAT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_CONTRAT]
GO
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_SOURCE_PPH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_SOURCE_PPH]
GO
 
CREATE TABLE [dbo].[TBL_CONTRAT] (
	[Num_contrat] [bigint] NOT NULL ,
	[CD_type_titulaire] [tinyint] NOT NULL ,
	[Date_naissance] [datetime] NULL ,
	[num_type_contrat] [tinyint] NOT NULL ,
	[Date_ouverture] [datetime] NULL ,
	[Date_fermeture] [datetime] NULL ,
	[Departement] [varchar] (5) COLLATE French_CI_AS NULL ,
	[janvier] [money] NULL ,
	[février] [money] NULL ,
	[mars] [money] NULL ,
	[avril] [money] NULL ,
	[mai] [money] NULL ,
	[juin] [money] NULL ,
	[juillet] [money] NULL ,
	[août] [money] NULL ,
	[septembre] [money] NULL ,
	[octobre] [money] NULL ,
	[novembre] [money] NULL ,
	[décembre] [money] NULL 
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[TBL_SOURCE_PPH] (
	[NO_CNT_DET] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
	[CD_TYP_TTL] [varchar] (50) COLLATE French_CI_AS NULL ,
	[DT_NAI] [nvarchar] (50) COLLATE French_CI_AS NULL ,
	[CD_CNT_TYP] [varchar] (50) COLLATE French_CI_AS NULL ,
	[LB_CNT_TYP] [varchar] (50) COLLATE French_CI_AS NULL ,
	[Département] [varchar] (50) COLLATE French_CI_AS NULL ,
	[DT_EFF_CNT] [nvarchar] (50) COLLATE French_CI_AS NULL ,
	[DT_CLO_CNT] [nvarchar] (50) COLLATE French_CI_AS NULL ,
	[DT_SLD] [nvarchar] (50) COLLATE French_CI_AS NULL ,
	[CPL_PRD_EPA_ORD] [varchar] (50) COLLATE French_CI_AS NULL 
) ON [PRIMARY]
GO

Script proc

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
 
CREATE PROCEDURE SP_IMPORT_OER
@PathFileName varchar(100)
as
DECLARE 
@SQL varchar(2000),
@Mois_source as varchar(30),
@req2 as varchar(2000)
 
truncate table dbo.TBL_SOURCE_PPH
 
 
set @SQL = 'BULK INSERT TBL_SOURCE_PPH FROM "'+@PathFileName+'" WITH (FIELDTERMINATOR =''";"'' , FIRSTROW = 2)'
 
EXEC (@SQL)
 
 
update tbl_source set 
NO_CNT_DET = replace(NO_CNT_DET,'"',''),
CPL_PRD_EPA_ORD = replace(CPL_PRD_EPA_ORD,'"','')
 
 
INSERT TBL_CONTRAT 
(Num_contrat, CD_type_titulaire, Date_naissance, num_type_contrat, 
Date_ouverture, Date_fermeture, Departement)
 
SELECT
cast(NO_CNT_DET as bigint),
cast(a.CD_TYP_TTL as tinyint),
case when isdate(a.DT_NAI)= 1 then cast(a.dt_nai as datetime) else null end as date_naiss, 
cast(a.CD_CNT_TYP as tinyint),
case when isdate(a.DT_EFF_CNT)= 1 then cast(a.DT_EFF_CNT as datetime) else null end as date_EFF, 
case when isdate(a.DT_CLO_CNT)= 1 then cast(a.DT_CLO_CNT as datetime) else null end as date_CLO, 
[Département]
from dbo.TBL_SOURCE_PPH as a
where not exists 
(select * from dbo.TBL_CONTRAT as b
where cast(a.NO_CNT_DET as bigint) = b.Num_contrat)
 
-- Permet d'identifier la colonne de la table final
set @Mois_source =(SELECT top 1 datename(M,DT_SLD) from dbo.TBL_SOURCE_PPH)
 
set @req2='update dbo.TBL_CONTRAT
	set ' + @Mois_source  + ' = cast(CPL_PRD_EPA_ORD as money)
 	FROM dbo.TBL_CONTRAT inner join dbo.TBL_SOURCE_PPH
	on Num_contrat = cast(NO_CNT_DET as bigint)'
exec(@req2)
 
GO

Pour finir j'ai créé des index sur les champs num_contrat, CD_type_titulaire, Date_naissance.

La proc ne fonctionne pas en l'état il a fallut pour la faire fonctionner (en 20h) inserer des commandes dbcc shrinkdatase tempdb pour que les journaux de transactions n'explose pas.
Merci de bien vouloir m'indiquer quels sont les optimisations que je peux apporter à ma base et/ou mon script

Merci à tous et j’espère d'avoir été clair.

[KookieMonster]

Bonjour,

désolé par avance de répondre un peu à côté de la question, mais pensez à migrer vos bases de données SqlServer 2000 vers une version plus récente. Le support étendu se termine en effet... demain ! Voir Politique de Support Microsoft.

[jobe3141]

Bonjour,

Non, à ma connaissance il n'est prévu aucune migration.

Merci quand meme.

[aieeeuuuuu]

Bonjour,

il y a je pense plusieurs choses à améliorer.

Est-ce que [Num_contrat] est la clef primaire de votre table TBL_CONTRAT ? elle n'est pas définie en tant que telle. pouvez vous poster l'index que vous avez créé sur cette colonne ?

Pour les autre points d'amélioration, il serait intéressant de connaitre approximativement la durée de chaque étape.

Mais le plus urgent à mon avis est la colonne NO_CNT_DET de votre table temporaire :
Elle devrait être en BIGINT pour éviter le transtypage dans les jointures. Il serait aussi je pense utile de l'indexer après l'import : à vérifier.

Je pense aussi que vous pouvez vous passer de votre dernier UPDATE pour le mois en modifiant la requête d'insertion

[jobe3141]

Bonjour,

Il n'y a personne qui puisse m'aider???

[jobe3141]

Bonjour aieeeuuuuu

Tout d'abords merci de t'intéresser à mon problème. Voici les réponses à tes questions:

Est-ce que [Num_contrat] est la clef primaire de votre table TBL_CONTRAT ? elle n'est pas définie en tant que telle. pouvez vous poster l'index que vous avez créé sur cette colonne ?
Oui [num_contrat] est la clef primaire.
Comment faire pour récupérer les index??? Je l'ai créé via enterprise manager.

Pour les autre points d'amélioration, il serait intéressant de connaitre approximativement la durée de chaque étape.

Pour insérer un fichier de 20 millions de lignes cela a pris plus de 20h voici donc à peu près les temps de traitement:
- J’insère via un bulk insert les données dans une table source env 1h
- je met à jour les 1er et le dernier champ de la table en supprimant les doubles cotes. 10 min
- J’insère les nouveaux contrats dans la tables de destination env 3h
- j'identifie sur quel mois porte les soldes (forcément 1 pas plus) 1 seconde
- Je mets à jour la colonne mois avec les soldes du fichier source. plus de 15 h

Pour info comme je l'ai précisé dans mon premier post la procèdure ne fonctionne pas en l'état car TEMPDB est plein et ensuite la procédure tourne sans fin. J'ai donc insérer des dbcc shrinkdatabase au milieu des traitements.


Mais le plus urgent à mon avis est la colonne NO_CNT_DET de votre table temporaire :
Elle devrait être en BIGINT pour éviter le transtypage dans les jointures. Il serait aussi je pense utile de l'indexer après l'import : à vérifier.

Je ne peut pas le mettre en BIGINT car dans mon fichier source CSV les champ sont délimités par des double cotes et le bulk insert utilise comme FIELDTERMINATOR =''";"''. Cela implique que mon premier champ (num_contrat) à pour valeur dans ma table TEMP "11111111111111 et le dernier champ à pour valeur 10.00". C'est pour cela que par la suite j'update le premier et le dernier champ en supprimant les doubles côtes.
Si vous voyez une meilleure méthode n'hésitez pas.



Je pense aussi que vous pouvez vous passer de votre dernier UPDATE pour le mois en modifiant la requête d'insertion.

Je ne vois pas comment. Prenons comme exemple je recois un fichier avec 10 num contrat et les soldes du mois de janvier. J'insere donc les nouveaux num contrat dans la table et ensuite j update le mois de janvier avec les montant. Jusque la pas de problème mais avec le fichier du mois de février j'aurais donc 105 num contrat dont 5 nouveaux. il faut donc que j'insere les 5 nouveau et ensuite j'update les montant de février sur les 105.
Si vous voyez une meilleure méthode n'hésitez pas.


Merci pour votre aide

[SQLpro]

Trois choses fondamentales pour ce faire :
1) rajoutez les options :

Code :

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

1
2
BATCHSIZE = 20000000,
KILOBYTES_PER_BATCH = 64

Au BUKL INSERT. L'optimiseur fera un plna plus adapté et vous n'aurez plus à saucissonner votre tempdb.
Éventuellement augmentez la taille de KILOBYTES_PER_BATCH pour trouver le bon réglage.

2) dimensionnez correctement vos fichier de données et de JT pour n'avoir aucune croissance dans la base de destination. Par exemple : 20 Go de données et 8 de JT

3) passez en mode de récupération BULK LOGGED.

Avec ceci vous devriez diviser par 3 au moins les temps de réponse !

Plus d'information dans mon livre sur SQL Server 2012 à paraître cette année, chapitre 6 import/export de données.

A +

[jobe3141]

Bonjour,

Merci pour toutes ces précisions. Mais étant novice pourriez vous m'indiquer comment passer en mode bulk logged et est ce la base tempdb que je dois redimenssionner???

Merci bcp

[SQLpro]

Non, vous devez redimensionner la bas de destination de vos données.

Pour passer en mode BULK LOGGED, faite un ALTER DATABASE ...SET RECOVERY BULK_LOGGED

A +

[SQLpro]

Pourquoi n'est vous pas typé dans la table TBL_SOURCE_PPH ?

D'autre part, il faudrait aussi indexer la table source après l'insert....

A +

[jobe3141]

SQL PRO

Merci pour ces infos supplémentaires. J'aurai encore une question sur le bulk insert. Je pensais devoir le retravailler en utilisant un fichier de format afin de pourvoir modifier le champ NO_CNT_DET en bigint directement de la table source_pph.
Cela me permettrait de ne pas effectuer la conversion lors des jointures et de créer les index sur ce champ comme vous me l'avez précisé. Le souci bien sur c'est que le bulk insert ne fonctionne pas et l'analyseur de requête ne m'affiche aucune erreur. Auriez vous une solution à me proposer:

Voici les données sources:

Code :

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

1
2
3
4
5
6
*
"col1","col2","col3","col4"
"10","Field2","Field3","Field4"
"15","Field2","Field3","Field4"
"46","Field2","Field3","Field4"
"58","Field2","Field3","Field4"

Mon fichier fmt

Code :

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

1
2
3
4
5
6
7.0
4
1       SQLINT       0       7       "\", \ ""        1     Col1		
2       SQLNCHAR       0       100     "\", \ ""      2     Col2		
3       SQLNCHAR       0       100     "\", \ ""      3     Col3		
4       SQLNCHAR       0       100     "\" \ r\n "    4     Col4

Le bulk insert

Code :

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

1
2
3
4
 
BULK INSERT myTestFormatFiles FROM 'd:\import.csv' 
WITH (firstrow = 1, FORMATFILE = 'd:\import.Fmt', 
codepage='RAW', datafiletype='native')

Mon problème réside dans le fichier source car tous les champs sont délimités par des doubles cotes et je ne peux bien sur pas modifier le fichiers source car il fait plus de 2 GB.

[iberserk]

En modifiant simplement la colonne en BIGINT dans la table de destination celà ne résout pas le problème?
Quel est votre erreur?

[jobe3141]

Bonjour

Merci de m'aider mais votre solution ne change rien. L'analyseur de requete que dit que tout s'est bien déroulé mais la table reste vide.

[Arkhena]

Mon problème réside dans le fichier source car tous les champs sont délimités par des doubles cotes et je ne peux bien sur pas modifier le fichiers source car il fait plus de 2 GB.

Ce n'est pas que vous pouvez pas. C'est que vous ne savez pas...
Regardez du côté de perl... Avec une expression régulière, vous faites tout ça en peu d'instructions...