Index + Like où sont les performance?

Version imprimable

Bonjour.

Je m'interroge devant certaines performance d'un de mes queries.
J'ai une table qui n'a pour seul index sa clé primaire composée d'un entier (IdClub) et d'un nvarchar(250) (FilePath).

J'aimerais retrouvé dans ma table toutes les lignes où IdClub correspond à un certain paramètre entier et où FilePath est le début (ou la totalité) d'un certain paramètre chaîne de caractères.

Cela donne quelque chose comme ceci :
Code:

1 2 3 4 5 6 SELECT * FROM dbo.tManagement_SkipFiles AS SK WHERE SK.IdClub IN (0, 1040) AND N'app[_]themesdsqfdqazzzzzzzzzzzzsfdsqf' LIKE SK.FilePath + '%'
Le problème avec cette écriture est que l'index n'est pas utilisé (sinon pour le paramètre entier).
J'aimerais évidement tirer un meilleur parti de l'index, mais je ne trouve pas d'écriture permettant de le faire.

Par exemple, ceci est un non-sens:
Code:

1 2 3 4 5 6 7 SELECT TOP(1) @Dummy = SK.NoSkip FROM dbo.tManagement_SkipFiles AS SK WHERE SK.IdClub IN (0, 1040) AND SK.FilePath LIKE SUBSTRING(N'app[_]themesdsqfdqazzzzzzzzzzzzsfdsqf', 0, LEN(SK.FilePath)) + N'%' ORDER BY SK.IdClub DESC
Avez-vous une idée ?

20/09/2011, 18h01
elsuket

Bonjour,

Cela ne serait-il pas du au IN ?

@++ ;)
20/09/2011, 18h25
mikedavem

Probablement le IN dans votre cas comme le dit Elsuket.

Postez votre plan d'exécution.

++

Non le résultat est pareil avec = même sans OR.
Code:

1 2 3 4 5 6 SELECT * FROM dbo.tManagement_SkipFiles AS SK WHERE SK.IdClub = 0 AND N'app[_]themesdsqfdqazzzzzzzzzzzzsfdsqf' LIKE SK.FilePath + '%'
Toujours lent.

Comment poster un plan d’exécution ?

20/09/2011, 19h07
elsuket

Quel est le nombre de lignes de la table et le nombre de valeurs distinctes de la colonne IdClub ?

Pour le plan d'exécution, je n'ai pas trouvé comment attacher un document à un post. Au pire, postez le document XML entier.

@++ ;)

1 pièce(s) jointe(s)

Il y a 60004 lignes dans la table (table remplie artificiellement).
Et 2 valeurs distincte pour IdClub.

Voici les plans (on y voit que l'index n'est pas exploité sur la chaîne de caractères) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 --Query1 SELECT * FROM dbo.tManagement_SkipFiles AS SK WHERE SK.IdClub = 0 AND N'app[_]themesdsqfdqazzzzzzzzzzzzsfdsqf' LIKE SK.FilePath + '%' --Query 2 SELECT * FROM dbo.tManagement_SkipFiles AS SK WHERE SK.IdClub = 0 AND SK.FilePath LIKE SUBSTRING(N'app[_]themesdsqfdqazzzzzzzzzzzzsfdsqf', 0, LEN(SK.FilePath)) + N'%'

21/09/2011, 10h13
StringBuilder
Juste comme ça, de ce que j'ai compris, vous recherchez si la valeur de votre champ est présente dans le critère de recherche.

Donc la comparaison qui utilise un LIKE :
Code:

1 2 where 'toto' like champ + '%'
Peut aussi s'écrire :
Code:

1 2 where left('toto', length(champ)) = champ
Avez-vous tenté cette écriture ?
Plus intéressant, si par heureux ahsard votre champ fait toujours la même taille, vous pouvez tronquer 'toto' à la source, avant de l'envoyer à votre requête, et ainsi transformer votre like ou left en égalité stricte.

