[SQL Server 2K8] Plan d'exécution

Version imprimable

05/01/2011, 16h05
tibobao

[SQL Server 2K8] Plan d'exécution

Bonjour à tous,

Voici le problème auquel je suis exposé en SQLServer 2008
Volumétrie des données récupérées : environ 19 000 lignes.

Lorsque j'exécute une procédure stockée à l'aide des paramètres, elle prends plus d'une minute à s'exécuter. Par contre, si j'exécute le corps de la procédure avec les même paramètres, elle met 6 secondes.

Avez-vous une idée d'où le problème peut provenir ?

Merci d'avance.
05/01/2011, 16h10
mikedavem

Bonjour,

Postez le code de votre procédure ...

++
05/01/2011, 16h19
aieeeuuuuu

Bonjour,

Avez-vous créé des index depuis la première exécution de la procédure stockée en question ?

Si c'est la cas, il est possible que celle ci ne les prenne pas en compte car son plan d'exécution est en cache depuis la première exécution.

Refaites vos tests en exécutant votre procédure avec l'option WITH RECOMPILE et dites nous ce qu'il en est...

(ou exécutez

Code:

sp_recompile MaProcedure

avant de lancer votre procédure)

Voici le code lancé avec un temps d'execution d'une minute:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
EXEC   [dbo].[sp_GetListActeRechAli_S]
             @CODE_MARQUE = N'AC',
             @CODE_TYPE_ACTE = N'VN',
             @CODE_PAYS = N'FR',
             @DATE_FACTURATION_LIVRAISON = N'01/01/2010',
             @CODE_RRDI = NULL,
             @VIN = NULL,
             @SITECODE = NULL,
             @CodeCustomerManager = NULL,
            @AGENTCODE = NULL,
             @ADRESSE_MAIL = NULL,
             @NOM_CLIENT = NULL

Voici la procédure

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
/****** Object:  StoredProcedure [dbo].[sp_GetListActeRechAli_S]    Script Date: 01/05/2011 14:08:06 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**************************************************************************************************     

#DCART     

NOM                     : sp_GetListActeRechAli_S     

DESCRIPTION             : Recherche une liste d'acte selon certains criteres      

PARAMETRES EN ENTREE    : code marque, code pays, dispositif, date, code vin, mail, nom client, code site   
       code agent, code rrdi, code CC    

PARAMETRES EN SORTIE    :      

HISTORIQUE    :     
        DATE            AUTEUR                  OBJET     
             12/10/2010          TCo                Création   
             10/12/2010          TCo               Modif (MANTIS 81)

#FCART     

**************************************************************************************************/   

CREATE PROCEDURE [dbo].[sp_GetListActeRechAli_S]   

(   
       @CODE_MARQUE nchar(2),   
       @CODE_TYPE_ACTE nvarchar(3),   
       @CODE_PAYS nchar(2),   
       @DATE_FACTURATION_LIVRAISON datetime,   
       @CODE_RRDI nvarchar(9) = NULL,   
       @VIN nvarchar(32) = NULL,   
       @SITECODE nvarchar(254) = NULL,   
       @CodeCustomerManager nvarchar(20) = NULL,   
       @AGENTCODE nvarchar(254) = NULL,   
       @ADRESSE_MAIL nvarchar(255) = NULL,   
       @NOM_CLIENT nvarchar(255) = NULL   
)   

AS   

BEGIN

DECLARE   

@DATEDEBUT datetime,   
@DATEFIN datetime   

SET @DATEDEBUT = dbo.GetFirstDayOfMonthCustom(@DATE_FACTURATION_LIVRAISON)   

SET @DATEFIN = dbo.GetLastDayOfMonthCustom(@DATE_FACTURATION_LIVRAISON,1)   

SELECT      
 A.IDACTE,  
 A.CODE_TYPE_ACTE,  
 A.CODE_MARQUE,  
 A.CODE_ISO_PAYS,  
 A.CODE_ETAT,      
 A.DATE_DEBUT_TERRAIN, 
 A.VIN, 
 CO.CODE_RRDI, 
 A.ID_AUTO, 
 A.DATE_FIN_INTERVIEW, 
 A.ADRESSE_MAIL,      
 A.LASTNAME,  
 CO.IDCONCESSIONNAIRE,  
 CO.CODE_IPSOS,  
 CO.NOM_COMMERCIAL,  
 M.LIBELLE AS LIB_MARQUE,  
 P.LIBELLE AS LIB_PAYS  ,
 H.ID_MODELE_FICHE
