Optimisation d'une SP de matching [Cursor et Table-Valued-Function]

Version imprimable

Bonjour,

Je cherche à optimiser en temps, un traitement SP qui parcourt (via un Curseur) toutes les lignes d'une table (150.000) pour envoyer à chaque fois deux colonnes comme paramètres d'une Table-Valued-Function.

Cette fonction fait le matching entre ces deux paramètres reçus et une colonne d'une table.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Create function [dbo].[findNewPerson] ( @nom as varchar(4000), @prenom as varchar(4000) ) returns table AS return SELECT WCS_NomComplet, WCS_StringAliases, WCS_Uid, WCS_ActualDateMaj from db_ref..LAB_WCS_WorldCheckStrings WHERE LEN(@nom) > 0 AND ( WCS_StringAliases like '% ' + @nom + ' %' + @prenom + '[^a-zA-Z0-9]%' OR WCS_StringAliases like @nom + ' %' + @prenom + '[^a-zA-Z0-9]%' )
Avez-vous des idées pour optimiser le temps d'exécution de cette SP surtout que j'aurai à l'exécuter chaque jour.

Merci !

18/01/2013, 10h26
aieeeuuuuu

Bonjour,

Concernant la fonction que vous avez postée :
- indiquez le nom du schéma de la table LAB_WCS_WorldCheckStrings
- Vous devriez lier la fonction au schéma, elle serait déterministe
- êtes vous sur que les type de @nom et @prenom soient les bons (y compris la longueur), ça éviterait du transtypage

par contre vous n'avez pas mis le code de la procédure qui appelle cette fonction. Vous indiquez y utiliser un curseur, vous devriez la recoder sans...

et vérifier l’existence et la pertinence des index... (envoyez nous les DLL de vos tables en jeu, avec les index).

Quel est le temps d’exécution de votre procédure principale ?

Bonjour,