Voici le résultat de mes tests :
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 drop table test; create table test ( idclub int not null, filepath nvarchar(250) not null ); alter table test add constraint pk_test primary key (idclub, filepath); declare @nb int; set @nb = 0; begin transaction; while @nb < 100000 begin insert into test (idclub, filepath) values (CONVERT(INT, (2)*RAND()), left(NEWID(), 5 + CONVERT(INT, (5)*RAND()))); set @nb = @nb + 1; end; commit; select COUNT(*) from test; => Plus de 98000 lignes select * from test order by filepath select * from test where idclub in (0, 1) and left(N'0045228D123', len(filepath)) = filepath; select * from test where idclub in (0, 1) and N'0045228D123' like filepath + '%';
(Il y a des erreurs d'insertion, c'est normal, je ne vérifie pas l'unicité lors de l'insertion dans la table)

Le premier SELECT fait un "clustered index seek", et le second un "clustered index scan".

Le premier (celui que je propose donc) semble plus rapide. Cependant, je n'arrive pas à lire précisément les différences entre les deux plans d'exécuction...

En tout cas, je ne m'explique pas un truc : au boulot, j'ai une brouette qui met à peut près 20 secondes à ouvrir le bloc note... et pourtant les deux requêtes sont absolument instantannées. Je ne comprends pas votre contre-performance avec seulement 64 000 lignes ! Même si vos filepath sont plus longs et ont plus de caractères que les miens, ça me semble quand même très étrange...

21/09/2011, 10h51
StringBuilder

je viens de refaire le test avec 1 000 000 lignes, et sur ma brouette, c'est toujours aussi rapide (moins de 1 seconde pour retrouver 0 ou n lignes, quelle que soit la méthode utilisée).

Tu peux poster le script de ton protocole de test ?

-- Edit : Et je confirme que ma syntaxe est plus performance (enfin... presque) : sur un lot avec les deux requêtes, ma syntaxe consomme 48% du lot, et la vôtre 52%.

Ce query doit être exécuté plusieurs milliers de fois de façon consécutive.
À l'unité c'est trois fois rien, certe mais par batch de 1000, le problème de performance devient conséquent.

Batch de 100:
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 DECLARE @D DATETIME DECLARE @i INT DECLARE @i1 INT DECLARE @i2 INT DECLARE @Dummy BIT SET @i = 0 SET @i1 = 0 SET @i2 = 0 WHILE @i < 100 BEGIN SET @D = GETDATE() SELECT TOP(1) @Dummy = 1 FROM test WHERE idclub IN (0, 1) AND LEFT(N'0045228D123', len(filepath)) = filepath ORDER BY IdClub DESC SET @i1 = @i1 + DATEDIFF(mcs, @D, GETDATE()) SET @D = GETDATE() SELECT TOP(1) @Dummy = 1 FROM test WHERE idclub IN (0, 1) AND N'0045228D123' LIKE filepath + '%' SET @i2 = @i2 + DATEDIFF(mcs, @D, GETDATE()) SET @i = @i + 1 END SELECT @i1, @i2, @i1 / @i2
Je vais posté un query qui tire parti de l'index mais vous verrez que sa complexité laisse perplexe et invite à espérer qu'il existe une autre écriture.

Voici le query qui fait ce que je souhaite qui soit fait :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT TOP(1) @Dummy = SK.NoSkip FROM dbo.tManagement_SkipFiles AS SK INNER JOIN ( SELECT N.n , SUBSTRING(Line, 0, N.n) FROM ( SELECT N'app_themesdsqfdqazzzzzzzzzzzzsfdsqf' ) AS X(Line) CROSS JOIN Utils.dbo.tTally AS N WHERE N.n BETWEEN 1 AND LEN(X.Line) ) AS N(n, Line) ON ( N.Line = SK.FilePath ) WHERE SK.IdClub IN (0, 1040) ORDER BY N.n DESC, SK.IdClub DESC
Et là, ça va +/- 150 fois plus rapidement car l'index est intelligemment utilisé.

