Full text : what is it ?

Vous avez tous utilisé un moteur de recherche Web tel Yahoo! ou Google qui vous permet de chercher des pages contenant un ou plusieurs mots :
'vacances en Grèce'
devrait vous renvoyer vers des agences de voyage...
Il est bien sûr possible de faire ce genre de requête sur une table en relationnel :


    create table SEJOUR(NO_SEJOUR int,.. DESCRIPTIF varchar(max))...

    select NO_SEJOUR from SEJOUR where DESCRIPTIF like '%vacances%en%Grèce%'

Maintenant, ce genre de requête pose plusieurs problèmes :

. les % sont obligatoires car on ne maitrise pas le fait que des mots ne s'intercalent pas entre les autres
. hélas , l'ordre est imposé et si un séjour contient 'Gréce des vacances merveilleuses' il ne sera pas trouvé
. enfin ceci pose des gros problèmes de performances comme nous allons le voir

Tous ces problèmes sont réglés par les index 'full text' ou de texte intégral en Français.

Comment cela fonctionne-t-il ?

Le principe est de faire correspondre des mots à des identifiants uniques (typiquement des clés primaires)
Cela donne des couples du style
'Vacances' 123
'Vacances' 456
....
qui permettent d'associer à chaque mot la ou les PK associées.

Bien sûr, dans toute langue des mots parasites existent (en terme de sémantique) et il serait inutile voir nuisible de les indexer : c'est le cas de EN dans vacances EN Grèce; ce mot n'apporte pas de sémantique particulière à la question.
Ce point est réglé par des listes de mots dits de 'bruit' (noise en Anglais).

Passons à la pratique :

Pour créer un index full text il nous faut un catalogue de texte intégral : il s'agit en fait d'un fichier qui recevra les valeurs des couples (mot,clé)
Créons ce catalogue :

    create FULLTEXT CATALOG CatalogueConseilNet AS DEFAULT; -- sera créé à l'emplacement par défaut des bases


Dans SQLServer2005, rien n'est autorisé par défaut et vous pouvez obtenir ce genre d'erreur :

Msg 7616, Niveau 16, État 100, Ligne 1
Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database. The functionality to disable and enable full-text search for a database is deprecated. Please change your application.

que l'on corrige aisément par :

    -- l'option fulltext doit être activée pour la base

    sp_fulltext_database enable

Maintenant, les emplacements par défaut style c:\program files... ne sont pas ma tasse de thé, aussi je préférerai écrire :

    create FULLTEXT CATALOG CatalogueConseilNet in path 'N:\Bases\MSSQL\Data\FTData' AS DEFAULT; -- sera créé à l'emplacement spécifié

Le AS DEFAULT signalant à SQLServer que tous les index full text créés par la suite iront par défaut dans ce catalogue.

On peut voir l'effet de cette création par :


    select * from sys.master_files

On voit que le nom de fichier associé a été :
sysft_CatalogueConseilNet

Maintenant, un des paramétres important de ce type d'index est la langue (j'ai parlé plus haut du bruit) il faut donc vérifier son paramétrage par :

    sp_configure 'default full-text language'

    -- Les codes sont donnés par

    select * from sys.fulltext_languages order by Lcid

Pour passer en Français :
    sp_configure 'default full-text language', 1036

L'option de configuration 'default full-text language' est passée de 1036 à 1036. Pour installer, exécutez l'instruction RECONFIGURE.

    reconfigure

    sp_configure 'default full-text language'

L'interface de SQLServer Management Studio nous permet de connaitre les caractéristiques de ce catalogue :

On peut maintenant créer des index fulltext (un seul par table)
par exemple :

    create FULLTEXT INDEX ON LIGNE_ARTICLE(LIBELLE)

    key INDEX PK_LIGNE_ARTICLE;

    go

va créer un index sur les libellés des articles tel que celui que vous lisez en ce moment.
Passons à l'utilisation de cet index :

    -- Recherche classique sans full text

    select NO_LIGNE_ARTICLE from LIGNE_ARTICLE where LIBELLE like '%INDEXPROPERTY%'

    -- Recherche avec Full text

    select NO_LIGNE_ARTICLE from LIGNE_ARTICLE where CONTAINS (LIBELLE,'INDEXPROPERTY')

Bien sûr, dans ce cas simple, les résultats sont les mêmes, mais regardons ce que cela donne en terme de performances :

    set statistics io on

    go

    select NO_LIGNE_ARTICLE from LIGNE_ARTICLE where LIBELLE like '%INDEXPROPERTY%'

    go

