Discussion :

[fredoche]

Bonjour

Je cherche à récupérer un maximum de résultat à la suite d'une saisie de mots-clés. Je vais remonter des résultats au fur et à mesure de la saisie avec AJAX pour alimenter un système d'autocomplète ou de suggestion de saisie, un peu à la façon du moteur de recherche de Google.
Pour pouvoir bénéficier à la fois de la recherche par préfixe (wildcard *), et d'une recherche freetext qui ne prend pas ne compte les préfixes mais qui peut tenir compte de résultats incluant les mots clés quelles que soient leurs positions, j'ai envie d'écrire une requête qui unit les résultats de ces 2 outils sur une table qui est indexée en plein texte :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT CTT.RANK as rang, MF1.*
  FROM [MEDIAFILES] MF1 inner join
  ContainsTABLE([MEDIAFILES],*, '"visuel novembre*"') AS CTT
  on CTT.[KEY] = MF1.record_id
 
  union
  SELECT FTT.RANK as rang, MF2.*
  FROM [MEDIAFILES] MF2 inner join
  freetextTABLE([MEDIAFILES],*, 'visuel novembre') AS FTT
  on FTT.[KEY] = MF2.record_id
  order by rang desc
GO

On peut imaginer que les mots clés affichés ici sont ceux finalement saisis mais qu'en lieu et place on aurait "vis*" puis "visu*" puis "visue*" puis "visuel*" puis "visuel n*" et ainsi de suite au fur et à mesure de la saisie et des requêtes AJAX
N'ayant pas vraiment de recul sur la recherche plein texte, je ne suis guère capable de me critiquer sur cette solution, sachant qu'elle me parait séduisante en l'état.

Ma question s'il en est : Est-ce idiot de faire comme ça ? d'autres pistes seraient-elles à envisager ?

[elsuket]

Bonjour,

Il est très probable que vous rencontriez vite des problèmes de performance si l'application exécute un appel en base de données pour chaque lettre supplémentaire, même si c'est après les 3 premiers caractères saisis.
Peut-être serait-il plus adapté d'implémenter un index rotatif ?
Dès lors vous pouvez probablement maintenir les éléments d'une tel index dans un cache, et ainsi minimiser les appels en base de données.

@++

[fredoche]

Bonjour

Concernant la performance on parle d'une table (MEDIAFILES) de 27 000 lignes qui grossit en gros d'une dizaine de lignes par jour ouvré, une cinquantaine par semaine.
En l'état le catalogue plein texte pèse 2 Mo pour 27000 éléments et 16683 clés uniques.

Et l'opération , la requête en soi concerne essentiellement à de la recherche sur des index, soit de PK, soit de plein texte.

Quand j'affiche le plan d'exécution réel, ce sont les jointures et la recherche sur les index PK (clustered index scan) de mediafiles qui représentent en gros 95 % du plan. La recherche plein texte et le tri pour la suppression des doublons fait le reste (5% ou moins).
Donc pour mon cas précis, je me demande si la question de la performance se pose vraiment ? Après mon application et les bases de données SQL server supportent plutôt bien ces appels AJAX multiples. C'est même très performant à mon sens.


Quand Frédéric Brouard avait publié cet article sur les index rotatifs, j'avais justement évoqué mon fort intérêt pour la chose sur le sujet qui l’accompagnait dans ce forum.
Par contre ça me semble moins souple, et moins puissant que ces fonctions de recherche plein texte, avec un coté moins 'sorti de la boite', donc éventuellement plus de maintenance

[elsuket]

Bonjour,

2Mo pour 27000 lignes, ça me paraît un peu en dessous de ce que j'ai pu observer. Est-ce que vous obtenez la taille de l'index fulltext à l'aide de la vue sys.fulltext_index_fragments ?

Quand j'affiche le plan d'exécution réel, ce sont les jointures et la recherche sur les index PK (clustered index scan) de mediafiles qui représentent en gros 95 % du plan. La recherche plein texte et le tri pour la suppression des doublons fait le reste (5% ou moins).