Comme je le disais, la complexité de ce query doit choquer (qui requiert une table numérique).

Alors, si moi je peux tirer parti de l'index avec un query aussi tiré par les cheveux que ça, il existe peut-être une méthode plus concise prévue par M$ ou découverte par les utilisateurs.

Merci à ceux qui pourraient me mettre sur une piste.

21/09/2011, 17h06
StringBuilder

Je constate que dans la query "tirée par les cheveux", il n'y a plus de like.

C'est conforme à mon analyse initiale : étrange de faire un LIKE d'un champ dans une constante.

Avez-vous testé ma suggestion ?
J'ai bien l'impression que ça fait à peu de chose près la même chose.

Et le plan d'exécution n'étant pas le même chez moi (d'un index scan, on passe à un index seek, soit une réelle utilisation de l'index) il se pourrait bien que votre solution réside dans ma requête.
21/09/2011, 17h23
Sergejack

Citation:

Envoyé par StringBuilder

Je constate que dans la query "tirée par les cheveux", il n'y a plus de like.

C'est conforme à mon analyse initiale : étrange de faire un LIKE d'un champ dans une constante.

Avez-vous testé ma suggestion ?
J'ai bien l'impression que ça fait à peu de chose près la même chose.

Et le plan d'exécution n'étant pas le même chez moi (d'un index scan, on passe à un index seek, soit une réelle utilisation de l'index) il se pourrait bien que votre solution réside dans ma requête.

Oui, j'ai testé votre solution (qui est synonyme à des tests que j'avais déjà moi-même faits), d'ailleurs si vous lisez le query que j'ai posté à 12h21, ce sont vos tables que j'ai reprises afin qu'on parte bien d'une même base.
Je serai ravis que vous le fassiez tourner sur votre machine et nous faire part de vos résultats afin d'avoir le cœur net (sait on jamais) des choses qui ont été discutée.
21/09/2011, 17h54
StringBuilder

