Amélioration du temps de réponse d'une requête

**Boubou2020** · 21/11/2014, 16h01

Bonjour a tous

J'ai une requête du reporting qui présente un temps d’exécution qui dépasse le 3 minutes ci joint leur syntaxe

Code :

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

select Max(inspection_end_time) from location

en affichant le plan d’exécution du requête j'ai trouver que l'opération le plus coûteuse c'est pendant le scan d'in index cluster qui point sur le colonne "Location_ID"

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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
USE [Ligne12]
GO
/****** Object:  Table [dbo].[Location]    Script Date: 11/21/2014 15:44:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Location](
	[Location_ID] [int] IDENTITY(1,1) NOT NULL,
	[Assembly_ID] [int] NOT NULL,
	[Panel_ID] [int] NOT NULL,
	[Board_ID] [int] NOT NULL,
	[Result_ID] [int] NOT NULL,
	[Inspection_end_time] [datetime] NOT NULL,
	[Total_Features] [int] NOT NULL,
	[Identifier1] [varchar](50) NOT NULL,
	[Identifier2] [varchar](50) NULL,
	[Identifier3] [varchar](50) NULL,
	[Inspection_info1] [varchar](200) NULL,
	[Inspection_info2] [varchar](200) NULL,
	[Inspection_info3] [varchar](200) NULL,
	[Image1] [nvarchar](250) NULL,
	[Image2] [nvarchar](250) NULL,
	[Attribute1] [int] NULL,
	[Attribute2] [int] NULL,
	[Min_Attribute2] [float] NULL DEFAULT ((-999999)),
	[Max_Attribute2] [float] NULL DEFAULT ((-999999)),
	[Tgt_Attribute2] [float] NULL DEFAULT ((-999999)),
	[Attribute3] [int] NULL,
	[Min_Attribute3] [float] NULL DEFAULT ((-999999)),
	[Max_Attribute3] [float] NULL DEFAULT ((-999999)),
	[Tgt_Attribute3] [float] NULL DEFAULT ((-999999)),
	[Attribute4] [int] NULL,
	[Min_Attribute4] [float] NULL DEFAULT ((-999999)),
	[Max_Attribute4] [float] NULL DEFAULT ((-999999)),
	[Tgt_Attribute4] [float] NULL DEFAULT ((-999999)),
	[Measure1] [float] NULL,
	[Min_Measure1] [float] NULL DEFAULT ((-999999)),
	[Max_Measure1] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure1] [float] NULL DEFAULT ((-999999)),
	[Measure2] [float] NULL,
	[Min_Measure2] [float] NULL DEFAULT ((-999999)),
	[Max_Measure2] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure2] [float] NULL DEFAULT ((-999999)),
	[Measure3] [float] NULL,
	[Min_Measure3] [float] NULL DEFAULT ((-999999)),
	[Max_Measure3] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure3] [float] NULL DEFAULT ((-999999)),
	[Measure4] [float] NULL,
	[Min_Measure4] [float] NULL DEFAULT ((-999999)),
	[Max_Measure4] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure4] [float] NULL DEFAULT ((-999999)),
	[Measure5] [float] NULL,
	[Min_Measure5] [float] NULL DEFAULT ((-999999)),
	[Max_Measure5] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure5] [float] NULL DEFAULT ((-999999)),
	[Measure6] [float] NULL,
	[Min_Measure6] [float] NULL DEFAULT ((-999999)),
	[Max_Measure6] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure6] [float] NULL DEFAULT ((-999999)),
	[Measure7] [float] NULL,
	[Min_Measure7] [float] NULL DEFAULT ((-999999)),
	[Max_Measure7] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure7] [float] NULL DEFAULT ((-999999)),
	[Measure8] [float] NULL,
	[Min_Measure8] [float] NULL DEFAULT ((-999999)),
	[Max_Measure8] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure8] [float] NULL DEFAULT ((-999999)),
	[Measure9] [float] NULL,
	[Min_Measure9] [float] NULL DEFAULT ((-999999)),
	[Max_Measure9] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure9] [float] NULL DEFAULT ((-999999)),
	[Measure10] [float] NULL,
	[Min_Measure10] [float] NULL DEFAULT ((-999999)),
	[Max_Measure10] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure10] [float] NULL DEFAULT ((-999999)),
	[Measure11] [float] NULL,
	[Min_Measure11] [float] NULL DEFAULT ((-999999)),
	[Max_Measure11] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure11] [float] NULL DEFAULT ((-999999)),
	[Measure12] [float] NULL,
	[Min_Measure12] [float] NULL DEFAULT ((-999999)),
	[Max_Measure12] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure12] [float] NULL DEFAULT ((-999999)),
	[Measure13] [float] NULL,
	[Min_Measure13] [float] NULL DEFAULT ((-999999)),
	[Max_Measure13] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure13] [float] NULL DEFAULT ((-999999)),
	[Measure14] [float] NULL,
	[Min_Measure14] [float] NULL DEFAULT ((-999999)),
	[Max_Measure14] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure14] [float] NULL DEFAULT ((-999999)),
	[Measure15] [float] NULL,
	[Min_Measure15] [float] NULL DEFAULT ((-999999)),
	[Max_Measure15] [float] NULL DEFAULT ((-999999)),
	[Tgt_Measure15] [float] NULL DEFAULT ((-999999)),
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
	[Location_ID] 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 OFF
GO
ALTER TABLE [dbo].[Location]  WITH CHECK ADD  CONSTRAINT [FK_Location_Board] FOREIGN KEY([Board_ID])
REFERENCES [dbo].[Board] ([Board_ID])
GO
ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [FK_Location_Board]
GO
ALTER TABLE [dbo].[Location]  WITH CHECK ADD  CONSTRAINT [FK_Location_Panel] FOREIGN KEY([Panel_ID])
REFERENCES [dbo].[Panel] ([Panel_ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [FK_Location_Panel]
GO
ALTER TABLE [dbo].[Location]  WITH CHECK ADD  CONSTRAINT [FK_Location_Result] FOREIGN KEY([Result_ID])
REFERENCES [dbo].[Result] ([Result_ID])
GO
ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [FK_Location_Result]
GO
ALTER TABLE [dbo].[Location]  WITH CHECK ADD  CONSTRAINT [FK_Location_Run] FOREIGN KEY([Assembly_ID])
REFERENCES [dbo].[Run] ([Assembly_ID])
GO
ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [FK_Location_Run]

ma Question Ma requête pointe sur la colonne "inspection_end_time" alors que mon index pointe sur la colonne "Location_ID" de plus il s'agit d'un index cluster donc je ne peux pas faire ajouter l'option include de plus il s'agit d'une opération scan y a t'il un moyen de le rendre opération seek

merci pour nos expert

**SQLpro** · 21/11/2014, 16h28

Avec une table aussi merdique, c'est parfaitement normal. Plus vous aurez de lignes, plus ça va devenir infâme...

Connaissez vous les principes de bases de la modélisation des données relationnelles ? Visiblement non !

Commencer par revoir entièrement votre MCD et vous verrez que votre temps de réponse va devenir infime (au plus quelques millisecondes !)...

Personnellement en audit, je flingue dès qu'une table dépasse 20 colonnes !

Et là vous avez dans cette table plus de 10 viols par extension de la première forme normale :
1)
Attribute1
Attribute2
Attribute3
Attribute4
=> devrait être dans une table à part en lien 1:n avec les colonnes : ATTRIBUTE, NUM_ATTRIBUTE

2)
Identifier1
Identifier2
Identifier3
=> même topo

3)
Image1
Image2

4)
Inspection_info1
Inspection_info2
Inspection_info3

5)
Max_Attribute2
Max_Attribute3
Max_Attribute4

6)
Max_Measure1
Max_Measure10
Max_Measure11
Max_Measure12
Max_Measure13
Max_Measure14
Max_Measure15
Max_Measure2
Max_Measure3
Max_Measure4
Max_Measure5
Max_Measure6
Max_Measure7
Max_Measure8
Max_Measure9

7)
Measure1
Measure10
Measure11
Measure12
Measure13
Measure14
Measure15
Measure2
Measure3
Measure4
Measure5
Measure6
Measure7
Measure8
Measure9

8)
Min_Attribute2
Min_Attribute3
Min_Attribute4

9)
Min_Measure1
Min_Measure10
Min_Measure11
Min_Measure12
Min_Measure13
Min_Measure14
Min_Measure15
Min_Measure2
Min_Measure3
Min_Measure4
Min_Measure5
Min_Measure6
Min_Measure7
Min_Measure8
Min_Measure9

10)
Tgt_Attribute2
Tgt_Attribute3
Tgt_Attribute4

11)
Tgt_Measure1
Tgt_Measure10
Tgt_Measure11
Tgt_Measure12
Tgt_Measure13
Tgt_Measure14
Tgt_Measure15
Tgt_Measure2
Tgt_Measure3
Tgt_Measure4
Tgt_Measure5
Tgt_Measure6
Tgt_Measure7
Tgt_Measure8
Tgt_Measure9

Au final vous devriez avoir 12 tables :
Location avec :

Assembly_ID
Board_ID
Inspection_end_time
Location_ID
Panel_ID
Result_ID
Total_Features

et 11 tables de la forme :

Location_ID
Colonne
Num_colonne

A +

**bvesan** · 24/11/2014, 12h07

Bonjour,
La création d'un indexe sur inspection_end_time devrait réduire considérablement le temps de réponse de votre requête.
Attention toutefois à l'impact sur la modification des données (elle sera plus longue, puisqu'un indexe supplémentaire sera à mettre à jour).

Cordialement.

Benjamin

Amélioration du temps de réponse d'une requête

Administration SQL Server

Discussions similaires

Partager

Partager