Como uma consulta com um TOP 10 demora infinitamente mais para concluir do que a mesma consulta sem o TOP 10?

9

Eu tenho uma situação muito simples:

Eu tenho uma função com valor de tabela chamada FullTextPagina definida da seguinte maneira:

select * from Pagina as p where contains(p.PageText, @term)

E depois tenho duas consultas:

declare @term nvarchar(4000)= N'"DIEGO NUNES J COMBINADO"'

SELECT Id, DtPagina
FROM FullTextPagina(@term)
ORDER BY DtPagina DESC

SELECT TOP 10 Id, DtPagina
FROM FullTextPagina(@term)
ORDER BY DtPagina DESC

Eles são idênticos, exceto pelo fato de que o segundo inclui uma instrução TOP 10 . E eles não retornam nada. 0 linhas.

O primeiro é executado instantaneamente. Os segundos levam 1: 20m para serem concluídos.

Por quê?

PS:

  1. Configurei corretamente o índice de texto completo
  2. Eu tenho um índice descendente não-agrupado e não exclusivo em DtPagina
  3. O plano de execução está aqui: link

EDITAR

Respondendo ao @MartinSmith, curiosamente, o "Número de Execuções" para as Funções de Valor da Tabela é de 1,18 milhão para o caso do TOP 10 e de 1 para o outro caso

EDIT 2

XML do plano de execução link

EDIT 3

Adicionar opção (recompilar) ou retirar os parâmetros não afeta o resultado

SELECT Id, DtPagina
FROM FullTextPagina(N'"DIEGO NUNES J COMBINADO"')
ORDER BY DtPagina DESC

SELECT TOP 10 Id, DtPagina
FROM FullTextPagina(N'"DIEGO NUNES J COMBINADO"')
ORDER BY DtPagina DESC
OPTION (RECOMPILE)

EDIT 4

Código completo para FullTextPagina

USE [RexConsumo_2011_11]
GO

/****** Object:  UserDefinedFunction [dbo].[FullTextPagina]    Script Date: 11/24/2011 11:43:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FullTextPagina] (@term nvarchar(4000))
RETURNS TABLE 
AS
RETURN 
(
    select * from Pagina as p where contains(p.PageText, @term)
)

GO
    
por André Pena 24.11.2011 в 14:54
fonte

1 resposta

5

O problema que você está recebendo é porque o SQL Server não está estimando com precisão quantas linhas corresponderão ao predicado.

Sua consulta está fazendo SELECT TOP 10 Id, DtPagina ... ORDER BY DtPagina DESC . Existem algumas opções de como isso pode ser feito

Opção 1

Ele poderia apenas verificar o índice DtPagina DESC na ordem e ver se cada linha corresponde ao predicado de texto completo e sair quando os 10 primeiros na ordem de índice forem encontrados.

Opção 2

  1. Avaliar o predicado de texto completo
  2. Recupere os valores da coluna DtPagina para todas as linhas correspondentes
  3. classificá-los e obter o top 10.

Ao custar a primeira opção, o plano inferior mostra que ele precisa varrer cerca de 600 linhas antes de obter 10 correspondências e poder sair. Essa é uma subestimativa massiva, já que na verdade nenhuma linha corresponde ao predicado e precisa fazer isso em 1.186.533 linhas.

Ao custar a segunda opção a partir do plano superior, pode-se ver que existem 13.846,2 linhas correspondentes que serão trazidas de volta da consulta de índice de texto completo e precisam ser unidas e classificadas. Essa é uma estimativa muito grande, pois o número real é zero.

Portanto, essas estimativas incorretas levam a favorecer a primeira opção incorretamente.

Não sei o que pode ser feito para melhorar a precisão das estatísticas de indexação do texto completo. Talvez tente reescrever a consulta usando containstable

Edit: Isso é um pouco complicado, mas pode funcionar bem. E se você tentar

declare @term nvarchar(4000)= N'"DIEGO NUNES J COMBINADO"'
declare @num int = 10

SELECT TOP (@num) Id, DtPagina
FROM FullTextPagina(@term)
ORDER BY DtPagina DESC

Em seguida, ele assumirá TOP 100 , o que pode ser suficiente para levá-lo a escolher o outro plano mais eficiente.

    
por Martin Smith 24.11.2011 / 15:54
fonte