[Sql Server 2008] Optimisation requête

Version imprimable

14/04/2010, 13h31
lucie.houel

[Sql Server 2008] Optimisation requête

Bonjour à tous et à toutes,

Je suis actuellement à la recherche de solutions pour otpimiser une requête qui ne me parait pas très performante.

Voilà le cas :

J'ai une table avec des documents et une table avec des tâches à faire sur les documents (utilisation d'une clé étrangère sur la table des documents classique). De plus chaque document peut "dépendre d'un autre", avec un maximum de 2 niveaux (utilisation d'une clé sur la table des documents elle-même)

Je voudrais avoir un SELECT qui me ressort tous mes documents avec une colonne : concaténation des tâches à faire et une colonne : nombre de documents dépendants.

Pour information, j'ai 12246 documents avec une moyenne de 2 tâches par document.

Je vous mets ici les tests déjà réalisés avec les résultats en terme de timing :

Select des documents (seulement eux) avec jointures classiques
==> 00:00:00

Select avec le nom des tâches : appel à une fonction qui met en oeuvre la méthode : SELECT @nom = @nom + ...
==> 00:01:20
Select avec le nom des tâches : appel à une fonction qui met en oeuvre un curseur.
==> 00:01:19

Select avec le nombre de documents dépendants : appel à une fonction qui met en oeuvre la méthode : SELECT @nombre = @nombre + ...
==> 00:02:42
Select avec le nombre de documents dépendants : appel à une fonction qui met en oeuvre un curseur.
==> 00:02:59

________________________________________________________________

Select des documents (seulement eux) en passant par l'insertion dans une table temporaire.
==> 00:00:00

UPDATE des colonnes :

Select avec le nom des tâches : appel à une fonction qui met en oeuvre la méthode : SELECT @nom = @nom + ...
==> 00:01:23
Select avec le nom des tâches : appel à une fonction qui met en oeuvre un curseur.
==> 00:01:19

Select avec le nombre de documents dépendants : appel à une fonction qui met en oeuvre la méthode : SELECT @nombre = @nombre + ...
==> 00:02:46
Select avec le nombre de documents dépendants : appel à une fonction qui met en oeuvre un curseur.
==> 00:02:30

Voilà où j'en suis de mes tests. Auriez-vous, s'il vous plaît, des idées de méthode que je pourrais tester pour améliorer ces performances.

Merci d'avance pour votre aide. :ccool:
14/04/2010, 13h38
Waldar

Regardez du côté des CTE récursives, voici le cours de SQLPro :
http://sqlpro.developpez.com/cours/s...te-recursives/
14/04/2010, 15h04
lucie.houel

Quand je teste la syntaxe dans une fenêtre de mon requeteur, ca fonctionne, mais qd je veux mettre le bout de code dans une fonction, j'ai une erreur. La syntaxe : WITH .... AS peut-elle être utilisée dans une fonction ?
14/04/2010, 16h20
lucie.houel

Problème de ";" avant le WITH, je vais tester.
14/04/2010, 16h36
lucie.houel

Avec la mise en oeuvre de cette méthode, je passe à :
==> 00:03:49 :(

D'autres idées ?
14/04/2010, 16h55
kagemaru

serait-il possible de poster la requête ou qqchose qui s'en rapproche ? merci
14/04/2010, 23h42
SQLpro

Virer toutes vos fonctions et exécutez directement des requêtes SELECT. Une fonction est itérative. Une requête est ensembliste. C'est comme si vous tentiez de faire le meilleur chrono du monde sur le circuit Bugatti du mans avec un convoi exceptionnel !

A +

Euh bah ca va etre coton 8O

Requête principale (je vous fais grâce de quelques colonnes en jointure simple) :

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
33
34
35
36
37
38
39
40
41
42
 
 
DECLARE @sp NVARCHAR(3000)
 
SET @sp = 
'SELECT	DISTINCT l.id_lignedesign AS ID, 
	et.ref_etude AS Etude,
             d.rando_design AS Rando,
	bado.dbo.NbreDesignGroupes(d.id_design) AS Groupement,
	d.nom_design AS Nom,
             d.ordre_design AS Ordre,
	l.datetheorique_lignedesign AS [Date théorique visite patient],
             l.daterelle_lignedesign AS [Date réelle visite patient],
	bado.dbo.NA(l.na_lignedesign) AS NA,
	bado.dbo.GetLigneDesignTrt(l.id_lignedesign, ''' + ISNULL(@nom, '@NULL@') + ''', ' + ISNULL(CONVERT(VARCHAR(5), @real), 'NULL') + ', ' + ISNULL(CONVERT(VARCHAR(5), @typeExec), 'NULL') + ') AS [Tâches]
FROM 	bado.dbo.T_LigneDesign l
INNER JOIN bado.dbo.T_Design d ON l.id_design = d.id_design'
 
IF @idEtude IS NOT NULL
  SET @sp = @sp + ' AND d.id_etude = ' + CONVERT(VARCHAR(5), @idEtude)
 
IF @categorie IS NOT NULL
  SET @sp = @sp + ' AND d.categorie_design = ' + CONVERT(VARCHAR(5), @categorie)
 
IF @typeExec IS NOT NULL
  SET @sp = @sp + ' INNER JOIN bado.dbo.T_DesignTrt dt ON dt.id_design = d.id_design AND dt.type_exec_designtrt = ' + CONVERT(VARCHAR(5), @typeExec)
 
SET @sp = @sp + '
  INNER JOIN bado.dbo.T_Patient pa ON l.id_patient = pa.id_patient
  INNER JOIN bado.dbo.T_Etude et ON pa.id_etude = et.id_etude
  INNER JOIN bado.dbo.T_EtudeUser eu ON et.id_etude = eu.id_etude
    AND eu.acces_etude = 1
    AND eu.type = ''Prm''
    AND eu.id_user = ' + CONVERT(VARCHAR(5), @idUser)
 
IF @date IS NOT NULL
  SET @sp = @sp + ' WHERE l.datetheorique_lignedesign <= ''' + CONVERT(VARCHAR(20), @date) + ''''
 
SET @sp = @sp + ' ORDER BY d.ordre_design'	
 
 
EXEC SP_EXECUTESQL @sp

bado.dbo.NbreDesignGroupes réécrite avec la CTE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
DECLARE @final INT
 
;WITH design_cte (id_design) AS 
(
	SELECT		id_design
	FROM		bado.dbo.T_Design 
	WHERE		id_design = @design
	UNION ALL
	SELECT		d.id_design
	FROM		bado.dbo.T_Design d
	INNER JOIN	design_cte dcte ON dcte.id_design = d.id_design_groupe
)
 
SELECT @final = COUNT(id_design) - 1 FROM design_cte
 
RETURN @final

bado.dbo.GetLigneDesignTrt :

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
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
 
 
-- Paramètres
 
@idLigneDesign INT,
@nom VARCHAR(50),
@real INT,
@typeExec INT
 
DECLARE @final VARCHAR(1000)
SET @final = ''
 
IF @real IS NULL
BEGIN
	IF @nom IS NULL OR @nom = '@NULL@'
	BEGIN
		IF @typeExec IS NULL
		BEGIN
			SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '')  + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '') 
			FROM  bado.dbo.T_DesignTrt r
			INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82
			LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
			INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
			WHERE r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
		END
		ELSE
		BEGIN
			SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '')  + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '') 
			FROM  bado.dbo.T_DesignTrt r
			INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82
			LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
			INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
			WHERE r.type_exec_designtrt = @typeExec AND r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
		END
	END
	ELSE
		BEGIN
			IF @typeExec IS NULL
			BEGIN
				SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '') + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '') 
				FROM  bado.dbo.T_DesignTrt r
				INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82 AND f.nom_fctrole like '%' + @nom + '%'
				LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
				INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
				WHERE r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
			END
			ELSE
			BEGIN
				SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '')  + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '') 
				FROM  bado.dbo.T_DesignTrt r
				INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82 AND f.nom_fctrole like '%' + @nom + '%'
				LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
				INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
				WHERE r.type_exec_designtrt = @typeExec AND r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
			END
		END
	END
	ELSE
	BEGIN
		IF @real = 1
		BEGIN
			IF @nom IS NULL OR @nom = '@NULL@'
			BEGIN
				IF @typeExec IS NULL
				BEGIN
[...]
 
END
 
IF @final <> ''
	SET @final = LEFT(@final, LEN(@final) - 1)
 
RETURN  @final

Du coup si vous avez des pistes pour tout faire en select direct, sans fonction, je suis preneuse, parce que là j'avoue .... :?8O

15/04/2010, 18h59
SQLpro

1) remplacez vos IF par des CASE et intégrez cela dans vos requêtes
2) si les CASE sont trop nombreux, alors écrivez plusieurs requêtes et faîte l'aiguillage dans une procédure stockée.

Chaque fois que vous ferez du code procédural (donc une fonction) dans du code ensembliste, vous perdez tout le bénéfice de l'ensembliste et forcer l'exécution en procédural c'est à dire ligne à ligne.

Le manque de culture sur l'univers des bases de données font que la plupart des développeurs veulent faire des fonctions ce qui tue les performances d'emblée, parce que la plupart des développeurs ne savent pas écrire des requêtes de plus de 5 lignes....

A +