Comportement curieux requete tres lente

**olibara** · 03/06/2014, 06h00

Bonjour

J'ai un comportement bizare
J'execute une vue un petit peu complexe qui fait plusieurs agregation et jointure
Mais en trois secondes elle me donne un résultat de +/- 600 records, c'est tout a fait acceptable

Si j'ajoute un filtre qui devrait me donner 2 records a partir de la meme vue ca rame plus d'une minute !

J'ai essayé d'emballer le resultat de la vue dans un WITH () et de faire le filtre sur le resultat du WITH : meme problème

Par contre si je fais un bricole du genre

Code :

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

1
2
3
4
5
6
7
8
9
10
DROP TABLE Tempo;
SELECT 
* 
INTO TEMPO
FROM MaVue;
SELECT 
* 
FROM 
TEMPO
WHERE Ma Condition

J'ai mon resultat en +/- 3 Secondes

Comment expliquer cela et existe -il des manière plus "propre" de resoudre ce gentre de difficultés ?

N.B. J'ai aussi passé l'éxecution plan qq fois qui m'a suggéré d'ajouter qq index mais ca n'a pas changé grand chose !

Merci de votre aide

**mikedavem** · 03/06/2014, 08h02

Hello,

Le fait d'ajouter un filtre peut changer la façon dont SQL Server exécute la requête. Pour répondre à ton problème il faudrait étudier le profil de ta requête sans filtre, avec filtre et avec le SELECT INTO ... FROM TABLE WHERE FILTREE = .. sans quoi on ne pourrait faire que des suppositions ou hypothèses non constructives.

Si tu peux nous fournir le plan d'exécution réel (et pas estimé) de tes requêtes on peut tenter de regarder.

++

**olibara** · 03/06/2014, 08h37

Merci Mikedavem

Comment puis-je obtenir le plan d'execution réel dans SSMS ?

Je n'ai jamais compris (ni cherché) a quoi servait le bouton "Include actual execution plan" qui semble n'avoir aucun effet.

Et j'utilise de temps en tems le "Display estimated execution plan"

**mikedavem** · 03/06/2014, 08h59

Il faut effectivement que tu utilises le bouton "Include actual execution plan". Après l'exécution de ta requête tu as un nouvel onglet qui apparaît et qui t'affiche le plan d'exécution réel de ta requête. La tu fais un clic droit sur le plan > Save Execution Plan As. Tu choisis un nom et tu devrais avoir un fichier avec l'extension .sqlplan

++

**olibara** · 03/06/2014, 11h53

Merci MikeDavem
Enfin j'ai apris comment utilisier cette option

Voici ma simple requete sur la View BccBlcFac

Code :

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

1
2
 select  *   from BccBlcFac  
 Where CustId='WB00000918'  AND (STBLC IS NULL OR STBLC<>'IN')

Sans le Where : execution = 3 secondes
Avec le Where : 2 minutes

J'ai zippé les deux plans sinon ils ne passaient pas

**aieeeuuuuu** · 03/06/2014, 13h38

Bonjour,

Les estimations sont archi fausses, et dans le cas de la requête avec filtre, c'est carrément un euphémisme !
La première chose à faire est donc de vérifier si les statistiques sont à jour sur les tables concernées, à commencer par la table docdet.

Peut-on voir la définition de la vue BccBlcFac ?

**olibara** · 03/06/2014, 18h45

Merci Aieuuuu

