Procédure stocké qui prend plus de 20s pour s'exécuter

**joujousagem2006** · 28/02/2014, 15h19

Bonjour
j'ai une procédure stocké qui prend plus de 20 s pour s'executé , j'ai afficher son plan d’exécution j'ai obtenu un message indiquant la manque de statistics sur la table stat (voir imprime écran)

ce table est un table temporaire

Ma question comment je peux ajouter cette statique afin que je puisse obtenir le bonne plan d’exécution de cette procédure

ci joint le script de création de procédure stocké

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
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
USE [Suivi_Compteur]
GO
 
/****** Object:  StoredProcedure [dbo].[spSGM_Stat_Mvtfab_PostChar]    Script Date: 02/28/2014 15:14:06 ******/
SET ANSI_NULLS OFF
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
 
 
 
 
/* 
	Procedure	:	spSGM_Stat_Mvtfab_PostChar
	Createur	:	F.R. BROUSSAIS
	Date		:	23/09/2004
	Objet		: 	Compte le nombre de mouvements de fabrication entre 2 dates pour un poste de charge donné.
				Compte par mf_trans (test et ReTest différenciés) et par statut
				Possibilité de limiter les résultats à un produit et/ou à une transaction
 
	Parametres en entree: 	@pr_num	n° du poste de travail
				@dateDebut	date de début du test	(incluse)
				@dateFin	date de fin du test	(NON incluse)
				@mf_trans	Filtre sur la transaction associé au poste
				@pt_num	Filtre sur le code produit
				@Re_Distinct	Afficher les tests et les reTests. Additionne les mouvements Test + ReTest si @Re_Distinct <> 1
				@Statut_Distinct	Affiche les statuts des mouvements (GO, NOGO...). Additionne les mouvements GO + NOGO +... si @Statut_Distinct <> 1
 
	Resultat en retour: 	mf_trans, mf_statut, compte de mouvements : NbMvt
 
	Modification le 25/11/2004 par F.R. BROUSSAIS : Ajout des paramètres @Re_Distinct et @Statut_Distinct et modifications du code qui en découlent...
	Modification le 21/01/2005 par F.R. BROUSSAIS : Correction du code de la requête générée quand pr_num NOT NULL
*/
 
CREATE PROCEDURE [dbo].[spSGM_Stat_Mvtfab_PostChar]
 
	@pr_num	int = NULL,
	@dateDebut	datetime,
	@dateFin	datetime,
	@mf_trans	varchar(25) = NULL,
	@pt_num	varchar(18) = NULL,
	@Re_Distinct	tinyInt = 1,
	@Statut_Distinct tinyInt = 1
AS
 
DECLARE @codeSQL nvarchar(4000)
DECLARE @Param nvarchar(500)
 
CREATE TABLE #Stat (mf_trans varchar(25), mf_statut varchar(5), NbMvt integer, Re tinyint)
 
SET @Param=' @dDateDebut datetime, @dDateFin datetime '
 
SET @codeSQL= 'INSERT INTO #Stat (mf_trans, mf_statut, NbMvt, Re) '
 
IF @Re_Distinct = 1
BEGIN
	SET @codeSQL = @codeSQL + ' SELECT DISTINCT mf_trans, mf_statut, COUNT(*) as NbMvt, NULL '
			+ ' FROM mvtfabr WHERE mf_dmvtdeb >= @dDateDebut AND mf_dmvtDeb < @dDateFin '
	IF @pr_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pr_num  = '+ cast(@pr_num AS varchar(20))
	IF @pt_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pt_num = ''' + @pt_num + ''''
	IF @mf_trans IS NOT NULL SET @codeSQL = @codeSQL  +' AND (mf_trans = ''' + @mf_trans + ''' OR mf_trans = ''Re_' + @mf_trans + ''')'
	SET @codeSQL = @codeSQL + ' GROUP BY mf_trans, mf_statut ORDER BY mf_trans, mf_statut'
END
ELSE
BEGIN
	SET @codeSQL = @codeSQL + ' SELECT DISTINCT mf_trans,mf_statut,COUNT(*) as NbMvt, 0 '
			+ ' FROM mvtfabr WHERE mf_dmvtdeb>=@dDateDebut AND mf_dmvtDeb<@dDateFin '
	IF @pr_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pr_num=' + cast(@pr_num AS varchar(20))
	IF @pt_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pt_num=''' + @pt_num + ''''
	IF @mf_trans IS NOT NULL SET @codeSQL = @codeSQL  +' AND mf_trans=''' + @mf_trans + ''''
	SET @codeSQL = @codeSQL + ' AND LEFT(mf_trans, 3)<>''re_'''
			+ ' GROUP BY mf_trans,mf_statut ' -- ORDER BY mf_trans, mf_statut '
	SET @codeSQL = @codeSQL + ' UNION '
	+ ' SELECT DISTINCT RIGHT(mf_trans,LEN(mf_trans)-3),mf_statut,COUNT(*) as NbMvt, 1 '
			+ ' FROM mvtfabr WHERE mf_dmvtdeb>=@dDateDebut AND mf_dmvtDeb<@dDateFin '
	IF @pr_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pr_num=' + cast(@pr_num AS varchar(20))
	IF @pt_num IS NOT NULL SET @codeSQL = @codeSQL + ' AND pt_num=''' + @pt_num + ''''
	IF @mf_trans IS NOT NULL SET @codeSQL = @codeSQL  +' AND mf_trans=''Re_' + @mf_trans + ''''
	SET @codeSQL = @codeSQL + ' AND LEFT(mf_trans, 3)=''re_'''
		+ ' GROUP BY mf_trans,mf_statut ' 
