Optimisation d'opération key lookup

Version imprimable

2 pièce(s) jointe(s)

bonjour a tout et a tous et merci à ceux qui nous aident et font vivre ce forum

j'ai eu dernièrement des problèmes du performances qui s’apparaît aléatoirement sur mon instance, en analysant en proche la situation je viens de

découvrir une jolie requête qui entraîne un blocage ci joint leur syntaxe

Code:

1
2
3
4
5
SELECT count(No_UM) FROM SERIALISATION, LIVRAISON_SERIALISATION  
					   WHERE SERIALISATION.No_Serie = LIVRAISON_SERIALISATION.No_Serie  
						AND No_BL  = '92514'  and no_materiel='25337'
						and No_UM=(select distinct no_um from serialisation where no_serie='31541255114')
						GROUP BY No_UM

en affichant le plan d’exécution du requête je vien d'avoir une opération key lookp très coûteuse (voir imprime écran) ,
le colonne rechercher c'est no_serie malgré que mon index cluster contiens ce colonne

je veux vous communiquer les syntaxes des indexes du table sérialisation

Code:

1
2
3
4
5
6
7
8
9
10
11
12
USE [Provisionning]
GO
 
/****** Object:  Index [_dta_index_SERIALISATION_7_661577395__K5_K1_K2_K3]    Script Date: 10/10/2016 12:34:38 ******/
CREATE NONCLUSTERED INDEX [_dta_index_SERIALISATION_7_661577395__K5_K1_K2_K3] ON [dbo].[SERIALISATION]
(
	[No_Materiel] ASC,
	[No_Serie] ASC,
	[No_UP] ASC,
	[No_UM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Code:

1
2
3
4
5
6
7
8
9
USE [Provisionning]
GO
 
/****** Object:  Index [IX_SERIALISATION_1]    Script Date: 10/10/2016 12:35:01 ******/
CREATE NONCLUSTERED INDEX [IX_SERIALISATION_1] ON [dbo].[SERIALISATION]
(
	[No_UM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO

Code:

1
2
3
4
5
6
7
8
9
USE [Provisionning]
GO
 
/****** Object:  Index [PK_SERIALISATION]    Script Date: 10/10/2016 12:35:18 ******/
ALTER TABLE [dbo].[SERIALISATION] ADD  CONSTRAINT [PK_SERIALISATION] PRIMARY KEY CLUSTERED 
(
	[No_Serie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO

Est ce que quelqu'un à une piste pour optimiser cette requîtes?

10/10/2016, 14h06
escartefigue

Bonjour,

Pouvez vous communiquer le DDL des 2 tables, a priori la requête imbriquée n'est pas nécessaire (et donc le groupage et le distinct), mais sans DDL, difficile d'être catégorique

bonjour

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE [dbo].[SERIALISATION](
	[No_Serie] [varchar](20) NOT NULL,
	[No_UP] [varchar](20) NULL,
	[No_UM] [varchar](20) NULL,
	[No_Materiel] [varchar](18) NULL,
 CONSTRAINT [PK_SERIALISATION] PRIMARY KEY CLUSTERED 
(
	[No_Serie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[SERIALISATION]  WITH NOCHECK ADD  CONSTRAINT [FK_SERIALISATION_MATERIEL] FOREIGN KEY([No_Materiel])
REFERENCES [dbo].[MATERIEL] ([No_Materiel])
GO
 
ALTER TABLE [dbo].[SERIALISATION] CHECK CONSTRAINT [FK_SERIALISATION_MATERIEL]
GO

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
CREATE TABLE [dbo].[LIVRAISON_SERIALISATION](
	[No_BL] [varchar](10) NOT NULL,
	[No_Serie] [varchar](20) NOT NULL,
	[No_Poste] [varchar](6) NULL,
 CONSTRAINT [PK_LIVRAISON_SERIALISATION] PRIMARY KEY CLUSTERED 
(
	[No_BL] ASC,
	[No_Serie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION]  WITH NOCHECK ADD  CONSTRAINT [FK_LIVRAISON_SERIALISATION_LIVRAISON] FOREIGN KEY([No_BL])
REFERENCES [dbo].[LIVRAISON] ([No_BL])
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION] CHECK CONSTRAINT [FK_LIVRAISON_SERIALISATION_LIVRAISON]
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION]  WITH NOCHECK ADD  CONSTRAINT [FK_LIVRAISON_SERIALISATION_SERIALISATION] FOREIGN KEY([No_Serie])
REFERENCES [dbo].[SERIALISATION] ([No_Serie])
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION] CHECK CONSTRAINT [FK_LIVRAISON_SERIALISATION_SERIALISATION]
GO

11/10/2016, 11h02
olivtone

salut a toi

as tu la main pour reecrire les requetes SQL ? et modifier la modelisation de la table

PK Clustered sur deux colonnes comment dire....:)

Bonjour,
alors déjà la jointure dans la clause where, c'est pas bon du tout !

Code:

1
2
3
4
5
6
7
8
9
SELECT COUNT(SERIALISATION.No_UM) AS Expr1
FROM SERIALISATION INNER JOIN 
     LIVRAISON_SERIALISATION ON SERIALISATION.No_Serie = LIVRAISON_SERIALISATION.No_Serie
WHERE (LIVRAISON_SERIALISATION.No_BL = '92514') AND 
      (SERIALISATION.No_Materiel = '25337') AND 
	  (SERIALISATION.No_UM = (SELECT DISTINCT No_UM
                              FROM            SERIALISATION
                              WHERE        (No_Serie = '31541255114')))
GROUP BY SERIALISATION.No_UM

ensuite c'est plus lisible avec des alias(réflexion purement subjective)

Code:

1
2
3
4
5
6
7
8
9
SELECT COUNT(S.No_UM) AS Mycount
FROM SERIALISATION S INNER JOIN 
     LIVRAISON_SERIALISATION LS ON S.No_Serie = LS.No_Serie
WHERE (LS.No_BL = '92514') AND 
      (S.No_Materiel = '25337') AND 
	  (S.No_UM = (SELECT DISTINCT No_UM
                              FROM            SERIALISATION
                              WHERE        (No_Serie = '31541255114')))
GROUP BY S.No_UM

Après je comprend pas forcément

Code:

1
2
3
4
AND 
	  (S.No_UM = (SELECT DISTINCT No_UM
                              FROM            SERIALISATION
                              WHERE        (No_Serie = '31541255114'))

Pourrais tu nous filer un petit jeu de donnée et nous expliquer ce que tu veux faire (le plus précisément possible)?
Cordialement,

Citation:
Envoyé par NULL008
bonjour
Code:

1 2 3 4 5 6 7 8 9 10 CREATE TABLE [dbo].[SERIALISATION]( [No_Serie] [varchar](20) NOT NULL, [No_UP] [varchar](20) NULL, [No_UM] [varchar](20) NULL, [No_Materiel] [varchar](18) NULL, CONSTRAINT [PK_SERIALISATION] PRIMARY KEY CLUSTERED ( [No_Serie] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY] ) ON [PRIMARY]
Effectivement l’utilisation d'un VARCHAR(20) pour une clef primaire est en terme "châtiés" une grosse merde ! En effet, il occupera au pire 22 octets et pour le lire nécessitera 3 passes dans le processeur, soit 6 passes pour faire une jointure par rapport à un BIGINT. En sus il y a toujours de l'extra overhead pour les données littérales liées à la gestion de la collation. Ce sera donc environ 4 fois moins performant qu'un BIGINT ou un INT.
En sus vous augmentez le volume globale de votre table et de tous vos index non clustered... Ce qui va rejaillir sur la maintenance, le stockage les sauvegardes...

Citation:

Envoyé par NULL008

[CODE]CREATE TABLE [dbo].[LIVRAISON_SERIALISATION](
[No_BL] [varchar](10) NOT NULL,
[No_Serie] [varchar](20) NOT NULL,
[No_Poste] [varchar](6) NULL,
CONSTRAINT [PK_LIVRAISON_SERIALISATION] PRIMARY KEY CLUSTERED
(
[No_BL] ASC,
[No_Serie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

Là c'est pire... La clef est composée de 2 colonnes VARCHAR donc en tout 34 octets soit 6 fois moins performant. En sus les statistiques d'optimisation n'étant disponible que pour la première colonne d'une clef composite, les performances des jointures vont se dégrader. Au bilan je pense que, par rapport à une clef INT ou BIGINT, vous allez être en moyenne entre 20 et 50 fois moins performant....

A +