Voici la vue

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
select  
CAST(BCC.number AS INT) AS BCC, 
CAST(BLC.number AS INT) AS BLC, 
DHBCC.Status AS STBCC, 
DHBLC.Status AS STBLC, 
CAST(DHFAC.number AS VARCHAR) AS FAC, 
DHBCC.Date AS DateBCC, 
DHBLC.Date AS DateBLC, 
DHFAC.Date AS DateFAC, 
RTRIM(DHBCC.thirdgroup) AS thirdgroup,  
cust.adrcountry AS IsoCC,   
cust.custid,
BCCQT.Grade, 
BCCQT.Qty, 
DHBCC.d_costamnt AS Cost, 
DHBCC.d_marg_eur AS Margin, 
 (DHBCC.d_costamnt+DHBCC.d_marg_eur) AS Tot, 
 DHBCC.d_marg_pou as prct,
 DHBCC.dlvdate2 AS DateDLV 
 
 from docdet BCC  
 inner join art on BCC.artid=art.a_hrk and BCC.artid>''  
 inner JOIN ( SELECT      
            CAST (BCC.number AS INT) AS BCCNUM,     
            BCC.c_neededgr AS Grade,     
            CAST(SUM(BCC.Qty) AS INT) AS Qty     
            FROM      
            docdet BCC     
            INNER JOIN art ON BCC.artid = art.a_hrk AND art.a_hrk>''      
            WHERE     
            BCC.jnl = '7-BCC'     
            group BY     
            CAST (BCC.number AS INT),     
            BCC.c_neededgr) AS BCCQT ON BCCQT.BCCNUM=BCC.number 
            LEFT OUTER JOIN  (    SELECT 
               number,
               docdetid2     
               FROM docdet     
               INNER JOIN art ON docdet.artid = art.a_hrk AND art.a_hrk>''     
               WHERE jnl='7-BLC' ) BLC ON BLC.docdetid2=BCC.docdetid   
            inner join dochead DHBCC on DHBCC.jnl='7-BCC' AND DHBCC.number=BCC.number 
            LEFT OUTER JOIN dochead DHBLC ON DHBLC.number=BLC.number AND DHBLC.jnl='7-BLC'   
            LEFT OUTER JOIN dochead DHFAC ON DHFAC.number=DHBLC.number2 AND DHFAC.jnl='7-FAC'   
            INNER join cust on cust.custid=DHBCC.thirdid  
            where   BCC.jnl='7-BCC'  
            GROUP BY 
             BCCQT.Grade,  
             BCCQT.Qty,  
             DHBCC.thirdgroup,  
             DHBCC.Status, 
             DHBLC.Status, 
             DHBCC.Date,  
             DHBLC.Date, 
             DHFAC.Date, 
             DHBCC.d_costamnt, 
             DHBCC.d_marg_eur, 
             BCC.number,  
             BLC.number,  
             DHFAC.number,  
             cust.adrcountry,  
             cust.custid,
             (DHBCC.d_costamnt+DHBCC.d_marg_eur), 
             DHBCC.d_marg_pou ,
             DHBCC.dlvdate2

**mikedavem** · 03/06/2014, 18h57

+1 avec aieeeuuuuu les estimations de cardinalité sont loin de la réalité.

Est-ce que tu pourrais faire une mise à jour de tes statistiques sur la table dcdet et sur la table art et voir si cela te donne quelque chose ?

++

**olibara** · 03/06/2014, 19h45

Rien n'y fait

La solution la plus efficace et de TRES TRES loin c'est ca

Code :

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

1
2
3
4
5
6
drop table #tmpx;
SELECT *
INTO #tmpx
  FROM [Logidrive].[dbo].[BccBlcFac] 
  SELECT * From  #tmpx
WHERE #tmpx.CustId='WB00000918'  AND (STBLC IS NULL OR STBLC<>'IN')

C'est d'ailleurs un comportement que j'avais remarqué aussi en MySql ou le fait de passer par des tables temporaire etait largement plus efficace que de construire une seule requete avec des select imbiiqué !!

Je pensais que SQL Serveur serait plus malin mais ca ne semble pas etre le cas malheureusement

**mikedavem** · 03/06/2014, 21h01

Il arrive que de temps en temps casser une grosse requête monolithique aide l'optimiseur à considérer moins de facteurs. Cela conduit à avoir de multiples plans optimales plus performant qu'un seul gros plan non optimal.
Mais je pense qu'ici il y a possibilité d'améliorer en retouchant un peu la requête ... je n'ai pas de solution immédiate mais ca vaut le coup de chercher un peu.

**SQLpro** · 04/06/2014, 17h57

Envoyé par olibara

Merci Aieuuuu

