différence de performance incompréhensible entre 2 méthodes

**Pol63** · 19/05/2009, 14h42

bonjour

j'ai un requete assez complexe, qui prend pas mal de temps, et pourtant si je le fais en 2 fois via une table temporaire, c'est instantané

dans les 2 cas j'ai le meme with au dessus :

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
 
WITH 
	ListePlanning(Numero, IdPlanning, IdArticle)
	AS (SELECT  ROW_NUMBER() OVER (ORDER BY Planning.IdPlanning, Planning.OrderToUse DESC), Planning.idPlanning, Planning.IdArticle FROM Planning) 
,
 
	ListeArticlesUtilisesPourPlanning (IdPlanning, IdArticle) -- pour chaque planning, l'article du planning et les articles en provenance
	AS (SELECT Planning.IdPlanning, Planning.IdArticle FROM Planning 
		UNION SELECT Planning.IdPlanning, PlanningProv.IdArticle FROM Planning INNER JOIN PlanningProv ON Planning.IdPlanning = PlanningProv.IdPlanning)
,
 
	ListeIncompatibilites (IdArticle, IdArticleInterdit, NbBatch, IdIncompatibilite) -- ne contient que des isarticles (soit direct soit pris dans les regroupements définis)
				-- article à fabriquer, article précédent interdit, nombre entre chaque requis
	AS (SELECT case when vueFab.IdArticle IS NULL then IncompatibiliteDefinition.IdArticle else vueFab.IdArticle end as c1, 
				case when vueInterdit.IdArticle IS NULL then IncompatibiliteInterdite.IdArticle else vueInterdit.IdArticle end as c2,
				IncompatibiliteInterdite.NbBatch, IncompatibiliteDefinition.IdIncompatibilite
			FROM  IncompatibiliteDefinition	
				LEFT JOIN ArticleGetArticleEtRegroupements vueFab		ON vueFab.IdRegroupement = IncompatibiliteDefinition.IdArticle
				INNER JOIN IncompatibiliteInterdite		ON IncompatibiliteDefinition.IdIncompatibilite = IncompatibiliteInterdite.IdIncompatibilite
				LEFT JOIN ArticleGetArticleEtRegroupements vueInterdit	ON vueInterdit.IdRegroupement = IncompatibiliteInterdite.IdArticle
			)

1ère méthode : je tente de faire un select direct de ce que je veux

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
 
--  WITH mis au dessus
 
SELECT PlanningCourant.IdPlanning , ListeIncompatibilites.IdIncompatibilite, ListeIncompatibilites.IdArticle, ListeIncompatibilites.IdArticleInterdit
, ArticleFab.Code, ArticlePasse.Code
 
	FROM Planning PlanningCourant
		INNER JOIN ListePlanning						ON PlanningCourant.IdPlanning = ListePlanning.IdPlanning
		INNER JOIN ListePlanning TrouvePrecedent		ON TrouvePrecedent.Numero = ListePlanning.Numero - 1
		INNER JOIN Planning PlanningPrecedent			ON PlanningPrecedent.IdPlanning = TrouvePrecedent.IdPlanning -- récupération pour chaque idplanning, l'idplanning précédent
		INNER JOIN ListeArticlesUtilisesPourPlanning lc	ON lc.IdPlanning = PlanningCourant.IdPlanning
		INNER JOIN ListeArticlesUtilisesPourPlanning lp ON lp.IdPlanning = PlanningPrecedent.IdPlanning
		INNER JOIN ListeIncompatibilites				ON lc.IdArticle = ListeIncompatibilites.IdArticle
														AND lp.IdArticle = ListeIncompatibilites.IdArticleInterdit
		INNER JOIN Article ArticleFab					ON ArticleFab.IdArticle = ListeIncompatibilites.IdArticle
		INNER JOIN Article ArticlePasse					ON ArticlePasse.IdArticle = ListeIncompatibilites.IdArticleInterdit

2 ème méthode : je retire 2 jointures sur la table Article et je passe par une 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
 
DECLARE @IncompatibiliteTable table 
	(IdPlanning bigint, IdIncompatibilite bigint, IdArticle bigint, IdArticleInterdit bigint)
 
--  WITH mis au dessus
 
INSERT INTO @IncompatibiliteTable
 
SELECT PlanningCourant.IdPlanning , ListeIncompatibilites.IdIncompatibilite, ListeIncompatibilites.IdArticle, ListeIncompatibilites.IdArticleInterdit
 
	FROM Planning PlanningCourant
		INNER JOIN ListePlanning						ON PlanningCourant.IdPlanning = ListePlanning.IdPlanning
		INNER JOIN ListePlanning TrouvePrecedent		ON TrouvePrecedent.Numero = ListePlanning.Numero - 1
		INNER JOIN Planning PlanningPrecedent			ON PlanningPrecedent.IdPlanning = TrouvePrecedent.IdPlanning -- récupération pour chaque idplanning, l'idplanning précédent
		INNER JOIN ListeArticlesUtilisesPourPlanning lc	ON lc.IdPlanning = PlanningCourant.IdPlanning
		INNER JOIN ListeArticlesUtilisesPourPlanning lp ON lp.IdPlanning = PlanningPrecedent.IdPlanning
		INNER JOIN ListeIncompatibilites				ON lc.IdArticle = ListeIncompatibilites.IdArticle
														AND lp.IdArticle = ListeIncompatibilites.IdArticleInterdit
 
 