Dans ce cas il faut peut-être modifier l'expression de la requête, car elle ne devrait à priori pas produire de lecture complète de la table : le fameux clustered index scan.
Il faudrait faire en sorte que le moteur extraie d'abord les valeurs de la clé primaire qui correspondent à la recherche full-text pour ensuite réaliser un clustered index seek par exemple, beaucoup plus efficace. J'ai observé plusieurs fois ce problème. Voyez aussi ce que donne la sortie des options de session SET STATISTICS IO, TIME ON placés juste avant la requête, dans l'onglet Messages de SQL Server Management Studio.

Par contre ça me semble moins souple, et moins puissant que ces fonctions de recherche plein texte, avec un coté moins 'sorti de la boite', donc éventuellement plus de maintenance

Comme bien souvent, la réponse est : "ça dépend" . Si ce que l'on cherche à réaliser est une recherche textuelle avec un joker devant et derrière la chaîne, l'index full-text ne le permet pas.
Par ailleurs, nous savons que les prédicats LIKE qui ont un joker en début de chaîne ne sont pas sargables, donc sous-performants.
L'index rotatif permet d'éviter ces écueils en utilisant un filtre soit d'égalité, soit un prédicat de filtre LIKE dont le joker est systématiquement en fin de chaîne.

Je vous accorde que ce n'est pas sorti de la boîte, mais ça a le mérite d'être performant et de couvrir un cas d'utilisation que les moteurs de recherche ont popularisé au point que c'en est tout à fait normal.
Si bien que sur des volumes importants (plusieurs millions de lignes sur plusieurs colonnes), les requêtes spécifiant de la recherche full-text ne sont pas toujours très performantes.
En ce qui concerne la maintenance, rien ne nous empêche de recourir, suivants vos besoins et contraintes, à un déclencheur, une vue indexée, un job de l'Agent SQL Server ou encore Service Broker.
Pour ma part, je suis en train d'étudier l'indexation rotative de plusieurs colonnes à l'aide de Service Broker pour profiter d'un traitement asynchrone de l'indexation ...

@++

[fredoche]

Bonjour,

2Mo pour 27000 lignes, ça me paraît un peu en dessous de ce que j'ai pu observer. Est-ce que vous obtenez la taille de l'index fulltext à l'aide de la vue sys.fulltext_index_fragments ?

non
et bizarrement cette vue comme toutes les vues adjacentes (nommées sys.fulltext) sont vides
Non je prends ces valeurs depuis les propriétés du catalogue d'indexation que j'ai créé. Mais comme c'est le seul index qu'il contient...

Dans ce cas il faut peut-être modifier l'expression de la requête, car elle ne devrait à priori pas produire de lecture complète de la table : le fameux clustered index scan.
Il faudrait faire en sorte que le moteur extraie d'abord les valeurs de la clé primaire qui correspondent à la recherche full-text pour ensuite réaliser un clustered index seek par exemple, beaucoup plus efficace. J'ai observé plusieurs fois ce problème. Voyez aussi ce que donne la sortie des options de session SET STATISTICS IO, TIME ON placés juste avant la requête, dans l'onglet Messages de SQL Server Management Studio.

là je ne comprends pas tout je crois... enfin surtout sur ce qu'il faudrait faire
Le clustered index scan correspond à la réalisation de la jointure entre les clés issues de la recherche plein texte et l'index sur la clé primaire de la table : la recherche porte sur PK_MEDIAFILES MF1 ou MF2.
Visiblement c'est parfois du 'seek' parfois du 'scan' selon les mots clés que je teste et sans que je sache vraiment ce qui guide ce choix pour l'optimiseur.
exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SET STATISTICS IO, TIME ON 
 
SELECT CTT.RANK as rang, MF1.*
  FROM [MEDIAFILES] MF1 inner join
  ContainsTABLE([MEDIAFILES],*, '"visuel oct*"') AS CTT
  on CTT.[KEY] = MF1.record_id
 
  union
  SELECT FTT.RANK as rang, MF2.*
  FROM [MEDIAFILES] MF2 inner join
  freetextTABLE([MEDIAFILES],*, 'visuel oct') AS FTT
  on FTT.[KEY] = MF2.record_id
  order by rang desc

