Index + Like où sont les performance?

**Sergejack** · 20/09/2011, 17h30

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 :

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

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 :

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

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 ?

**elsuket** · 20/09/2011, 18h01

Bonjour,

Cela ne serait-il pas du au IN ?

@++

**mikedavem** · 20/09/2011, 18h25

Probablement le IN dans votre cas comme le dit Elsuket.

Postez votre plan d'exécution.

++

**Sergejack** · 20/09/2011, 18h43

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

Code :

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

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 ?

**elsuket** · 20/09/2011, 19h07

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.

@++

**Sergejack** · 21/09/2011, 09h30

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 :

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

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

**StringBuilder** · 21/09/2011, 10h13

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 :

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

1
2
 
where 'toto' like champ + '%'

Peut aussi s'écrire :

Code :

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

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.

**StringBuilder** · 21/09/2011, 10h33

Voici le résultat de mes tests :

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

**StringBuilder** · 21/09/2011, 10h51

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

**Sergejack** · 21/09/2011, 12h14

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 :

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

**Sergejack** · 21/09/2011, 12h21

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

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

**StringBuilder** · 21/09/2011, 17h06

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.

**Sergejack** · 21/09/2011, 17h23

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.

**StringBuilder** · 21/09/2011, 17h54

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.

**StringBuilder** · 21/09/2011, 18h08

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

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

**Waldar** · 21/09/2011, 19h33

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 :

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

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 :

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

**mikedavem** · 21/09/2011, 19h50

Envoyé par Sergejack

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

Code :

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

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

++

**StringBuilder** · 22/09/2011, 09h52

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.

**Sergejack** · 22/09/2011, 10h27

Envoyé par Waldar

Code :

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

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 :

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

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 ?

**Waldar** · 22/09/2011, 10h51

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

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 :

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

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.

Index + Like où sont les performance?

Développement SQL Server

Discussions similaires

Partager

Partager