Voici la vue

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
select  
CAST(BCC.number AS INT) AS BCC, 
CAST(BLC.number AS INT) AS BLC, 
DHBCC.Status AS STBCC, 
DHBLC.Status AS STBLC, 
CAST(DHFAC.number AS VARCHAR) AS FAC, 
DHBCC.Date AS DateBCC, 
DHBLC.Date AS DateBLC, 
DHFAC.Date AS DateFAC, 
RTRIM(DHBCC.thirdgroup) AS thirdgroup,  
cust.adrcountry AS IsoCC,   
cust.custid,
BCCQT.Grade, 
BCCQT.Qty, 
DHBCC.d_costamnt AS Cost, 
DHBCC.d_marg_eur AS Margin, 
 (DHBCC.d_costamnt+DHBCC.d_marg_eur) AS Tot, 
 DHBCC.d_marg_pou as prct,
 DHBCC.dlvdate2 AS DateDLV 
 
 from docdet BCC  
 inner join art on BCC.artid=art.a_hrk and BCC.artid>''  
 inner JOIN ( SELECT      
            CAST (BCC.number AS INT) AS BCCNUM,     
            BCC.c_neededgr AS Grade,     
            CAST(SUM(BCC.Qty) AS INT) AS Qty     
            FROM      
            docdet BCC     
            INNER JOIN art ON BCC.artid = art.a_hrk AND art.a_hrk>''      
            WHERE     
            BCC.jnl = '7-BCC'     
            group BY     
            CAST (BCC.number AS INT),     
            BCC.c_neededgr) AS BCCQT ON BCCQT.BCCNUM=BCC.number 
            LEFT OUTER JOIN  (    SELECT 
               number,
               docdetid2     
               FROM docdet     
               INNER JOIN art ON docdet.artid = art.a_hrk AND art.a_hrk>''     
               WHERE jnl='7-BLC' ) BLC ON BLC.docdetid2=BCC.docdetid   
            inner join dochead DHBCC on DHBCC.jnl='7-BCC' AND DHBCC.number=BCC.number 
            LEFT OUTER JOIN dochead DHBLC ON DHBLC.number=BLC.number AND DHBLC.jnl='7-BLC'   
            LEFT OUTER JOIN dochead DHFAC ON DHFAC.number=DHBLC.number2 AND DHFAC.jnl='7-FAC'   
            INNER join cust on cust.custid=DHBCC.thirdid  
            where   BCC.jnl='7-BCC'  
            GROUP BY 
             BCCQT.Grade,  
             BCCQT.Qty,  
             DHBCC.thirdgroup,  
             DHBCC.Status, 
             DHBLC.Status, 
             DHBCC.Date,  
             DHBLC.Date, 
             DHFAC.Date, 
             DHBCC.d_costamnt, 
             DHBCC.d_marg_eur, 
             BCC.number,  
             BLC.number,  
             DHFAC.number,  
             cust.adrcountry,  
             cust.custid,
             (DHBCC.d_costamnt+DHBCC.d_marg_eur), 
             DHBCC.d_marg_pou ,
             DHBCC.dlvdate2

Pour pouvoir améliorer votre requête il nous faut le DDL.

La solution consiste à supprimer toutes les colonnes non clef du GROUP BY et joindre au final pour le détail.

A +

**aieeeuuuuu** · 04/06/2014, 18h09

Je pense même que la vue pourrait être réécrite.

Par exemple, ceci

Code :

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

1
2
3
INNER JOIN dochead DHBCC ON DHBCC.jnl='7-BCC' AND DHBCC.number=BCC.number 
            LEFT OUTER JOIN dochead DHBLC ON DHBLC.number=BLC.number AND DHBLC.jnl='7-BLC'   
            LEFT OUTER JOIN dochead DHFAC ON DHFAC.number=DHBLC.number2 AND DHFAC.jnl='7-FAC'

les deux jointures externes pourraient être supprimées...

Sans avoir regardé dans le détail, on pourrait peut être au moins réduire, si ce n'est se passer complétement des sous requetes.

Mais à mon avis, le problème se trouve encore plus en amont, certainement au niveau de la modélisation. les DDL des tables sous-jacentes seraient en effet un bon début pour y voir plus clair.

Le choix des types de données serait sans doute aussi à revoir... pourquoi tous ces transtypages ?

**elsuket** · 04/06/2014, 18h28

Bonjour à tous,

Dans SQL Server en tous cas ce comportement est normal, puisque les statistiques d'une table temporaire sont recalculées dès lors qu'une requête DML affecte 6 lignes ou plus.
Dès lors elles sont correctes, donc l'estimation de cardinalité se fait proprement, et le plan a de grande chances d'être optimal ... mais pas l'utilisation de TempDB !
TempDB est une base de données système très particulière : on devrait dès lors la considérer en tant que telle