utilise du cluster index seek pour MF1 et 2, retourne 351 résultats
et les messages stats sont celles-ci :

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
Temps d'analyse et de compilation de SQL Server : 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 0*ms, temps écoulé = 0*ms.
Temps d'analyse et de compilation de SQL Server : 
, Temps UC = 15*ms, temps écoulé = 18*ms.
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
(351*ligne(s) affectée(s))
Table 'MEDIAFILES'. Nombre d'analyses 0, lectures logiques 1083, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
 
(1*ligne(s) affectée(s))
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 16*ms, temps écoulé = 268*ms.
Temps d'analyse et de compilation de SQL Server : 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 0*ms, temps écoulé = 0*ms.

la recherche sur 2 mots cles complets ("visuel octobre") utilise encore seek pour les 2 tables et retourne 407 lignes

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
Temps d'analyse et de compilation de SQL Server : 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 0*ms, temps écoulé = 0*ms.
Temps d'analyse et de compilation de SQL Server : 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
(407*ligne(s) affectée(s))
Table 'MEDIAFILES'. Nombre d'analyses 0, lectures logiques 1255, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
 
(1*ligne(s) affectée(s))
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 16*ms, temps écoulé = 245*ms.
Temps d'analyse et de compilation de SQL Server : 
, Temps UC = 0*ms, temps écoulé = 0*ms.
 
 SQL Server \endash Temps d'exécution*: 
, Temps UC = 0*ms, temps écoulé = 0*ms.

puis la recherche sur 2 mots cles complets ("visuel juin") utilise seek pour MF1 et scan pour MF2 et retourne 807 lignes, avec Temps UC = 47*ms, temps écoulé = 263*ms.
puis la recherche sur 2 mots cles complets ("affiche juin") utilise scan pour MF1 et MF2 et retourne 3027 lignes, avec Temps UC = 78*ms, temps écoulé = 383*ms. Mais ces valeurs sont très variables sur une série de 5 exécutions
visiblement la bascule de seek à scan semble lié au nombre de résultats retourné par l'index plein-texte

Comme bien souvent, la réponse est : "ça dépend" . Si ce que l'on cherche à réaliser est une recherche textuelle avec un joker devant et derrière la chaîne, l'index full-text ne le permet pas.
Par ailleurs, nous savons que les prédicats LIKE qui ont un joker en début de chaîne ne sont pas sargables, donc sous-performants.
L'index rotatif permet d'éviter ces écueils en utilisant un filtre soit d'égalité, soit un prédicat de filtre LIKE dont le joker est systématiquement en fin de chaîne.

Je vous accorde que ce n'est pas sorti de la boîte, mais ça a le mérite d'être performant et de couvrir un cas d'utilisation que les moteurs de recherche ont popularisé au point que c'en est tout à fait normal.
Si bien que sur des volumes importants (plusieurs millions de lignes sur plusieurs colonnes), les requêtes spécifiant de la recherche full-text ne sont pas toujours très performantes.
En ce qui concerne la maintenance, rien ne nous empêche de recourir, suivants vos besoins et contraintes, à un déclencheur, une vue indexée, un job de l'Agent SQL Server ou encore Service Broker.
Pour ma part, je suis en train d'étudier l'indexation rotative de plusieurs colonnes à l'aide de Service Broker pour profiter d'un traitement asynchrone de l'indexation ...

@++

Je sais pas trop si un joker avant le mot clé est très pertinent pour moi. Cela étant je peux toujours essayer et comparer les 2 solutions. Sauf que je ne suis pas sur d'avoir trop le temps. Je peux implémenter la première et ensuite faire mes comparaisons et passer sur la 2e en fonction du retour utilisateur sur la 1ère et de ce que je peux tirer de la 2e.
La 2e solution avec les index rotatifs m'obligent à découper la chaine saisie pour cumuler les "SUPERLIKE" et ne saura pas faire la distinction sur les "mots vides" ni faire usage des synonymes, etc. Pour une saisie un peu langage naturel, c'est pas génial je crois...

[fredoche]

non
et bizarrement cette vue comme toutes les vues adjacentes (nommées sys.fulltext) sont vides
Non je prends ces valeurs depuis les propriétés du catalogue d'indexation que j'ai créé. Mais comme c'est le seul index qu'il contient...

correction sur ce point :
datasize 1875670
row_count 16931

Je cherchais cette vue dans la database Master, au lieu de la chercher dans la base concernée.