FreeText Query es lento: incluye TOP y Order By

La tabla Producto tiene 700K loggings en ella. La consulta:

SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc

toma aproximadamente 1 minuto para ejecutarse. Hay un índice no agrupado en DateMadeNew y un índice de FreeText en Name.

Si elimino TOP 1 o Order By – toma less de 1 segundo para ejecutarse.

Aquí está el enlace al plan de ejecución. http://screencast.com/t/ZDczMzg5N

Parece que FullTextMatch tiene más de 400,000 ejecuciones. ¿Por qué está pasando esto? ¿Cómo se puede hacer más rápido?

ACTUALIZACIÓN 5/3/2010

Parece que la cardinalidad está fuera de control en las búsquedas de FreeText de múltiples palabras:

El optimizador estima que hay 28K loggings que coinciden con 'White Dress', mientras que en realidad solo hay 1. http://screencast.com/t/NjM3ZjE4NjAt

Si reemploop 'Vestido blanco' con 'Blanco', el número estimado es '27, 951 ', mientras que el número real es '28, 487', que es mucho mejor.

Parece que Optimizer está utilizando solo la primera palabra en la frase que se busca cardinalidad.

Editar

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

Lo más importante es que se selecciona el tipo de combinación correcto para la consulta de text completo. La estimación de cardinalidad en FulltextMatch STVF es muy importante para el plan correcto. Entonces, lo primero que debe verificar es la estimación de cardinalidad de FulltextMatch. Este es el número estimado de visitas en el índice para la cadena de búsqueda de text completo. Por ejemplo, en la consulta de la Figura 3 esto debería estar cerca del número de documentos que contienen el término 'palabra'. En la mayoría de los casos, debería ser muy preciso, pero si la estimación fuera larga, podría generar malos planes. La estimación de términos únicos suele ser muy buena, pero la estimación de términos múltiples como frases o consultas AND es más compleja ya que no es posible saber cuál será la intersección de los términos en el índice en function de la frecuencia de los términos en el índice . Si la estimación de cardinalidad es buena, un plan incorrecto probablemente sea causado por el model de costo del optimizador de consultas. La única forma de solucionar el problema del plan es utilizar una sugerencia de consulta para forzar un cierto tipo de combinación u OPTIMIZAR.

Por lo tanto, simplemente no puede saber, a partir de la información que almacena, si los dos términos de búsqueda juntos son bastante independientes o comunes. Tal vez deberías tener 2 procedimientos separados, uno para consultas de una sola palabra en el que permitas que el optimizador haga sus cosas y otro para procedimientos de varias palabras que fuerces un plan "suficientemente bueno" (sys.dm_fts_index_keywords podría ayudar si no quieres un plan una talla para todos plan).

NB: Su procedimiento de una sola palabra probablemente necesite la opción WITH RECOMPILE al mirar este fragment del artículo.

En la búsqueda de text completo de SQL Server 2008, tenemos la capacidad de modificar el plan que se genera en function de una estimación de cardinalidad del término de búsqueda utilizado. Si el plan de consulta es fijo (como lo es en una consulta parametrizada dentro de un procedimiento almacenado), este paso no se lleva a cabo. Por lo tanto, el plan comstackdo siempre sirve esta consulta, incluso si este plan no es ideal para un término de búsqueda determinado.

Respuesta original

Sin embargo, su nuevo plan todavía se ve bastante mal. Parece que solo está devolviendo 1 fila de la parte de consulta de text completo, pero escanea todas las 770159 filas de la tabla Producto.

¿Cómo funciona esto?

 CREATE TABLE #tempResults ( ID int primary key, Name varchar(200), DateMadeNew datetime ) INSERT INTO #tempResults SELECT ID, Name, DateMadeNew FROM Product WHERE contains(Name, '"White Dress"') SELECT TOP 1 * FROM #tempResults ORDER BY DateMadeNew desc 

No puedo ver el plan de ejecución vinculado, la policía de la networking está bloqueando eso, así que esto es solo una suposition …