FROM      
 ACTE AS A  
 LEFT JOIN CONCESSIONNAIRE AS CO ON A.IDCONCESSIONNAIRE = CO.IDCONCESSIONNAIRE      
 INNER JOIN MARQUE M ON A.CODE_MARQUE = M.CODE_MARQUE  
 INNER JOIN PAYS P ON A.CODE_ISO_PAYS = P.CODE_ISO_PAYS  
 LEFT JOIN HISTORIQUE_REPONSE H ON
       H.IDACTE = A.IDACTE AND
       H.CODE_PAYS = A.CODE_ISO_PAYS AND
       H.CODE_MARQUE = A.CODE_MARQUE AND
       H.CODE_TYPE_ACTE = A.CODE_TYPE_ACTE AND
       H.CATI = 0
WHERE  
 A.CODE_MARQUE = @CODE_MARQUE AND  
 A.CODE_TYPE_ACTE = @CODE_TYPE_ACTE AND  
 A.CODE_ISO_PAYS = @CODE_PAYS AND  
 A.DATE_FACTURATION_LIVRAISON BETWEEN @DATEDEBUT AND @DATEFIN AND  
 (@CODE_RRDI IS NULL OR UPPER(CO.CODE_RRDI)=UPPER(@CODE_RRDI)) AND  
 (@CodeCustomerManager IS NULL OR A.CODECUSTOMERMANAGER = @CodeCustomerManager) AND  
 (@VIN IS NULL OR A.VIN = @VIN) AND  
 (@SITECODE IS NULL OR UPPER(A.SITECODE) = UPPER(@SITECODE)) AND  
 (@AGENTCODE IS NULL OR UPPER(A.AGENTCODE) = UPPER(@AGENTCODE)) AND  
 (@ADRESSE_MAIL IS NULL OR UPPER(A.ADRESSE_MAIL) = UPPER(@ADRESSE_MAIL)) AND  
 (@NOM_CLIENT IS NULL OR UPPER(A.LASTNAME) = UPPER(@NOM_CLIENT)) AND  
 (A.FLAG_ANNULE = 0 OR A.FLAG_ANNULE IS NULL)

END

Et la c'est le code qui prend 6 secondes.

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
declare @CODE_MARQUE nchar(2)  
declare @CODE_TYPE_ACTE nvarchar(3)   
declare @CODE_PAYS nchar(2) 
declare @DATE_FACTURATION_LIVRAISON datetime   
declare @CODE_RRDI nvarchar(9) 
declare @VIN nvarchar(32)
declare @SITECODE nvarchar(254)
declare @CodeCustomerManager nvarchar(20)
declare @AGENTCODE nvarchar(254)
declare @ADRESSE_MAIL nvarchar(255)
declare @NOM_CLIENT nvarchar(255) 
             
set @CODE_MARQUE = N'AC'
set @CODE_TYPE_ACTE = N'VN'
set @CODE_PAYS = N'FR'
set @DATE_FACTURATION_LIVRAISON = N'01/01/2010'
set @CODE_RRDI = NULL
set @VIN = NULL
set @SITECODE = NULL
set @CodeCustomerManager = NULL
set @AGENTCODE = NULL
set @ADRESSE_MAIL = NULL
set @NOM_CLIENT = NULL
             
declare @DATEDEBUT datetime  
declare  @DATEFIN datetime    
    
SET @DATEDEBUT = dbo.GetFirstDayOfMonthCustom(@DATE_FACTURATION_LIVRAISON)    
SET @DATEFIN = dbo.GetLastDayOfMonthCustom(@DATE_FACTURATION_LIVRAISON,1)    
    
SELECT       
 A.IDACTE,   
 A.CODE_TYPE_ACTE,   
 A.CODE_MARQUE,   
 A.CODE_ISO_PAYS,   
 A.CODE_ETAT,       
 A.DATE_DEBUT_TERRAIN,  
 A.VIN,  
 CO.CODE_RRDI,  
 A.ID_AUTO,  
 A.DATE_FIN_INTERVIEW,  
 A.ADRESSE_MAIL,       
 A.LASTNAME,   
 CO.IDCONCESSIONNAIRE,   
 CO.CODE_IPSOS,   
 CO.NOM_COMMERCIAL,   
 M.LIBELLE AS LIB_MARQUE,   
 P.LIBELLE AS LIB_PAYS  ,
 H.ID_MODELE_FICHE
FROM       
 ACTE AS A   
 LEFT JOIN CONCESSIONNAIRE AS CO ON A.IDCONCESSIONNAIRE = CO.IDCONCESSIONNAIRE       
 INNER JOIN MARQUE M ON A.CODE_MARQUE = M.CODE_MARQUE   
 INNER JOIN PAYS P ON A.CODE_ISO_PAYS = P.CODE_ISO_PAYS   
 LEFT JOIN HISTORIQUE_REPONSE H ON H.IDACTE = A.IDACTE   