Je viens de faire tourner le bench des 100 itérations.
Chez moi ça dure 9 secondes, et ma requpête (après suppression du ORDER BY, pour la mettre sur un pied d'égalité avec la vôte) dure environ 30% moins longtemps (3701000 µs contre 5138000 µs).

En revanche, je ne peux pas tester votre requête qui va plus vite : vous utilisez une table et des fonctions dont je n'ai pas connaissance.

En reprenant le concept de ta super requête, je vais... "infiniment" plus vite en utilisant un petit bloc de T-SQL :

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
74
75
76
 
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
 
SET @i = 0
SET @j = 0
SET @i1 = 0
SET @i2 = 0
SET @i3 = 0
SET @i4 = 0
set @Dummy1 = 0
set @Dummy2 = 0
set @Dummy3 = 0
set @Dummy4 = 0
 
WHILE @i < 100
BEGIN
	SET @D = GETDATE()
 
	SELECT @Dummy1 = COUNT(*)
	FROM test
	WHERE idclub IN (0, 1) AND LEFT(N'4360218F-123', len(filepath)) = filepath
 
	SET @i1 = @i1 + DATEDIFF(mcs, @D, GETDATE())
 
	SET @D = GETDATE()
 
	SELECT @Dummy2 = COUNT(*)
	FROM test
	WHERE idclub IN (0, 1) AND N'4360218F-123' LIKE filepath + '%'
 
	SET @i2 = @i2 + DATEDIFF(mcs, @D, GETDATE())
 
	SET @D = GETDATE()
 
	SELECT @Dummy3 = COUNT(*)
	FROM test
	WHERE idclub IN (0, 1) AND N'4360218F-' = filepath
 
	SET @i3 = @i3 + DATEDIFF(mcs, @D, GETDATE())
 
	SET @D = GETDATE()
 	set @j = LEN(N'4360218F-123')
 
	while @j > 0
	begin
 
		SELECT @Dummy4 = COUNT(*)
		FROM test
		WHERE idclub IN (0, 1) AND left(N'4360218F-123', @j) = filepath
 
		if @Dummy4 = 1
		begin
			break
		end
 
		set @j = @j - 1
	end
 
	SET @i4 = @i4 + DATEDIFF(mcs, @D, GETDATE())
 
	SET @i = @i + 1
END
 
SELECT @i1, @i2, @i3, @i4;
 
SELECT @Dummy1, @Dummy2, @Dummy3, @Dummy4;

La troisème requête recherche sur l'égalité stricte : instantanné (0 µs en cumulé)

La quatrième requête (T-SQL) recherche sur l'égalité stricte pour toutes les sous-chaines en partant de la chaine originale, puis en enlevant un caractère jusqu'à ce qu'on trouve une ligne) : instantanné (0 µs en cumulé)

En tout cas, je ne comprends pas comment SQL Server fait sont like... Parceque mon T-SQL fait rigoureusement ce que fait un LIKE !

J'ai implémenté votre algorithme astucieux SergeJack, mais sur ma machine le like a des performances comparables légèrement plus rapide !

Il s'agit d'un serveur qui tourne sur SQL-Server 2005.

En repartant de la table test de StringBuilder, je rajoute la table Tally que je limite à 250 entrées pour ce sujet :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
create table dbo.tally (nb integer primary key clustered)
 
;With Cpt (nb) as
(
select 1
 union all
select nb + 1
  from Cpt
 where nb < 250
)
insert into dbo.Tally (nb)
select nb from Cpt
option (maxrecursion 250)

Je laisse de côté le premier test qui est lui beaucoup plus lent (500 fois plus lent sur mon environnement) :

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
DECLARE @D DATETIME
DECLARE @i INT
DECLARE @j INT
DECLARE @i1 INT
DECLARE @i2 INT
DECLARE @i3 INT
DECLARE @Dummy BIT
declare @Word nvarchar(250)
 
set @Word = N'00759049-lmsdfjhklmjgfhjf'
SET @j = 0
 
WHILE @j < 5
BEGIN
 
SET @j = @j + 1
SET @i = 0
SET @i1 = 0
SET @i2 = 0
SET @i3 = 0
 
WHILE @i < 1000
BEGIN
    /*SET @D = GETDATE()
 
    SELECT TOP(1)
        @Dummy = 1
    FROM dbo.test
    WHERE idclub IN (0, 1) AND LEFT(@Word, len(filepath)) = filepath
    ORDER BY IdClub DESC
 
    SET @i1 = @i1 + DATEDIFF(mcs, @D, GETDATE())*/
 
    SET @D = GETDATE()
 
    SELECT TOP(1)
        @Dummy = 1
    FROM dbo.test
    WHERE idclub IN (0, 1) AND @Word LIKE filepath + '%'
 
    SET @i2 = @i2 + DATEDIFF(mcs, @D, GETDATE())
 
    SET @D = GETDATE()
 
    select TOP(1) @Dummy = 1
      from test 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)
 
    SET @i3 = @i3 + DATEDIFF(mcs, @D, GETDATE())
 
    SET @i = @i + 1
 
END
 
SELECT 'Exec ' + cast(@j as varchar), @i1, @i2, @i3
 
END
 
 
Exec 1                              0           75000       78000
Exec 2                              0           48000       54000
Exec 3                              0           57000       48000
Exec 4                              0           45000       54000
Exec 5                              0           33000       66000

21/09/2011, 19h50
mikedavem
Citation:
Envoyé par Sergejack

Non le résultat est pareil avec = même sans OR.

Code:

1 2 3 4 5 6 SELECT * FROM dbo.tManagement_SkipFiles AS SK WHERE SK.IdClub = 0 AND N'app[_]themesdsqfdqazzzzzzzzzzzzsfdsqf' LIKE SK.FilePath + '%'

Toujours lent.

Comment poster un plan d’exécution ?
Vos plans d'exécutions indiquent pourtant que votre index est bien utilisé ou je n'ai pas les bons...

++
22/09/2011, 09h52
StringBuilder

Waldar > Refaites votre bench en réactivant la première requête, et en virant le "order by". Vous verrez qu'elle est bien plus rapide que les autres requêtes. ce qui plopmbe tout, c'est le order by, je me demande bien pour quoi d'ailleurs. N'étant pas présent sur les autres requête, il faut l'enlever pour que le test soit significatif.

Citation:
Envoyé par Waldar
Code:

1 2 3 4 5 Exec 1 0 75000 78000 Exec 2 0 48000 54000 Exec 3 0 57000 48000 Exec 4 0 45000 54000 Exec 5 0 33000 66000
J'avais et j'ai refait les tests sur des instances 2K5 et 2K8, en poussant même le vice à reconstruire de temps en temps les indexes et à redémarrer le service SQL (ça remet la RAM "à neuf").
Mais jamais je n'ai obtenu des résultats allant dans ce sens.

Par exemple, mes 2 derniers runs :
Code:

1 2 3 4 Exec 1 0 21235000 135000 Exec 2 0 21279000 102000 ...
Est-ce que votre table de test est bien populée ?
Moi j'ai 99918 lignes.

-

Dans tous mes tests :
l'index est utilisé, mais uniquement pour la correspondance du IdClub.
Son utilisation est donc partielle et insuffisante.

Si vous obtenez autre chose, est-ce que vous pourriez postez vos excecution plans ?

22/09/2011, 10h51
Waldar

1 pièce(s) jointe(s)
Citation:

Envoyé par StringBuilder

Waldar > Refaites votre bench en réactivant la première requête, et en virant le "order by". Vous verrez qu'elle est bien plus rapide que les autres requêtes.

Oui c'est vrai, je n'avais pas fait attention, elle a des performances en ligne voire légèrement meilleur qu'avec les autres syntaxes.
Sur 5 * 1000 exécutions :
```
Exec 1                              42000       66000       39000
Exec 2                              21000       48000       42000
Exec 3                              33000       39000       39000
Exec 4                              36000       60000       12000
Exec 5                              27000       66000       18000
```
Citation:

Envoyé par Sergejack

Est-ce que votre table de test est bien populée ?
Moi j'ai 99918 lignes.

Oui, j'ai la même volumétrie :
Code:

1 2 3 4 5 6 select count(*) as nb_lignes from dbo.test nb_lignes ----------- 99917
Au niveau des plans d'exécution, sur la requête avec le left j'ai un index seek, la requête avec le like un index scan, la requête qui décompose le mot entrant parallelisme + index seek.

Je les joins à ce message.
22/09/2011, 13h41
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:

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.
22/09/2011, 14h18
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.
22/09/2011, 14h19
iberserk

Sinon de telles requêtes ne cachent'elle pas une modélisation à revoir?
22/09/2011, 16h16
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 ;) )

Citation:

(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)

++
23/09/2011, 15h15
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 ?
23/09/2011, 16h13
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 !

Ç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:

1 2 3 4 select idclub, COUNT(*) from test group by idclub
0 4999032
1 5000968
Code:

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:

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.

24/09/2011, 09h25
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

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:

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:

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)

Citation:

--> 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

Citation:

--> 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

Citation:

--> 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.

++

26/09/2011, 09h38
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.
26/09/2011, 11h03
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 !
26/09/2011, 13h07
Sergejack

Citation:

Envoyé par 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 !

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.
26/09/2011, 15h09
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.
26/09/2011, 16h08
Sergejack

Citation:

Envoyé par 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.

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.