OPTION(FORCE ORDER) execute plus vite la requête

Version imprimable

02/09/2009, 13h21
cmako

OPTION(FORCE ORDER) execute plus vite la requête

Bonjour,

J'aimerais savoir, de manière générale, pourquoi parfois en faisant un SELECT (multitable) il s'exécute en plusieurs heures, voir pas du tout, tandis que si l'on ajoute OPTION(FORCE ORDER) il met 50 secondes? 8O

Ça fait plusieurs fois que je tombe sur ce problème (qui en est un pour moi).
:(
02/09/2009, 14h18
SQLpro

L'optimisation dépend de nombreux paramètres différents. Sortir cette affimation sans un exemple précis n'a aucun intérêt :
1) quel est le modèle de données ?
2) quelles sont les contraintes des tables ?
3) quel sont les index des tables ?
4) quelle est la distribution des données et leur volume ?
5) les statistiques sont-elles à jour ?
6) les index sont-il défragmentés ?
7) les services pack sont-ils à jour ?
8) quelles sont les caractéristiques physiques de la machine ?
9) quelle est la configuration du serveur SQL ?
10) quels services tourne sur la machine en dehors de SQL Server ?
...
Il y a encore un vingtaine de questions de cet acabit à répondre avant de commencer à prétendre pouvoir expliquer des problèmes de performances...

A +

Citation:

Envoyé par SQLpro

L'optimisation dépend de nombreux paramètres différents. Sortir cette affimation sans un exemple précis n'a aucun intérêt

J'ai une requête comme celle-ci

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
30
31
32
UPDATE t1
SET
	t1.PGA_M_PRM_PER = t2.PRM_M_PRM_PURE
	,t1.PGA_M_PRM_ANN	= t2.PRM_M_PRM_PURE * 4
FROM FACT_PGA t1
INNER JOIN ( -- somme des primes pures
	SELECT t2.PGA_D_RES, t2.PRM_POL_NO_POL, t2.PRM_GAR_KEY, t2.PRM_D_ECHEA, t2.PGA_D_DEB_VER, SUM(t1.PRM_M_PRM_PURE) PRM_M_PRM_PURE
	FROM FACT_PRM t1
	INNER JOIN ( -- recherche de date d'échéance max
		SELECT t2.PGA_D_RES, t1.PRM_POL_NO_POL, t1.PRM_GAR_KEY, t2.PGA_D_DEB_VER, MAX(t1.PRM_D_ECHEA) PRM_D_ECHEA
		FROM FACT_PRM t1
		INNER JOIN DIM_GAR g1 ON g1.GAR_KEY = t1.PRM_GAR_KEY AND g1.GAR_TYP = 'COMPL'
		INNER JOIN DIM_PRO p1 ON p1.PRO_KEY = t1.PRM_PRO_KEY AND p1.PRO_C_BRCH = '0012'
		INNER JOIN FACT_PGA t2
			ON  t1.PRM_D_VAL <= t2.PGA_D_RES
			AND t1.PRM_POL_NO_POL = t2.PGA_POL_NO_POL
			AND t1.PRM_GAR_KEY = t2.PGA_GAR_KEY
		WHERE	t1.PRM_C_TYP_EMI = 'P' AND t1.PRM_SWI_ANNUL = 'N'
			AND t2.PGA_D_RES IN (SELECT DISTINCT DAT FROM #d_res)
		GROUP BY t2.PGA_D_RES, t1.PRM_POL_NO_POL, t1.PRM_GAR_KEY, t2.PGA_D_DEB_VER
	) t2
		ON  t1.PRM_POL_NO_POL = t2.PRM_POL_NO_POL
		AND t1.PRM_GAR_KEY = t2.PRM_GAR_KEY
		AND t1.PRM_D_ECHEA = t2.PRM_D_ECHEA
	WHERE t1.PRM_C_TYP_EMI = 'P' AND t1.PRM_SWI_ANNUL = 'N'
	GROUP BY t2.PGA_D_RES, t2.PRM_POL_NO_POL, t2.PRM_GAR_KEY, t2.PRM_D_ECHEA, t2.PGA_D_DEB_VER
)t2
	ON  t1.PGA_D_RES = t2.PGA_D_RES
	AND t1.PGA_POL_NO_POL = t2.PRM_POL_NO_POL
	AND t1.PGA_GAR_KEY = t2.PRM_GAR_KEY
	AND t1.PGA_D_DEB_VER = t2.PGA_D_DEB_VER
OPTION(FORCE ORDER)

En attachement les 2 plans d'exécution. 1 c'est une estimation et 2 c'est une exécution réelle avec option.

Les réponses aux questions :
1,2 - Pour l'instant il s'agit de tables simples sans relations, avec PK.
3 - il n'y a pas, sauf pour les tables en prod DIM_PRO
4 - sais pas
5 - c'est automatique
6 - c'est en mode automatique
7 - je ne sais pas, et je ne sais pas comment le savoir. SQL Manager est à jour.
8 - Je ne sais pas
9 - quoi précisément ?
10 - Rien. Je suis tout seul.

03/09/2009, 11h57
cmako

Citation:

Envoyé par SQLpro

5) les statistiques sont-elles à jour ?

Je viens de mettre à jour les statistiques. La requête s'est exécutée en 8 minutes au lieu de plus d'1 heure. Par contre OPTION(FORCE ORDER) est toujours plus rapide (1 min)
08/09/2009, 17h26
cmako

Je viens de trouver une explication. Effectivement le problème vient de statistiques pas à jour. Mais je ne comprenais pas pourquoi. Visiblement elles ne sont pas mises à jour après chaque INSERT. Mon exemple en question faisait un UPDATE juste après un INSERT et sur les lignes insérées. Donc c'est pour ça que sur le plan1 la flèche partant de FACT_PGA est toute fine.

Voilà. Voilà. :oops:

Bonjour,

Vos statistiques sont-elles mises à jour et crées automatiquement ?

Code:

1
2
3
4
5
6
7
8
SELECT CASE DATABASEPROPERTY('maBD', 'IsAutoCreateStatistics')
			WHEN 1 THEN 'Oui'
			ELSE 'Non'
		END AS CreationAutomatiqueDesStats,
		CASE DATABASEPROPERTY('maBD', 'IsAutoUpdateStatistics')
			WHEN 1 THEN 'Oui'
			ELSE 'Non'
		END AS MAJAutomatiqueDesStats

Si l'une des deux colonnes est à Non, faites :

Code:

1
2
ALTER DATABASE maBD
SET AUTO_CREATE_STATISTICS ON

et / ou

Code:

1
2
ALTER DATABASE maBD
SET AUTO_UPDATE_STATISTICS ON

@++ ;)