Discussion :

[Sergejack]

Waldar, est-ce que vous pourriez me donner un backup de la database ?
Je n'obtiens pas du tout les même résultats quoi que j'essaie, peut-être quelque chose au niveau la configuration de votre DB m'échappe.

Si vous ne souhaitez pas que j'ai accès à d'autres tables de la copie de votre base voici un query qui les supprimera :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
DECLARE @i INT
SET @i = 0
 
WHILE @i < 20 -- titré de mon chapeau
BEGIN
	EXEC sp_MSforeachtable 'IF ''?'' NOT LIKE ''\[%\].\[test\]'' ESCAPE ''\'' DROP TABLE ?'
 
	SET @i = @i + 1
END

Ça m'aiderait bcp à y voir plus clair.

[StringBuilder]

Dans tous les cas, j'attire quand même votre attention sur le bloc PL que j'ai testé hier.

Encapsulé dans une fonction si nécessaire, je pense que c'est une solution plus propre et plus performante par rapport aux autres solutions testées.

[iberserk]

Sinon de telles requêtes ne cachent'elle pas une modélisation à revoir?

[mikedavem]

Je ne suis posé la question de savoir si la requête de SergeJack était vraiment si astucieuse ? A première vue oui (Je reprends le jeu d'essai de Waldar avec 1000 000 de lignes de données dans la table test) mais au final je ne pense pas que cela soit le cas (attention pas d'attaque sur la personne mais simplement un complément de post sur ce thread )

(0 row(s) affected)
Table 'test'. Scan count 1, logical reads 3083, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)
Table 'test'. Scan count 1, logical reads 3083, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)
Table 'test'. Scan count 50, logical reads 150, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tally'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Les dernières statistiques correspondent à la requête de SergeJack. Pour moi même constat le like est plus performant. Mais cela n'est pas si étonnant que cela. Dans le 1er cas on lit en une seule fois le niveau feuille de l'index (Scan count = 1) alors que dans le cas de SergeJack on parcours 50 fois ce même index (Scan count = 50) à chaque fois. L'index possédant 3 niveaux dans mon cas on arrive bien à 3 X 50 = 150 reads.

La question est donc la suivante : Est-il plus performant de faire une seule lecture du niveau feuille de l'index ou parcourir 50 fois 3 niveaux de l'index pour récupérer nos données ?

Le query cost des plans d'exécution (actual plan et non le plan estimé) est le suivant pour les 3 requêtes :

21%
21%
58% (requête SergeJack)