Merci pour cette réponse.
Le schéma de la table LAB_WCS_WorldCheckStrings est :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE dbo.LAB_WCS_WorldCheckStrings(
	WCS_Uid int NOT NULL,
	WCS_NomComplet varchar(100) NOT NULL,
	WCS_StringAliases varchar(4000) NULL,
	WCS_DateEntree datetime NULL,
	WCS_DateMAJ datetime NULL,
	WCS_ActualDateMaj datetime NULL,
 CONSTRAINT PK_LAB_WCS_WorldCheckStrings PRIMARY KEY CLUSTERED 
(
	WCS_Uid ASC
)

Et la procédure qui l'appelle :

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
 
-- #####################################################################################################################################################
Declare @Nom varchar(300)
Declare @NomMarital varchar(300)
Declare @Prenom varchar(300)
Declare @CodTiers varchar(300)
DECLARE @CodCompte VARCHAR(23)
Declare @Nb AS INT
Declare @Nb2 AS INT
DECLARE @Uid AS INT
DECLARE @AliasesString AS VARCHAR(4000)
-- #####################################################################################################################################################
 
DECLARE CurrData CURSOR READ_ONLY FOR SELECT  GTI_CodTiers,GCO_CodCompte,GTI_NomLong,GTI_Prenom,GTI_NomMarital FROM 
								   db_ldw_full.dbo.CLI_GTI_GeneriquesTiers 
								   LEFT JOIN db_ldw_full..CLI_GCO_GeneriquesComptes
								   ON GTI_CodTiers = GCO_CodTiersPrincipal
								   WHERE GTI_NumStatutPers = '0' 
OPEN CurrData;
	FETCH NEXT FROM CurrData into  @CodTiers,@CodCompte,@Nom,@Prenom,@NomMarital
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--print @Nom +  ' ' + @Prenom + ' '
		select @Nb=COUNT(*) from db_ref.dbo.findNewPerson(@Nom,@Prenom)
		IF @Nb>0 
		BEGIN 
			SELECT @AliasesString = WCS_StringAliases, @Uid = WCS_Uid FROM db_ref.dbo.findNewPerson(@Nom,@Prenom) 
			INSERT INTO dbo.LAB_WCC_WorldCheckControl (WCC_CodTiers,WCC_CodCompte,WCC_Nom,WCC_Uid,WCC_Prenom,WCC_WorldCheckString,WCC_Matching) VALUES
			(@CodTiers,@CodCompte,@Nom,@Uid,@Prenom,@AliasesString,'PP : Correspondance avec le nom de naissance et prénom')
			--PRINT @CodTiers + ' ' + @Nom +  ' ' + @Prenom + ' ' + CAST (@Nb AS VARCHAR(10)) 
		END
 
		FETCH NEXT FROM CurrData into @CodTiers,@CodCompte,@Nom,@Prenom,@NomMarital
	END
CLOSE CurrData
DEALLOCATE CurrData
END

Sinon, que voulez-vous dire par lier la fonction au schéma ?
Les types @nom et @prenom doivent rester au varchar(4000), j'ai de longues chaines de caractères.

Le temps moyen d'exéc du traitement est entre 3 et 4h

Qu'est-ce que ça donne si vous remplacez votre procédure principale par ceci (a faire sur une base de test ! ):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
INSERT INTO dbo.LAB_WCC_WorldCheckControl (
		WCC_CodTiers,
		WCC_CodCompte,
		WCC_Nom,
		WCC_Uid,
		WCC_Prenom,
		WCC_WorldCheckString,
		WCC_Matching
	) 
	SELECT 
		GTI_CodTiers,
		GCO_CodCompte,
		GTI_NomLong,
		WCS_Uid,
		GTI_Prenom,
		WCS_StringAliases,
		'PP : Correspondance avec le nom de naissance et prénom'
	FROM	db_ldw_full.dbo.CLI_GTI_GeneriquesTiers 
	LEFT JOIN	db_ldw_full..CLI_GCO_GeneriquesComptes
	   ON			GTI_CodTiers = GCO_CodTiersPrincipal
	CROSS APPLY db_ref.dbo.findNewPerson(GTI_NomLong,GTI_Prenom) fct
	WHERE GTI_NumStatutPers = '0'

18/01/2013, 11h35
localhost21

Testé sur un échantillon de 1000 lignes.

La PS actuelle (avec curseur) prend 1.57 minutes
Votre requête : 4.2 minutes

:/

et pour info, que donne ceci :

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
 
 
INSERT INTO dbo.LAB_WCC_WorldCheckControl (
		WCC_CodTiers,
		WCC_CodCompte,
		WCC_Nom,
		WCC_Uid,
		WCC_Prenom,
		WCC_WorldCheckString,
		WCC_Matching
	) 
	SELECT (
		GTI_CodTiers,
		GCO_CodCompte,
		GTI_NomLong,
		WCS_Uid,
		GTI_Prenom,
		WCS_StringAliases,
		'PP : Correspondance avec le nom de naissance et prénom'
	)
	FROM	db_ldw_full.dbo.CLI_GTI_GeneriquesTiers 
	LEFT JOIN	db_ldw_full..CLI_GCO_GeneriquesComptes
	   ON			GTI_CodTiers = GCO_CodTiersPrincipal
	INNER JOIN	db_ref..LAB_WCS_WorldCheckStrings
		ON	LEN(GTI_NomLong) > 0  
            AND ( WCS_StringAliases LIKE '% ' + GTI_NomLong + ' %' + GTI_Prenom
                  + '[^a-zA-Z0-9]%'
                  OR WCS_StringAliases LIKE GTI_NomLong + ' %' + GTI_Prenom
                  + '[^a-zA-Z0-9]%'
                )
	WHERE GTI_NumStatutPers = '0'

Pouvez vous poster votre méthode pour générer votre échantillon de 1000 lignes ?

Pouvez-vous aussi poster les DDL avec les index de toutes vos tables de la requête ci-dessus ?

Ci-après, les DDL.
L'échantillon de 1000 lignes est un simple TOP 1000 appliqué à la fois sur la PS actuelle et votre script (juste pour pouvoir tester sans avoir à attendre 3h).

Merci pour votre temps !

LAB_WCS_WorldCheckStrings

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE [dbo].[LAB_WCS_WorldCheckStrings](
	[WCS_Uid] [int] NOT NULL,
	[WCS_NomComplet] [varchar](100) NOT NULL,
	[WCS_StringAliases] [varchar](4000) NULL,
	[WCS_DateEntree] [datetime] NULL,
	[WCS_DateMAJ] [datetime] NULL,
	[WCS_ActualDateMaj] [datetime] NULL,
 CONSTRAINT [PK_LAB_WCS_WorldCheckStrings] PRIMARY KEY CLUSTERED 
(
	[WCS_Uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING ON
GO

LAB_WCC_WorldCheckControl

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE TABLE [dbo].[LAB_WCC_WorldCheckControl](
	[WCC_CodTiers] [varchar](100) NULL,
	[WCC_CodCompte] [varchar](23) NULL,
	[WCC_Nom] [varchar](300) NULL,
	[WCC_Uid] [int] NULL,
	[WCC_Prenom] [varchar](300) NULL,
	[WCC_WorldCheckString] [varchar](4000) NULL,
	[WCC_Matching] [varchar](300) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING ON
GO

CLI_GTI_GeneriquesTiers

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
 
CREATE TABLE [dbo].[CLI_GTI_GeneriquesTiers](
	[GTI_CodTiers] [varchar](15) NOT NULL,
	[GTI_CodAdresse] [varchar](15) NULL,
	[GTI_CodSociete] [varchar](3) NOT NULL,
	[GTI_DatTSO] [datetime] NOT NULL,
	[GTI_HashCodeTSO] [bigint] NULL,
	[GTI_CodEtablissement] [varchar](3) NOT NULL,
	[GTI_CodAssiste] [varchar](4) NOT NULL,
	[GTI_NomAbrege] [varchar](12) NULL,
	[GTI_NomLong] [varchar](35) NULL,
	[GTI_NomLongSuite] [varchar](35) NULL,
	[GTI_NomMarital] [varchar](32) NULL,
	[GTI_Prenom] [varchar](32) NULL,
	[GTI_Prenom2] [varchar](32) NULL,
	[GTI_Prenom3] [varchar](32) NULL,
	[GTI_PrenomMarital] [varchar](32) NULL,
	[GTI_Tx] [decimal](19, 6) NULL,
	[GTI_CodAffilieSico] [varchar](10) NULL,
	[GTI_CodAnalytique] [varchar](3) NULL,
	[GTI_CodBICContrepartieEuropeenne] [varchar](11) NULL,
	[GTI_CodCivilite] [varchar](2) NULL,
	[GTI_CodClasseMifid] [varchar](3) NULL,
	[GTI_CodCompetenceBoursiere] [varchar](10) NULL,
	[GTI_CodExpertiseProduit] [varchar](10) NULL,
	[GTI_CodFacturation] [varchar](10) NULL,
	[GTI_CodIBEIContrepartieEuropeenne] [varchar](10) NULL,
	[GTI_CodLBPBureauGP] [varchar](6) NULL,
	[GTI_CodLBPBureauRV] [varchar](6) NULL,
	[GTI_CodLBPPortefeuille] [varchar](5) NULL,
	[GTI_CodMnemonique] [varchar](20) NULL,
	[GTI_CodOrientationPatrimoineActuel] [varchar](10) NULL,
	[GTI_CodOrientationPatrimoineSouhaite] [varchar](10) NULL,
	[GTI_CodOrientationTransactionActuelle] [varchar](10) NULL,
	[GTI_CodOrientationTransactionSouhaite] [varchar](10) NULL,
	[GTI_CodPaysNaissance] [varchar](3) NULL,
	[GTI_CodProfilExecution] [varchar](15) NULL,
	[GTI_CodSecuSiret] [varchar](15) NULL,
	[GTI_CodSiren] [varchar](9) NULL,
	[GTI_CodService] [varchar](5) NULL,
	[GTI_CodTarif] [varchar](10) NULL,
	[GTI_CodTypeTiers] [varchar](2) NULL,
	[GTI_CodRcs] [varchar](50) NULL,
	[GTI_CodApe] [varchar](5) NULL,
	[GTI_CodTvaIntra] [varchar](50) NULL,
	[GTI_NumCapacite] [varchar](2) NULL,
	[GTI_NumFormeJuridique] [varchar](2) NULL,
	[GTI_NumNationalite] [varchar](3) NULL,
	[GTI_NumPaysResident] [varchar](3) NULL,
	[GTI_NumRegimeMatrimonial] [varchar](2) NULL,
	[GTI_NumResidentFiscalite] [varchar](2) NULL,
	[GTI_NumSituationFamiliale] [varchar](2) NULL,
	[GTI_NumSoumissionTva] [varchar](2) NULL,
	[GTI_DatCreation] [datetime] NULL,
	[GTI_DatNaissanceOuCreation] [datetime] NULL,
	[GTI_DatDerniereMaj] [datetime] NULL,
	[GTI_CodApparente] [varchar](2) NULL,
	[GTI_CodAgentEco] [varchar](2) NULL,
	[GTI_AdrCommuneNaissance] [varchar](35) NULL,
	[GTI_CodCommuneNaissance] [varchar](3) NULL,
	[GTI_CodCSP] [varchar](3) NULL,
	[GTI_CodSexe] [varchar](1) NULL,
	[GTI_NumProfession] [varchar](3) NULL,
	[GTI_NumStatutPers] [int] NULL,
	[GTI_AdrDepartementNaissance] [varchar](2) NULL,
	[GTI_DatFermeture] [datetime] NULL,
	[GTI_AdresseSwift] [varchar](11) NULL,
	[GTI_CodDocumentIdentite] [varchar](2) NULL,
	[GTI_CodEligibilite] [varchar](2) NULL,
	[GTI_IsReinvestOPCVM] [bit] NULL,
	[GTI_IsReinvestBrut] [bit] NULL,
	[GTI_IsRegltEur] [bit] NULL,
	[GTI_CodCalculPlusOuMoinsValues] [varchar](2) NULL,
	[GTI_IsDeclaCptTiers] [bit] NULL,
	[GTI_IsPrelevLibObligations] [bit] NULL,
	[GTI_IsPrelevLibRevenusCreances] [bit] NULL,
	[GTI_IsPrelevLibProduitsTresorerie] [bit] NULL,
	[GTI_IsPrelevLibActions] [bit] NULL,
	[GTI_MntSeuilRevenusAction] [decimal](19, 6) NULL,
	[GTI_MntSeuilRevenusObligation] [decimal](19, 6) NULL,
	[GTI_MntSeuilRevenusTresorerie] [decimal](19, 6) NULL,
	[GTI_MntSeuilRevenusTCN] [decimal](19, 6) NULL,
	[GTI_CodNonResidentConnu] [varchar](2) NULL,
	[GTI_DatAttestation] [datetime] NULL,
	[GTI_CodDomFisRevenus] [varchar](15) NULL,
	[GTI_CodDomFisPlusValues] [varchar](15) NULL,
	[GTI_CodEditionIFU] [varchar](15) NULL,
	[GTI_CodApporteur] [varchar](15) NULL,
	[GTI_DatDeces] [datetime] NULL,
	[GTI_CodDepartement] [varchar](2) NULL,
	[GTI_NumPapierIdentite] [varchar](15) NULL,
	[GTI_NumPieceIdentite] [varchar](12) NULL,
	[GTI_NomAutoriteCivile] [varchar](32) NULL,
	[GTI_DatRemiseDocumentIdentite] [datetime] NULL,
	[GTI_CodLangueEdition] [varchar](2) NULL,
	[GTI_IsInterditChequier] [bit] NULL,
	[GTI_NumIdentificationFiscale] [varchar](16) NULL,
	[GTI_CodClasseRisque] [varchar](10) NULL,
	[GTI_CodFiscalite] [varchar](2) NULL,
	[GTI_DatImmatriculation] [datetime] NULL,
	[GTI_CodClasseClient] [varchar](5) NULL,
 CONSTRAINT [PK_CLI_GTI_GeneriquesTiers] PRIMARY KEY CLUSTERED 
(
	[GTI_CodTiers] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING ON
GO

En gros, le besoin est de parcourir toutes les lignes de CLI_GTI_GeneriquesTiers et faire un matching avec LAB_WCS_WorldCheckStrings selon la logique :
Code:

1 2 3 4 5 WCS_StringAliases LIKE '% ' + GTI_NomLong + ' %' + GTI_Prenom + '[^a-zA-Z0-9]%' OR WCS_StringAliases LIKE GTI_NomLong + ' %' + GTI_Prenom + '[^a-zA-Z0-9]%'
Si un matching est positif, on insère dans une autre table (LAB_WCC_WorldCheckControl) , sinon pas d'insertion et on passe à la ligne suivante.

21/01/2013, 14h05
aieeeuuuuu

Avez-vous testé la dernière requête que je vous ai envoyée ?

Au vu de vos DDL, remplacez GTI_NumStatutPers = '0' par GTI_NumStatutPers = 0
N'avez-vous aucun clef étrangère de déclarée sur vos tables ? Aucun index non plus ? Ou avez-vous omis de les mettre dans vos DDL ?
22/01/2013, 10h27
localhost21

Je l'ai testé sur un TOP 5000, elle met plus qu'une heure.

Sinon, j'ai beaucoup de clef etrangère sur la table CLI_GTI_GeneriquesTiers mais pas sur les autres.