END
 
PRINT @codeSQL
EXEC sp_executesql @codeSQL,@Param, @dDateDebut = @datedebut, @dDateFin = @Datefin
 
-- select * from #stat
 
IF @Statut_Distinct = 1
BEGIN
	SELECT distinct mf_trans, mf_statut, SUM(NbMvt) AS NbMvt FROM #Stat
		GROUP BY mf_trans, mf_statut
		ORDER BY mf_trans, mf_statut
END
ELSE
BEGIN
	SELECT distinct mf_trans, '' as mf_statut, SUM(NbMvt) AS NbMvt FROM #Stat
		GROUP BY mf_trans
		ORDER BY mf_trans
END
 
 
 
 
GO[ATTACH=CONFIG]139264[/ATTACH]

**SQLpro** · 03/03/2014, 18h42

donnez la définition de cette table. As t-elle une clef ?

A +

**joujousagem2006** · 04/03/2014, 09h02

Envoyé par SQLpro

donnez la définition de cette table. As t-elle une clef ?

A +

Ci joint le script utilisé pour la création de 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
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
USE [Suivi_Compteur]
GO
 
/****** Object:  Table [dbo].[mvtFabr]    Script Date: 03/04/2014 09:01:21 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[mvtFabr](
	[pt_num] [varchar](18) NOT NULL,
	[mf_dmvtdeb] [datetime] NOT NULL,
	[ni_num] [varchar](23) NOT NULL,
	[mf_trans] [varchar](25) NULL,
	[pr_num] [int] NULL,
	[pl_badge] [int] NULL,
	[mf_statut] [varchar](5) NULL,
	[mf_dmvtfin] [datetime] NULL,
	[mf_duree] [float] NULL,
	[mf_obs] [varchar](78) NULL,
	[mf_seq] [int] NULL,
	[of_num] [varchar](8) NULL,
	[ge_phase] [int] NULL,
	[mf_qte] [real] NULL,
	[qc_code] [varchar](10) NULL,
	[qd_id] [int] NULL,
PRIMARY KEY NONCLUSTERED 
(
	[pt_num] ASC,
	[mf_dmvtdeb] ASC,
	[ni_num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[mvtFabr]  WITH NOCHECK ADD  CONSTRAINT [FK_mvtFabr_numSerie] FOREIGN KEY([pt_num], [ni_num])
REFERENCES [dbo].[numSerie] ([pt_num], [ni_num])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
 
ALTER TABLE [dbo].[mvtFabr] CHECK CONSTRAINT [FK_mvtFabr_numSerie]
GO
 
ALTER TABLE [dbo].[mvtFabr]  WITH NOCHECK ADD  CONSTRAINT [FK_mvtFabr_transMF] FOREIGN KEY([mf_trans])
REFERENCES [dbo].[transMF] ([tf_code])
ON UPDATE CASCADE
GO
 
ALTER TABLE [dbo].[mvtFabr] CHECK CONSTRAINT [FK_mvtFabr_transMF]
GO

**aieeeuuuuu** · 04/03/2014, 10h38

Soit une clef primaire sur... 53 octets !!!

A terme, il faudra sans doute revoir votre modélisation.

Dans l'immédiat, voici quelques pistes :

1/ vos DISTINCT dans vos requête avec UNION sont inutiles : UNION dédoublonne naturellement. Enlevez les également dans les requêtes avec GROUP BY.
2/ remplacez AND LEFT(mf_trans, 3)='re_' par AND mf_trans LIKE 're_%'.
3/ revoyez l’ordre de vos colonnes dans la clef primaire, ou, pour éviter des effets de bords, posez l'index suivant pour supporter vos requêtes :

Code :

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

1
2
3
 
CREATE INDEX IX_mvtFabr 
    ON mvtFabr (mf_dmvtdeb, mf_trans, mf_statut,  pt_num, pr_num)

A adapter sans doute...

4/ Je pense que vous pourriez éviter votre double requête avec UNION en gérant dans le SELECT et dans le GROUP BY les cas où mf_trans commence par 're_' :

Code :

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

1
2
3
SELECT CASE WHEN mf_trans LIKE 'Re_%' THEN RIGHT(mf_trans,LEN(mf_trans)-3) ELSE mf_trans END AS mf_trans, ...
...
GROUP BY CASE WHEN mf_trans LIKE 'Re_%' THEN RIGHT(mf_trans,LEN(mf_trans)-3) ELSE mf_trans END...

ça devrait déjà aller beaucoup mieux

Procédure stocké qui prend plus de 20s pour s'exécuter

Administration SQL Server

Discussions similaires

Partager

Partager