Discussion :

[NULL008]

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 :

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

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 :

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

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 :

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

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 :

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

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?

[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

[NULL008]

bonjour

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

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

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

[Bernardos]

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

Code :

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

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 :

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

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 :

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

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,

[SQLpro]

bonjour

Code :

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

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

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