si se ejecuta rápidamente sin TOP y ORDER BY , intente hacer esto:

 SELECT TOP 1 * FROM (SELECT ID, Name, DateMadeNew FROM Product WHERE contains(Name, '"White Dress"') ) dt ORDER BY DateMadeNew desc 

Parece que FullTextMatch tiene más de 400,000 ejecuciones. ¿Por qué está pasando esto?

Como tiene un índice combinado con TOP 1 , el optimizador cree que será mejor atravesar el índice, verificando cada logging de la input.

¿Cómo se puede hacer más rápido?

Si la actualización de las statistics no ayuda, intente agregar una pista a su consulta:

 SELECT TOP 1 * FROM product pt WHERE CONTAINS(name, '"test1"') ORDER BY datemadenew DESC OPTION (HASH JOIN) 

Esto obligará al motor a usar un algorithm HASH JOIN para unir su tabla y el resultado de la consulta de text completo.

La consulta de text completo se considera una fuente remota que devuelve el set de valores indexados por KEY INDEX proporcionado en la definición de FULLTEXT INDEX .

Actualizar:

Si su ORM utiliza consultas parametrizadas, puede crear una guía de plan.

  • Use Profiler para interceptar la consulta que el ORM envía al pie de la letra
  • Genere un plan correcto en SSMS usando sugerencias y guárdelo como XML
  • Use sp_create_plan_guide con un OPTION USE PLAN para forzar al optimizador a usar este plan.

Tengo una mejor solución.

I. Primero, veamos las soluciones propuestas, ya que también se pueden usar en algunos casos:

  1. OPCIÓN (HASH UNIRSE): no es bueno ya que puede get el error "El procesador de consultas no pudo generar un plan de consulta debido a las sugerencias definidas en esta consulta. Vuelva a enviar la consulta sin especificar sugerencias y sin usar SET FORCEPLAN".

  2. SELECCIONAR TOP 1 * FROM (ORIGINAL_SELECT) ORDER BY … – no es bueno, cuando necesita usar resultados de pagination de usted ORIGINAL_SELECT

  3. sp_create_plan_guide – no es bueno, como para usar plan_guide, tienes que save el plan para una statement sql específica, esto no funcionará para las sentencias sql dinámicas (por ejemplo, generadas por ORM)

II. Mi solución contiene dos partes 1. Tabla de unión automática utilizada para la búsqueda de text completo 2. Usar MS SQL HASH Unir sugerencias MSDN Join Hints

Su SQL:

 SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc 

Debe ser reescrito como:

 SELECT TOP 1 p.ID, p.Name FROM Product p INNER HASH JOIN Product fts ON fts.ID = p.ID WHERE contains(fts.Name, '"White Dress"') ORDER BY p.DateMadeNew desc 

Si está utilizando NHibernate con / sin Castle Active Records, he respondido en post como escribir el interceptor para modificar su consulta para replace INNER JOIN por INNER HASH JOIN

Un par de pensamientos sobre este:

1) ¿Ha actualizado las statistics en la tabla Producto? Sería útil ver las estimaciones y el número real de filas en las operaciones allí también.

2) ¿Qué versión de SQL Server estás usando? Tuve un problema similar con SQL Server 2008 que resultó ser nada más que no tener instalado el Service Pack 1. Instalar SP1 y una consulta de text libre que tardaba unos minutos (debido a una gran cantidad de ejecuciones reales contra real) se networkingujo a tomar un segundo.

Tuve el mismo problema antes.

El performance depende del índice único que elija para la indexing de text completo. Mi tabla tiene dos columnas únicas: ID y article_number .

La consulta:

 select top 50 id, article_number, name, ... from ARTICLE CONTAINS(*,'"BLACK*" AND "WHITE*"') ORDER BY ARTICLE_NUMBER 

Si el índice de text completo está conectado a ID entonces es lento dependiendo de las palabras buscadas. Si el índice de text completo está conectado con el índice ARTICLE_NUMBER UNIQUE , siempre fue rápido.