Discussion :

[localhost21]

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 :

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
 
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 !

[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 ?

[localhost21]

Bonjour,

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

Code :

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

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 :

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
 
-- #####################################################################################################################################################
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

[aieeeuuuuu]

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

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
 
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'

[localhost21]

Testé sur un échantillon de 1000 lignes.

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

:/

[aieeeuuuuu]

et pour info, que donne ceci :

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
 
 
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 ?