EDIT : J'ai rajouté un index sur la colonne filepath dans mon cas pour parcourir moins de pages que l'index cluster (je passe de 5000 pages environ à 3000 pages pour la lecture du niveau feuille de l'index)

++

[Sergejack]

Il ne faut pas toujours se fier aux plans, les plans restent des estimations et des fois l'écart est énorme entre l'estimation et le résultat.

Mikedavem, vous aussi vous obtenez comme Waldar le prétend, de meilleurs temps de réponse avec le like ?
Si oui, pourriez-vous me faire une copie de la DB (avec uniquement la table test), svp ?

[Waldar]

Voici le backup complet de la base de test.
N'étant pas adepte de ces manipulations, si ça ne convient pas dites-le moi !

[StringBuilder]

Ça m'a pris un bon moment pour faire ce test (près de la soirée :o), et je vous donne les résultats :

Table test de 10 000 000 lignes (10 millions oui oui vous avez bien lu), soit une table de plus de 400 Mo.

Les valeurs sont constituées de GUID tronqués entre 8 et 16 caractères.

Voici la répartition de la population :

Code :

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

1
2
3
4
 
select idclub, COUNT(*)
from test
group by idclub

0 4999032
1 5000968

Code :

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

1
2
3
4
 
select LEN(filepath), COUNT(*)
from test
group by LEN(filepath)

15 1249700
9 1250538
12 1249548
10 1248268
13 1251618
11 1249842
14 1248826
8 1251660


Voici le script de test :
- Un nouveau GUID est utilisé à chaque itération, afin de tester de façon significative les performances des index, plutôt que d'utiliser tout le temps la même valeur et recharger le résultat en cache.
- Les boucles font 100 itérations chacunes, afin de plomber le serveur suffisamment longtemps pour que les différenses de timing soient significatives. Vu le volume de 10 millions de lignes, j'ai dû capituler quand j'ai tenté avec 1000 itérations.
- Les boucles sont testées séparément, afin de calculer le temps global des boucles plutôt qu'une addition des temps de requêtes, qui sera faussé si une requête dure moins de 1 ms.

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
 
DECLARE @D DATETIME
DECLARE @i INT
DECLARE @j INT
DECLARE @i1 INT
DECLARE @i2 INT
DECLARE @i3 INT
DECLARE @i4 INT
DECLARE @Dummy1 INT
DECLARE @Dummy2 INT
DECLARE @Dummy3 INT
DECLARE @Dummy4 INT
DECLARE @nbtest INT
 
DECLARE @word nvarchar(50)
 
SET @i1 = 0
SET @i2 = 0
SET @i3 = 0
SET @i4 = 0
SET @Dummy1 = 0
SET @Dummy2 = 0
SET @Dummy3 = 0
SET @Dummy4 = 0
 
set @nbtest = 100
 
SET @D = GETDATE()
SET @i = 0
WHILE @i < @nbtest
BEGIN
	set @word = NEWID()
	SELECT @Dummy1 = COUNT(*)
	FROM test
	WHERE idclub IN (0, 1) AND LEFT(@word, len(filepath)) = filepath
	SET @i = @i + 1
END
SET @i1 = DATEDIFF(second, @D, GETDATE())
 
SET @D = GETDATE()
SET @i = 0
WHILE @i < @nbtest
BEGIN
	set @word = NEWID()
	SELECT @Dummy2 = COUNT(*)
	FROM test
	WHERE idclub IN (0, 1) AND @word LIKE filepath + '%'
	SET @i = @i + 1
END
SET @i2 = DATEDIFF(second, @D, GETDATE())
 
SET @D = GETDATE()
SET @i = 0
WHILE @i < @nbtest
BEGIN
	set @word = NEWID()
	SELECT @Dummy3 = count(*)
      FROM test AS T1
           INNER JOIN
           (SELECT nb, LEFT(@Word, nb) AS sWord
              FROM Tally
             WHERE nb <= len(@Word)) AS T2
             ON T2.sWord = T1.filepath
     WHERE idclub IN (0, 1)
	SET @i = @i + 1
END
SET @i3 = DATEDIFF(second, @D, GETDATE())
 
SET @D = GETDATE()
SET @i = 0
WHILE @i < @nbtest
BEGIN
	set @word = NEWID()
 	SET @j = LEN(@word)
 
	while @j > 0
	begin
 
		SELECT @Dummy4 = COUNT(*)
		FROM test
		WHERE idclub IN (0, 1) AND LEFT(@word, @j) = filepath
 
		IF @Dummy4 = 1
		begin
			break
		end
 
		SET @j = @j - 1
	end
	SET @i = @i + 1
END
SET @i4 = DATEDIFF(second, @D, GETDATE())
 
SELECT @i1 "Left + Len", @i2 "Like", @i3 "Tally", @i4 "PL";

Résultat :
Ma solution à pase de LEFT + LEN : 140 secondes
Like : 222 secondes
La jointure avec la table Tally : 0 secondes
Le bloc PL : 0 secondes

J'ai refait tourner 100 000 fois les deux dernières solution, en vérifiant manuellement qu'elles rammenaient des lignes :

La jointure avec la table Tally : 10 secondes
Le bloc PL : 4 secondes

La solution PL est donc la plus rapide, même si celle avec la table Tally s'en rapproche.

J'ai fait tourner le bench sur ma machine personnelle, avec la configuration suivante :
- Windows 7 64 bits
- SQL Server Express 2008 R2 64 bits SP1
- 8 Go de RAM
- Processeur Quad Core
Mise à part pour les disques, on s'approche donc d'une config serveur "standard".

Cependant, il n'y a eu aucun accès disque pendant tout le temps du bench.

[mikedavem]

Intéressant ...

Je vais faire le même test que vous en passant par une trace profiler .. SQL Server annonce des coûts d'exécution plus élevés pour la requête de Sergejack ... vérifions le maintenant par des benchmarks plus approfondis ce que cela donne en terme de read, write, cpu et duration. Il n'est pas dit que je me retrouve avec quelques surprises.

Je posterai mes résultats par la suite

[mikedavem]

Bon voilà,

J'ai pris comme référence le backup de Waldar.

Pour pouvoir tester un ensemble de cas probable, j'ai pris 3 tables :

- test_small (10000 lignes avec une taille de 300Ko environ)
- test (99906 lignes avec une taille de 3M0 environ)
- test_big (2057658 lignes avec une taille de 63 Mo environ)

On pourrait bien entendu pousser le test plus loin en augmentant de nouveau la taille ... ou en ajoutant des accès concurrents ...

Les requêtes utilisées :

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
DECLARE @word NVARCHAR(50);
SET @word = NEWID();
 
SELECT count(*)
FROM dbo.test_big AS T1
   INNER JOIN
   (SELECT nb, LEFT(@Word, nb) AS sWord
      FROM dbo.Tally
     WHERE nb <= len(@Word)) AS T2
     ON T2.sWord = T1.filepath
 WHERE idclub IN (0, 1);
 
SELECT COUNT(*)
FROM dbo.test_big WITH(INDEX = idx_test_big)
WHERE idclub IN (0, 1) AND LEFT(@word, len(filepath)) = filepath;
 
SELECT COUNT(*)
FROM dbo.test_big WITH(INDEX = idx_test_big)
WHERE idclub IN (0, 1) AND @word LIKE filepath + '%';
 
WAITFOR DELAY '00:00:01';
 
GO 10000

Le test est donc lancé 10000 fois pour avoir un échantillon de résultat plus que raisonnable Le tout est capturé par une trace profiler. Le script est lancé indépendemment pour chaque table (test_small, test et test_big).

Script utilisé pour récupérer et traiter les résultats :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT 
 CAST(TextData AS VARCHAR(MAX)) AS text_data,
 AVG(Reads) AS avg_reads,
 AVG(CPU) AS avg_cpu,
 AVG(Duration) AS avg_duration
FROM fn_trace_gettable('C:\Users\dbn\Desktop\test_small.trc', NULL)
WHERE TextData IS NOT NULL
GROUP BY CAST(TextData AS VARCHAR(MAX))
ORDER BY AVG(Reads) + AVG(CPU);

Voici donc les résultats obtenus (trié par coût = AVG(reads) + AVG(CPU) .. je ne prends pas en compte duration car cette métrique est discutable)

--> Table test _small

req1 (LIKE) avg_reads = 41, avg_cpu = 3 avg_duration = 3179
req2(LEFT) avg_reads = 44 avg_cpu = 2 avg_duration = 2407
req3(INNER JOIN) avg_reads = 153 avg_cpu = 0 avg_duration = 0

--> Table test

req1 (INNER JOIN) avg_reads = 229, avg_cpu = 0 avg_duration = 362
req2(LIKE) avg_reads = 388 avg_cpu = 21 avg_duration = 22183
req3(LEFT) avg_reads = 392 avg_cpu = 18 avg_duration = 18368

--> Table test_big

req1 (INNER JOIN) avg_reads = 230 avg_cpu = 0 avg_duration = 391
req2(LEFT) avg_reads = 7954 avg_cpu = 382 avg_duration = 387307
req3(LIKE) avg_reads = 7950 avg_cpu = 463 avg_duration = 475636

Ce que je peux tirer de ce résultat est que la requête de SergeJack (dans notre cas de test) n'est pas la plus performante sur les tables de faible volumétrie mais devient intéressante lorsque la volumétrie de la table augmente. Donc voilà bien un cas où le query cost est à prendre avec des pincettes.

++

[Sergejack]

Waldar, j'ai refait des tests avec la copie de votre DB et je n'obtiens toujours pas les même résultats.
Et pourtant j'aimerais ! Surtout que vous obtenez des vitesses d’exécutions bien plus rapides que les miennes.

Mais si ce n'est pas dans la configuration de la DB (puisque nos essaie se font bien sur la même), où dans la configuration de votre serveur avez vous obtenu cette magie ?

Quelle est la puissance de votre machine ? Et quels peuvent être les éléments clés de votre configuration ?

Mes qualités de DBA sont peut-être trop limités donc j'aimerais savoir si vous le voulez bien.


---

Merci mikedavem pour ces tests dont les résultats ressemblent aux miens.

À ma connaissance (et jusqu'à preuve du contraire) un plan, qu'il soit estimated plan ou excetution plan, voit ses valeurs (costs) exprimées selon des estimations.

En français, je dirais :
Pour l'estimated plan, SQL Server pense faire ceci et estime que ça coûtera cela.
Pour l'execution plan, SQL Server a fait ceci et estime que ça aura coûté cela.

[Waldar]

Oui effectivement les différences peuvent provenir du système plus que de la requête en elle-même.

Ici il s'agit d'un SQL-Server 2005 sur Windows Server 2003 R2 x64.
Ça tourne sur une machine physique, Intel Xeon 3 GHz, 6 Go de RAM, HD locaux.
C'est un serveur de développement que j'ai honteusement squatté au travail !

En tout cas le sujet est très intéressant !

[Sergejack]

Oui effectivement les différences peuvent provenir du système plus que de la requête en elle-même.

Ici il s'agit d'un SQL-Server 2005 sur Windows Server 2003 R2 x64.
Ça tourne sur une machine physique, Intel Xeon 3 GHz, 6 Go de RAM, HD locaux.
C'est un serveur de développement que j'ai honteusement squatté au travail !

En tout cas le sujet est très intéressant !

Alors là, je comprends pas.
Votre backup est restauré en tant que SQL 2008 (100). O_o
Jusqu'à preuve du contraire quand on restaure une DB 2005, elle reste 2005 même sur un 2008.

Bon... il y a quelque chose de très curieux dans cette affaire.

[Waldar]

Méa culpa, le DBA a bien migré la 2005 en 2008 la semaine dernière, j'avais oublié !
v. 10.0.2531 si c'est significatif.

[Sergejack]

Méa culpa, le DBA a bien migré la 2005 en 2008 la semaine dernière, j'avais oublié !
v. 10.0.2531 si c'est significatif.

C'est possible.
Vous avez un 2008 SP1, moi j'ai un 2008R2 SP1 (10.50.2500 pour les intimes).
Mais les tests que j'ai fait en 2005 étaient du même acabit que ceux fait en 2008R2.