WHERE   
 A.CODE_MARQUE = @CODE_MARQUE AND   
 A.CODE_TYPE_ACTE = @CODE_TYPE_ACTE AND   
 A.CODE_ISO_PAYS = @CODE_PAYS AND   
 A.DATE_FACTURATION_LIVRAISON BETWEEN @DATEDEBUT AND @DATEFIN AND   
 (@CODE_RRDI IS NULL OR UPPER(CO.CODE_RRDI)=UPPER(@CODE_RRDI)) AND   
 (@CodeCustomerManager IS NULL OR A.CODECUSTOMERMANAGER = @CodeCustomerManager) AND   
 (@VIN IS NULL OR A.VIN = @VIN) AND   
 (@SITECODE IS NULL OR UPPER(A.SITECODE) = UPPER(@SITECODE)) AND   
 (@AGENTCODE IS NULL OR UPPER(A.AGENTCODE) = UPPER(@AGENTCODE)) AND   
 (@ADRESSE_MAIL IS NULL OR UPPER(A.ADRESSE_MAIL) = UPPER(@ADRESSE_MAIL)) AND   
 (@NOM_CLIENT IS NULL OR UPPER(A.LASTNAME) = UPPER(@NOM_CLIENT)) AND   
 (A.FLAG_ANNULE = 0 OR A.FLAG_ANNULE IS NULL)

05/01/2011, 16h21
tibobao

J'ai essayé avec le "with recompile" mais sans succès...

Re,

Votre dernière jointure diffère entre les deux versions !

Code:

1
2
3
4
5
6
7
 
LEFT JOIN HISTORIQUE_REPONSE H ON
       H.IDACTE = A.IDACTE AND
       H.CODE_PAYS = A.CODE_ISO_PAYS AND
       H.CODE_MARQUE = A.CODE_MARQUE AND
       H.CODE_TYPE_ACTE = A.CODE_TYPE_ACTE AND
       H.CATI = 0

Code:

1
2
 
LEFT JOIN HISTORIQUE_REPONSE H ON H.IDACTE = A.IDACTE

En effet, un oubli de ma part lors de mes tests mais le problème ne vient pas de là.
Je viens de tester et le résultat est toujours le même.

je repost le corps actualisé de la proc :

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

declare @CODE_MARQUE nchar(2) 
declare @CODE_TYPE_ACTE nvarchar(3)  
declare @CODE_PAYS nchar(2)
declare @DATE_FACTURATION_LIVRAISON datetime  
declare @CODE_RRDI nvarchar(9) = NULL
declare @VIN nvarchar(32) = NULL
declare @SITECODE nvarchar(254) = NULL
declare @CodeCustomerManager nvarchar(20)= NULL
declare @AGENTCODE nvarchar(254)= NULL
declare @ADRESSE_MAIL nvarchar(255)= NULL
declare @NOM_CLIENT nvarchar(255)= NULL

set @CODE_MARQUE = 'AC'
set @CODE_TYPE_ACTE = 'VN'
set @CODE_PAYS = 'FR'
set @DATE_FACTURATION_LIVRAISON = '01/01/2010'
set @CODE_RRDI = NULL
set @VIN = NULL
set @SITECODE = NULL
set @CodeCustomerManager = NULL
set @AGENTCODE = NULL
set @ADRESSE_MAIL = NULL
set @NOM_CLIENT = NULL
            
declare @DATEDEBUT datetime 
declare  @DATEFIN datetime   

SET @DATEDEBUT = dbo.GetFirstDayOfMonthCustom(@DATE_FACTURATION_LIVRAISON)   
SET @DATEFIN = dbo.GetLastDayOfMonthCustom(@DATE_FACTURATION_LIVRAISON,1)   


SELECT      
 A.IDACTE,  
 A.CODE_TYPE_ACTE,  
 A.CODE_MARQUE,  
 A.CODE_ISO_PAYS,  
 A.CODE_ETAT,      
 A.DATE_DEBUT_TERRAIN, 
 A.VIN, 
 CO.CODE_RRDI, 
 A.ID_AUTO, 
 A.DATE_FIN_INTERVIEW, 
 A.ADRESSE_MAIL,      
 A.LASTNAME,  
 CO.IDCONCESSIONNAIRE,  
 CO.CODE_IPSOS,  
 CO.NOM_COMMERCIAL,  
 M.LIBELLE AS LIB_MARQUE,  
 P.LIBELLE AS LIB_PAYS  ,
 H.ID_MODELE_FICHE
FROM      
 ACTE AS A  
 LEFT JOIN CONCESSIONNAIRE AS CO ON A.IDCONCESSIONNAIRE = CO.IDCONCESSIONNAIRE      
 INNER JOIN MARQUE M ON A.CODE_MARQUE = M.CODE_MARQUE  
 INNER JOIN PAYS P ON A.CODE_ISO_PAYS = P.CODE_ISO_PAYS  
 LEFT JOIN HISTORIQUE_REPONSE H ON H.IDACTE = A.IDACTE  
 AND  
 H.CODE_PAYS = A.CODE_ISO_PAYS AND  
 H.CODE_MARQUE = A.CODE_MARQUE AND  
 H.CODE_TYPE_ACTE = A.CODE_TYPE_ACTE AND  
 H.CATI = 0 
