Discussion :

[Christophe Charron]

Bonjour,
je commence à m'intéresser à la recherche fulltext, mais du peu que j'ai commencé à lire, je n'en vois pas l'intérêt, par rapport à mon besoin.

J'ai compris que l'on pouvait créer des index de recherche sur une table (voire une vue) du moment que l'on pointait sur une clé unique.
J'ai compris que c'était un service distinct, et non pas le moteur de base de données, qui se chargeait de la recherche.

Pour une recherche simple, comme rechercher un mot, la couleur "rouge", par exemple, dans la table des produits, dans la table des lignes de devis, dans la table des lignes de factures,
Quelle différence entre une vue d'union sur 3 "description like '%rouge'" ou sur 3 "contains(description,'rouge')" ?

D'avance, merci pour vos lumières et retours d'expérience.

[SQLpro]

J'ai compris que c'était un service distinct, et non pas le moteur de base de données, qui se chargeait de la recherche.

Non, la recherche FULL TEXT est intégrée dans le moteur depuis la version 2008 de SQL Server. Il n'y a juste que le partage des DLL des ifilters qui nécessite un service externe, ces ifilter étant utilisé pour indexer des documents électroniques stockées dans la base sous forme de BLOB en VARBINARY(max)) ou dans un FILESTREAM.

Pour une recherche simple, comme rechercher un mot, la couleur "rouge", par exemple, dans la table des produits, dans la table des lignes de devis, dans la table des lignes de factures,
Quelle différence entre une vue d'union sur 3 "description like '%rouge'" ou sur 3 "contains(description,'rouge')" ?

Dans ce cas précis, c'est juste une question de performances... La recherche FT est plus rapide qu'un LIKE. Plus la colonne sera longue et moins rapide sera le LIKE alors que la recherche FT aura le même temps de réponse quelque soit la longueur des données. Mettons que ce soit 3 fois plus rapide...
De plus vos 3 requêtes en UNION nécessiterons 3 opérations LIKE, soit 3 fois plus de temps que la recherche FT qui tente d'effectuer un seul parcours pour toutes les opérations de type CONTAINS...
Grosso modo vous irez 3 à 9 fois plus vite avec FT qu'avec un LIKE.... Mais vous ne verrez réellement la différence que quand il sera trop tard, c'est à dire quand vous aurez trop de données pour revenir en arrière et votre application sera bonne à jeter à la poubelle !

De plus le FULL TEXT permet de rechercher des formes fléchies, des synonymes. Par exemple, à l'aide des synonyme, recherchant du rouge, vous pouvez trouver du carmin, de l'écarlate, du vermillon...

Lisez l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...text_search_no

A +

[fredoche]

Bonjour

j'utilise ces index fulltext pour implémenter deux fonctionnalités de recherche sur une application de GED web :
- l'une nourrit un système d'auto-complétion associé à un champ de recherche qui est toujours présent à l'écran, et qui présente 10 propositions de complétions en fonction de ce que l'utilisateur saisit, le champ étant mis à jour à chaque nouvelle lettre saisie.
- la seconde fonctionnalité réalise la recherche en elle-même et présente un jeu de résultat

Le tout fonctionne en AJAX, c'est sur du SQL server 2008, sur des bases qui étaient déjà existantes, sur tous les champs d'une table qui répertorie les noms de fichiers et leurs attributs.

Moi je trouve qu'il y a plusieurs avantages :
- c'est rapide, très rapide, mes requêtes AJAX qui ne sont pas si simples répondent en moins de 200 ms sur un serveur plutôt daté mais bien indexé. La moyenne est plutôt aux alentours de 80-100ms.
- il y a un système de ranking intégré à ces requêtes d'index pour la pertinence des résultats que l'on peut donc utiliser pour classer les résultats à présenter : pour l'autocomplétion j'utilise le rang puis la longueur du mot proposé, pour la recherche j'utilise le rang puis la date du document
- les requêtes de recherche sont complétées de jointures pour récupérer les droits des utilisateurs sur les documents, de jointures pour restreindre les résultats à un sous-dossier, de clauses limitant la période des résultats, et le temps de réponse est constant
- je fais une union entre les résultats d'une requête CONTAINS et d'une requête FREETEXT pour ratisser large si je puis dire, parfois l'une partie ne renvoie rien tandis que l'autre si.

A la fin j'ai un système de recherche avec une interface à la façon google pour l'auto-complétion et des résultats pertinents quels que soient les mots clefs saisis, pour l'effort d'utiliser intelligemment une des fonctionnalités intégrée à SQL server. C'est puissant, super rapide, et livré en standard
Point de vue utilisateur c'est génial

Et bien sur de lire SQLpro permet de se lancer sur le sujet avec beaucoup d'aise, quand on commence à se poser ce genre de question.

[Christophe Charron]

Merci à tous les deux pour ces éclaircissements.
Des comportements exotiques à attendre, à votre avis, si le contenu de la colonne est en rtf ?
D'avance, merci.

[SQLpro]

Merci à tous les deux pour ces éclaircissements.
Des comportements exotiques à attendre, à votre avis, si le contenu de la colonne est en rtf ?
D'avance, merci.

Il suffit de la stocker dans une colonne de type VARBINARY(max) et de rajouter une colonne dans la table indiquant le type d'extension du fichier stocké.

Exemple :

La table :

Code :

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

1
2
3
4
5
CREATE TABLE dbo.T_DOC
(DOC_ID         INT IDENTITY CONSTRAINT PK_DOC PRIMARY KEY,
 DOC_NATURE     VARCHAR(8),
 DOC_DATA       VARBINARY(max));
GO

Exemple d'insertion du contenu binaire d'un fichier RTF :

Code :

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

1
2
3
4
5
INSERT INTO dbo.T_DOC
SELECT '.rtf', CAST(BulkColumn AS VARBINARY(max))
FROM   OPENROWSET(BULK 'C:\DATA\doc.rtf',
                  SINGLE_CLOB) AS T;
GO

Création du catalog FT

Code :

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

1
2
3
CREATE FULLTEXT CATALOG FT_CAT 
   WITH ACCENT_SENSITIVITY  = ON;
GO

Création de l'index FT sur le texte

Code :

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

1
2
3
4
5
6
7
8
9
CREATE FULLTEXT INDEX 
   ON dbo.T_DOC (DOC_DATA 
                 TYPE COLUMN DOC_NATURE
				 LANGUAGE 1036)
   KEY INDEX PK_DOC
ON FT_CAT
WITH (CHANGE_TRACKING = AUTO,
      STOPLIST= SYSTEM)
GO

SQL Server est capable d'indexer le contenu de plus de 50 types de fichiers différents :

Code :

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

SELECT * FROM sys.fulltext_document_types

A +

[Christophe Charron]

Merci pour cette réponse qui répond à la question que j'aurai posé, ensuite.
Je précise mieux ma question initiale : il s'agirait d'indexer une colonne de type nvarchar(max) contenant des chaines formatées en rtf.