Il est clair que vos estimations de cardinalités sont totalement faussées, et ce sur plusieurs tables.
Je vous propose donc d'exécuter le batch suivant sur la base de données en question :

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
SET NOCOUNT ON
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
-------------------------------------------------------------------------------------------------------
DECLARE @recompute bit = 1 -- xml_partner_ratefix_filters
-------------------------------------------------------------------------------------------------------
IF (SELECT OBJECT_ID('TEMPDB.dbo.#STALE_STAT')) IS NOT NULL
BEGIN
	DROP TABLE #STALE_STAT
END
 
;WITH
	SRT(low_boundary, high_boundary, threshold) AS
	(
		SELECT	V.low_boundary
			, V.high_boundary
			, V.threshold
		FROM	(
				VALUES (1, 1000, 0.1)
					, (1001, 5000, 0.0800)
					, (5001, 10000, 0.0750)
					, (10001, 50000, 0.0700)
					, (50001, 100000, 0.0650)		-- 50,000 and 100,000
					, (100001, 250000, 0.0625)		-- 100,001 and 250,000
					, (250001, 500000, 0.0600)		-- 250,000 and 500,000
					, (500001, 1000000, 0.0575)		-- 500,001 and 1,000,000
					, (1000001, 2500000, 0.0550)		-- 1,000,001 and 2,500,000
					, (2500001, 5000000, 0.0525)		-- 2,500,001 and 5,000,000
					, (5000001, 7500000, 0.0500)		-- 5,000,001 and 7,500,000
					, (7500001, 10000000, 0.0475)		-- 7,500,001 and 10,000,000
					, (10000001, 25000000, 0.0450)		-- 10,000,001 and 25,000,000
					, (25000001, 50000000, 0.0425)		-- 25,000,001 and 50,000,000
					, (50000001, 75000000, 0.0400)		-- 50,000,001 and 75,000,000
					, (75000001, 100000000, 0.0375)		-- 75,000,001 and 100,000,000
					, (100000001, 250000000, 0.0350)	-- 100,000,001 and 250,000,000
					, (250000001, 500000000, 0.0325)	-- 250,000,001 and 500,000,000
					, (500000001, 750000000, 0.0300)	-- 500,000,001 and 750,000,000
					, (750000000, 1000000000, 0.0275)	-- 750,000,001 and 1,000,000,000
			) AS V(low_boundary, high_boundary, threshold)
	)
	, OBSOLETE_STAT AS
	(
			SELECT		S.name AS schema_name
					, O.name AS object_name
					, QUOTENAME(D.name) AS stat_name
					, LEFT(SC.stat_column_list, LEN(SC.stat_column_list) - 1) AS stat_column_list
					, D.filter_definition
					, PS.row_count
					, SI.rowmodctr
					, CAST((100 * CAST(SI.rowmodctr AS float)) / CASE PS.row_count WHEN 0 THEN 1 ELSE PS.row_count END AS decimal(13,2)) AS [gap_%]
					, CAST(SRT.threshold * 100 AS decimal(5, 2)) AS pct
					, CAST(PS.row_count * SRT.threshold AS bigint) AS SRT
					, STATS_DATE(D.object_id, D.stats_id) AS last_update
			FROM		sys.schemas AS S
			INNER JOIN	sys.objects AS O
						ON S.schema_id = O.schema_id
			INNER JOIN	sys.stats AS D
						ON O.object_id = D.object_id
			INNER JOIN	(
						SELECT	object_id
							, MAX(row_count) AS row_count
						FROM	sys.dm_db_partition_stats AS PS
						GROUP	BY object_id
					) AS PS
						ON PS.object_id = O.object_id
			INNER JOIN	sys.sysindexes AS SI WITH (FORCESEEK)
						ON SI.id = D.object_id
						AND SI.indid = D.stats_id
			INNER JOIN	SRT 
						ON PS.row_count BETWEEN SRT.low_boundary AND SRT.high_boundary
						AND SI.rowmodctr > PS.row_count * SRT.threshold
			CROSS APPLY	(
						SELECT		C.name + ','
						FROM		sys.stats AS S 
						INNER JOIN	sys.stats_columns AS SC
									ON S.object_id = SC.object_id
									AND S.stats_id = SC.stats_id
						INNER JOIN	sys.columns AS C
									ON C.object_id = SC.object_id
									AND C.column_id = SC.column_id
						WHERE		D.name = S.name
						ORDER BY	SC.stats_column_id
						FOR XML PATH ('')
					) AS SC (stat_column_list)
			WHERE		S.name <> 'sys'
			AND		O.type IN ('U', 'V') -- user table or indexed view
	)
