Sql serve Recherche en text intégral avec Containstable est très lent lorsqu'il est utilisé dans JOIN!

J'ai passé beaucoup de time sur cette question, et basé sur l'exécution de nombreux scénarios, voici ce que j'ai compris:

Si vous avez Contains ou ContainsTable n'importe où dans votre requête, c'est la partie qui est exécutée en premier et de façon plutôt indépendante. Cela signifie que même si le rest des conditions limite votre search à un seul logging, ni Contient, ni containstable ne se soucie de cela. C'est comme une exécution parallèle.

Maintenant que la search fulltext renvoie uniquement un champ Key, il search immédiatement la key en tant que premier champ des autres index choisis pour la requête. Donc, pour l'exemple ci-dessus, il search l'index avec [key], champ1, champ2. Le problème est qu'il choisit un index pour le rest de la requête en fonction des champs de la clause where. donc pour l'exemple ci-dessus, il sélectionne l'index couvert que j'ai, qui est quelque chose comme field1, field2, Id. (Id de la table est la même que la [Key] returnné de la search en text intégral). Donc résumé est:

1. exécute containstable
2. exécute le rest de la requête et sélectionne un index basé sur la clause where de la requête
3. Il essaie de merge ces deux. Par conséquent, si l'index qu'il a sélectionné pour le rest de la requête commence par le champ [key], c'est bien. Cependant, si l'index n'a pas le champ [key] comme première key, il commence à faire des loops. Il ne fait même pas un scan de table, sinon parcourir 5000 disques ne serait pas si lent. La façon dont il fait la boucle est qu'il exécute la boucle pour le nombre total de résultats de FTS multiplié par le nombre total de résultats du rest de la requête. Donc, si le FTS returnne 2000 loggings et le rest de la requête renvoie 3000, il boucle 2000 * 3000 = 6,000,000. Je ne comprends pas pourquoi.

Donc, dans mon cas, il fait la search en text intégral, puis il rest de la requête, mais choisit l'index couvert que j'ai qui est basé sur field1, field2, id (qui est faux) et comme le résultat, il se fout. Si je change mon index couvert en Id, field1, field2 tout serait très rapide.

Mon attente était que FTS returnne un tas de [key], le rest de la requête returnne un package de [Id] et ensuite l'Id doit être comparé à [key].

Bien sûr, j'ai essayé de simplifier ma requête ici, mais la requête réelle est beaucoup plus compliquée et je ne peux pas simplement changer l'index. J'ai aussi des scénarios où le text passé en text intégral est vide et dans ces scénarios je ne veux même pas me joindre à containstable. Dans ces cas, changer mon index couvert pour avoir le champ id comme premier champ, va générer un désastre.

Quoi qu'il en soit, pour l'instant j'ai choisi la solution de la table temporaire, car cela fonctionne pour moi. Je limite également le résultat à quelques milliers, ce qui aide les problèmes de performance potentiels des variables de table lorsque le nombre d'loggings est trop élevé.

Merci

Normalement, cela fonctionne très vite:

 select t1.*, t2.Rank from containstable(table1, field2, 'something') as t2 join table1 as t1 ON t1.id = t2.Key AND t1.field1=90 order by t2.Rank desc 

Il y a une grande différence lorsque vous mettez vos critères de search: dans JOIN ou dans WHERE.

Je vais supposer ici que votre problème est le même que sur l'autre thread auquel je suis lié. Trouvez-vous que le problème se pose avec plusieurs termes de search de mots?

Si oui, ma réponse de ce fil s'appliquera.

De http://technet.microsoft.com/en-us/library/cc721269.aspx#_Toc202506240

La chose la plus importante est que le type de jointure correct est choisi pour la requête en text intégral. L'estimation de la cardinalité sur le FulltextMatch STVF est très important pour le bon plan. La première chose à vérifier est donc l'estimation de la cardinalité FulltextMatch. C'est le nombre estimé de résultats dans l'index pour la string de search de text intégral. Par exemple, dans la requête de la figure 3, cela devrait être proche du nombre de documents contenant le mot «mot». Dans la plupart des cas, il devrait être très précis, mais si l'estimation était longue, vous pourriez générer de mauvais plans. L'estimation pour des termes simples est normalement très bonne, mais l'estimation de plusieurs termes tels que des phrases ou des requêtes ET est plus complexe car il est impossible de savoir quelle sera l'intersection des termes de l'index sur la fréquence des termes de l'index . Si l'estimation de la cardinalité est bonne, un mauvais plan est probablement provoqué par le model de coût de l'optimiseur de requête. La seule façon de résoudre le problème de plan consiste à utiliser un indicateur de requête pour forcer un certain type de jointure ou OPTIMISER POUR.

Ainsi, il ne peut tout simplement pas savoir à partir des informations qu'il stocke si les deux termes de search set sont susceptibles d'être assez indépendants ou communément trouvés set. Peut-être devriez-vous avoir deux procédures séparées, l'une pour les requêtes de mots simples que vous laissez l'optimiseur faire son travail et l'autre pour les termes de search de plusieurs mots que vous forceriez à un plan "assez bon" (sys.dm_fts_index_keywords peut vous aider si vous voulez estimation approximative de cardinalité vous-même).

Si vous rencontrez le problème avec des requêtes à un seul mot, ce passage de l'article lié peut s'appliquer.

Dans la search de text intégral SQL Server 2008, nous avons la possibilité de modifier le plan généré en fonction d'une estimation de la cardinalité du terme de search utilisé. Si le plan de requête est fixe (comme dans une requête paramétrée dans une procédure stockée), cette étape n'a pas lieu. Par conséquent, le plan compilé sert toujours cette requête, même si ce plan n'est pas idéal pour un terme de search donné.

Vous devrez peut-être utiliser l'option RECOMPILE.