Table 'LIGNE_ARTICLE'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    select NO_LIGNE_ARTICLE from LIGNE_ARTICLE where CONTAINS (LIBELLE,'INDEXPROPERTY')

Table 'LIGNE_ARTICLE'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

On est passé de 67 pages lues à 14 pages lues : c'est donc un facteur de 5 ; déjà pas si mal !!
Pensez que si votre serveur fait 5 fois moins d'entrée sorties, vous aurez des temps de réponse trés certainement 5 fois meilleurs si ce n'est plus ....

Gains avec du volume

Afin de mieux évaluer les performances, je repasse sur une table plus volumineuse : mes coureurs à pied qui sont 170000.
Je mets en œuvre un index intégral sur la ville :

    use Digitime

    go

    set statistics io off

    go

    sp_fulltext_database enable

    go

    create FULLTEXT CATALOG CatalogueDigitime in path 'N:\Bases\MSSQL\Data\FTData' AS DEFAULT; -- sera créé à l'emplacement spécifié

    create FULLTEXT INDEX ON SYSADM.COUREUR(VILLE)

    key INDEX PK_COUREUR;

    go

Une recherche classique :

    select NOM,PRENOM from SYSADM.COUREUR

    where VILLE like '%ALIERMONT%'

Table 'COUREUR'. Scan count 1, logical reads 3942, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

et une recherche par texte intégral :

    select NOM,PRENOM from SYSADM.COUREUR

    where contains (VILLE,'"ALIERMONT"')

Table 'COUREUR'. Scan count 0, logical reads 243, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

On a donc divisé par plus de 10 les entrés sorties disque....

Maintenant, ce facteur sera d'autant plus élevé que le volume de la table est important et que le nombre de lignes restituées est faible.
Voyons une requête qui retourne une ou deux lignes :

    select NOM,PRENOM,VILLE from SYSADM.COUREUR

    where VILLE LIKE '%SAINT%JACQUES%ALIERMONT%'

NOM PRENOM VILLE
---------------------------------------- ---------------------------------------- --------------------------------------------------------------------------------
XXXXX Patrice SAINT JACQUES D ALIERMONT
XXXXXXX Dominique SAINT JACQUES D'ALIERMONT

(2 ligne(s) affectée(s))

Table 'COUREUR'. Scan count 1, logical reads 3942, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    select NOM,PRENOM from SYSADM.COUREUR

    where contains (VILLE,'"SAint JACQUES D''ALIERMONT"')

Table 'COUREUR'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ouh là ! On a divisé par 1314 les entrées sorties disque !!
Ceux qui ont des millions de lignes à gérer dans leur table apprécieront l'écart...

Maintenant, en y regardant bien, on constate des réponses différentes dans les deux modes : cela vient de l'écriture D'ALIERMONT dans un cas et D ALIERMONT dans l'autre.
Une clause NEAR va corriger tout cela :

    select NOM,PRENOM from SYSADM.COUREUR

    where contains (VILLE,'SAint NEAR JACQUES NEAR ALIERMONT')

NOM PRENOM
---------------------------------------- ----------------------------------------
XXXXX Patrice
XXXXXXX Dominique

(2 ligne(s) affectée(s))

Table 'COUREUR'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Et tout rentre dans l'ordre, nous avons bien nos deux coureurs; le gain n'est 'que de 657 fois'....

Remplissage en différé :

Dans cette version 2005, le remplissage est par défaut en temps réel, c'est à dire sur chaque INSERT,UPDATE,DELETE.
Maintenant il se peut que, pour des raisons particulières (import de masse par exemple) cela ne soit pas souhaitable.
Voici comment faire :


    -- On désactive le momde de remplisage automatique

    alter FULLTEXT INDEX ON SYSADM.COUREUR set CHANGE_TRACKING MANUAL

    go

    -- Et ensuite on remplit à la demande

    alter FULLTEXT INDEX ON SYSADM.COUREUR START FULL POPULATION

    go

Conclusion

Voilà un bel outil qui devrait satisfaire tous ceux qui ont du volume à gérer avec des recherches par mots.

C'est le cas de tous ceux qui développent des applications Web.

Il faut d'ailleurs que je vous donne une recherche par mots clés dans mes articles.... Cette recherche utilisera bien évidemment l'index full text que je viens de créer précédemment.

De grosses améliorations ont donc été apportées par rapport à la version SQL2000.

Le 12/2/2007 :
voilà c'est fait, vous pouvez faire des recherches dans les articles par indexation full text !