SELECT	schema_name
	, object_name
	, stat_name
	, last_update
	, stat_column_list
	, filter_definition
	, row_count
	, rowmodctr
INTO	#STALE_STAT
FROM	OBSOLETE_STAT
OPTION	(RECOMPILE)
 
SELECT	*
FROM	#STALE_STAT
 
IF @recompute = 1
BEGIN
	DECLARE @sql nvarchar(max)
 
	SELECT	@sql = CASE
				WHEN @sql IS NULL THEN ''
				ELSE @sql
			END + ';UPDATE STATISTICS ' + QUOTENAME(schema_name) + '.' + QUOTENAME(object_name) + '(' + stat_name + ')'
	FROM	#STALE_STAT
 
	PRINT @sql
 
	EXEC (@sql)
END

C'est un script que j'utilise en production depuis quelques années, et qui s'exécute une fois par jour et par instance SQL Server.
Dans le pire des cas, sur une base de données de plusieurs téra-octets, ce script s'exécute en quelques minutes.

Ré-exécutez ensuite la requête avec filtre. Si elle s'exécute toujours aussi lentement, postez le nouveau plan d'exécution réel

@++

**olibara** · 04/06/2014, 20h44

Merci à tous

Merci Elsuket

Demain matin j'essaye ta requete
Malheureusement je n'ai absolument pas la main sur le desing des tables de la DB qui est utilisé par une application tierce
Je peux tout au plus ajouter des index ou des tables mais en aucun cas toucher au desing existant qui n'est certe pas idéal

Mais ca ne devrait pas pouvoir justifier qu'une requete avec Filtre prenne +/- 40 fois plus de temps quue sans filtre et que le passage par la creation d'une table pour executer le filtre sur celle ci soit infiniment plus rapide

J'avais deja eu un cas similaire ou la simple présence d'un TOP semblait perdre MSSQL dans une boucle infinie !

**olibara** · 05/06/2014, 07h44

Merci de votre aide a tous

Mais je pense qu'il n'y a pas de solution

J'ai exécuté la requete d'elsuket ca n'a strictement rien changé, ca semble meme pire !
Pour la supression des OUTER JOIN suggérée par Aieeuu ca change la logique et le résultat de la requete, ce n'est pas une solution

Comme cette requete s'execute a partir d'une application Csharp, j'ai abandonné le filtre en SQL et je filtre le résultat dans l'application !

J'envoie quand meme l'execution plan

**dbaffaleuf** · 06/06/2014, 17h06

Envoyé par mikedavem

+1 avec aieeeuuuuu les estimations de cardinalité sont loin de la réalité.

Est-ce que tu pourrais faire une mise à jour de tes statistiques sur la table dcdet et sur la table art et voir si cela te donne quelque chose ?

++

Vous ne regarderiez pas les écarts de cardinalité avec plan explorer en version < 7.2.42.0 par hasard ?

Il y a un bug sur le calcul des estimates lorsque le jeu intérieur de la nested loops est un clustered key lookup avec un prédicat résiduel, ce qui est le cas ici notamment sur le nodeID="114". Au lieu d'afficher l'estimate qui est indiqué dans le plan, il affiche le produit des estimates sur chaque branche de la jointure (11867.3 * 11885.3 = 141046420,69)

Corrigé en 7.2.42.0, BugID 3589. Regardez le plan dans SSMS, l'estimation est juste. cf: http://downloads.sqlsentry.com/downloads/sqlsentryplanexplorer/PEChange_List.html

A+

**dbaffaleuf** · 06/06/2014, 17h30

Et quant à l'écart sur le NodeID="99" (Repartition Streams, est. 12195, act. 1449), c'est lié au Bitmap qui se trouve au dessus (NodeID="48") qui pousse le prédicat sur la branche de PROBE.

Comportement curieux requete tres lente

Développement SQL Server

Discussions similaires

Partager

Partager