select tmp.*, ArticleFab.Code, ArticlePasse.Code
	FROM @IncompatibiliteTable tmp
		INNER JOIN Article ArticleFab					ON ArticleFab.IdArticle = tmp.IdArticle
		INNER JOIN Article ArticlePasse					ON ArticlePasse.IdArticle = tmp.IdArticleInterdit

on répète souvent que les tables temporaires ralentissent les traitements, que des lectures sont plus performantes donc j'ai essayé de décomposer mon traitement en plusieurs sous requetes

dans le 1er cas, je retrouve 2 IdArticle, et je fais une jointure sur la table article pour retrouver le code de l'article
ca prend des 10aines de secondes

dans le 2eme cas, je stocke le résultat et donc les 2 IdArticle dans une table temporaire, puis je fais un select de cette table temporaire avec la table article pour avoir le Code
c'est instantané

ma table article comporte 300 ligne

si quelqu'un peut m'expliquer pourquoi sql server s'emmele les pinceaux avec ma requete ...

**SQLpro** · 19/05/2009, 16h02

je pense qu c'est assez simple : vous n'avez pas préfixé vos objets par le schéma SQL.

A +

**Pol63** · 19/05/2009, 16h09

ce n'est pas aussi simple malheureusement ...
je viens de faire le test en rajoutant dbo. dans les clauses FROM et pas mieux ...

vous m'aviez dejà dit que vous aviez réussi chez un client à significativement augmenter les perfs en préfixant par le propriétaire, mais suite à ca j'avais fait des tas de tests et je n'avais vu aucune différence !

je veux bien vous croire, mais ca ne doit pas etre vrai dans tous les cas
nous n'avons pas créés de propriétaires sur la base et nous n'utilisons que 2 utilisateurs sur la base (et 2 logins sql)

**SQLpro** · 19/05/2009, 17h16

Commencez par nous dire quelle version de SQl : 2000, 2005, 2008, 7 ????

A +

**Pol63** · 19/05/2009, 17h19

SQL Server 2005 standard 64bits SP3 sur Windows Server 2003 R2 standard 64bits SP2

sur le plan d'execution réel j'ai vu 2 ou 3 index spool qui prenent chacun entre 10 et 15% mais je vois pas trop ce que c'est index spool

**elsuket** · 19/05/2009, 19h07

Bonjour,

l'opérateur index spool place les lignes qu'il reçoit en entrée dans TempDB pour le temps de la requête, et crée un index non-cluster dessus.
Vous accédez donc à un autre groupe de fichiers, et vous créez un index dessus, c'est cela qui doit être à l'origine de la lenteur.

A mon sens cela indique une indexation manquante ... voyons ce qu'en pensent d'autres participants

@++

**Pol63** · 19/05/2009, 19h18

ma requete ne retourne que peux de lignes, mes tables sont pas spécialement remplies, par contre ne connaissant pas totalement le fonctionnement d'sql server je ne sais pas ce qu'il fait avec les with
est-ce qu'il charge tout avec de faire le select associé ou est-ce qu'il traite les jointures dans la foulée pour limiter le volume (enfin mes tables sont pas biens grosses)

**mikedavem** · 19/05/2009, 19h50

Bonsoir,

Je suis du même avis qu'Elsuket. Il doit vous manquer un ou des index sur les tables concernés par votre requête ... sur la table article peut être ?? ..

++

**Pol63** · 19/05/2009, 20h10

la table article a un index sur le colonne code
et si j'ai bien compris un index a intègre la PK en plus des colonnes définies

**elsuket** · 19/05/2009, 22h42

Ce sera difficile à dire sans le DDL de vos tables et le plan de requête

un index a intègre la PK en plus des colonnes définies

Pas tout à fait.
D'abord un clé primaire reste une clé primaire, c'est SQL Server qui crée implicitement un index ordonné en cluster sur les colonnes qui portent la clé.

Les niveaux intermédiaires de l'arbre de tri contiennent les valeurs de la clé (et un RowID), selon l'ordre physique de celle-ci, et seul le niveau feuille contient les pages de données de la table.

En ce sens :

- il ne peut y avoir qu'un seul index cluster par table,
- un index cluster est plus sujet aux splits de page,
- un index cluster est un doublon de la table,
- une analogie en est l'index que vous trouvez au début d'un livre.

Un index non-cluster est ordonné suivant l'ordre logique des valeurs de sa clé (et un keyID), et son niveau feuille ne contient pas les pages de données, mais les lignes de l'index cluster.

En ce sens :

- il peut y avoir plusieurs indexes non-cluster sur une table,
- un index non-cluster est moins sujet aux splits de page,
- une analogie en est l'index que vous trouvez à a fin d'un livre.

Il est donc très facile de rendre un index non-cluster couvrant.
C'est encore plus vrai dès SQL Server 2005 avec les colonnes incluses.

Quel est le cardinal de la table ListePlanning ?
Comment est-elle indexée ?

@++

différence de performance incompréhensible entre 2 méthodes

MS SQL Server

Discussions similaires

Partager

Partager