WHERE  
 A.CODE_MARQUE = @CODE_MARQUE AND  
 A.CODE_TYPE_ACTE = @CODE_TYPE_ACTE AND  
 A.CODE_ISO_PAYS = @CODE_PAYS AND  
 A.DATE_FACTURATION_LIVRAISON BETWEEN @DATEDEBUT AND @DATEFIN AND  
 (@CODE_RRDI IS NULL OR UPPER(CO.CODE_RRDI)=UPPER(@CODE_RRDI)) AND  
 (@CodeCustomerManager IS NULL OR A.CODECUSTOMERMANAGER = @CodeCustomerManager) AND  
 (@VIN IS NULL OR A.VIN = @VIN) AND  
 (@SITECODE IS NULL OR UPPER(A.SITECODE) = UPPER(@SITECODE)) AND  
 (@AGENTCODE IS NULL OR UPPER(A.AGENTCODE) = UPPER(@AGENTCODE)) AND  
 (@ADRESSE_MAIL IS NULL OR UPPER(A.ADRESSE_MAIL) = UPPER(@ADRESSE_MAIL)) AND  
 (@NOM_CLIENT IS NULL OR UPPER(A.LASTNAME) = UPPER(@NOM_CLIENT)) AND  
 (A.FLAG_ANNULE = 0 OR A.FLAG_ANNULE IS NULL)

05/01/2011, 16h43
mikedavem

Pouvez vous poster le plan d'exécution réel lorsque vous lancez la procédure et lorsque vous lancez la requête SQL directement ?

++

1 pièce(s) jointe(s)

Voici les deux plans d'execution au format XML et sqlplan.

Chose que je viens de remarquer, le plan d'execution de la procédure me renvoie ce message alors que l'autre pour la requete non :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
/*
Détails de l'index absent de SQLQuery29.sql - PSASQL2\RECETTE.NetEQC (sa (59))
Le processeur de requêtes estime que l'implémentation de l'index suivant peut améliorer le coût de requête de 81.1675%.
*/
 
/*
USE [NetEQC]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ACTE] ([CODE_TYPE_ACTE],[CODE_MARQUE],[CODE_ISO_PAYS],[DATE_FACTURATION_LIVRAISON],[FLAG_ANNULE])
INCLUDE ([IDACTE],[CODE_ETAT],[IDCONCESSIONNAIRE],[DATE_DEBUT_TERRAIN],[CODECUSTOMERMANAGER],[LASTNAME],[ADRESSE_MAIL],[VIN],[DATE_FIN_INTERVIEW],[ID_AUTO])
GO
*/

06/01/2011, 09h06
tibobao

Personne n'a d'idée ?
06/01/2011, 09h41
tibobao

[RESOLU][SQL Server 2K8] Plan d'exécution

J'ai créé un index non clustered sur les champs préconisé par la proc et c'est tout bon.

Merci à vous.

Vu l'index qu'il demande de créer je trouve cela bizarre.
Vous devriez essayer de forcer vos variables de votre procédure en local pour voir si le plan d'exécution est meilleur.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROCEDURE [dbo].[sp_GetListActeRechAli_S]   
 
(   
       @CODE_MARQUE nchar(2),   
       @CODE_TYPE_ACTE nvarchar(3),   
       @CODE_PAYS nchar(2),   
       @DATE_FACTURATION_LIVRAISON datetime,   
       @CODE_RRDI nvarchar(9) = NULL,   
       @VIN nvarchar(32) = NULL,   
       @SITECODE nvarchar(254) = NULL,   
       @CodeCustomerManager nvarchar(20) = NULL,   
       @AGENTCODE nvarchar(254) = NULL,   
       @ADRESSE_MAIL nvarchar(255) = NULL,   
       @NOM_CLIENT nvarchar(255) = NULL   
)   
AS
 
DECLARE @CODE_MARQUE_P = @CODE_MARQUE
DECLARE @CODE_TYPE_ACTE_P = @CODE_TYPE_ACTE
 
....

06/01/2011, 15h53
elsuket

Bonjour,

J'appuie la réponse de Mikedavem ;)

Citation:

J'ai créé un index non clustered sur les champs préconisé par la proc et c'est tout bon.

D'accord, mais est-ce que celui-ci est utilisé par votre plan ?
Il arrive souvent que les index recommandés ne soient pas utilisés par l'optimiseur, et dans ce cas cela indiquerait un problème de statistiques de colonnes ...

@++ ;)