Eu tenho um zoológico de 20 milhões de animais que acompanho em meu banco de dados SQL Server 2005. Cerca de 1% deles são negros e cerca de 1% deles são cisnes. Eu queria obter detalhes de todos os cisnes negros e assim, não querendo inundar a página de resultados, fiz:
select top 10 *
from animal
where colour like 'black'
and species like 'swan'
(Sim, desaconselhadamente esses campos são de texto livre, mas ambos são indexados). Acontece que não temos esses animais, pois a consulta retorna um conjunto vazio em cerca de 300 milissegundos. Teria sido cerca de duas vezes mais rápido se eu tivesse usado '=' em vez de 'curtir', mas tenho uma premonição de que o último está prestes a me poupar um pouco de digitação.
Acontece que o tratador-chefe acha que ele pode ter inserido alguns dos cisnes como 'negros', então modifico a consulta de acordo:
select top 10 *
from animal
where colour like 'black%'
and species like 'swan'
Acontece que também não há nenhum desses (e de fato não há animais 'black%', exceto os 'black'), mas a consulta agora leva cerca de 30 segundos para retornar vazia.
Parece que é apenas a combinação de 'top' e 'like %' causando problemas porque
select count(*)
from animal
where colour like 'black%'
and species like 'swan'
retorna 0 muito rapidamente, e mesmo
select *
from animal
where colour like 'black%'
and species like 'swan'
retorna vazio em uma fração de segundo.
Alguém tem alguma ideia de por que 'top' e '%' devem conspirar para causar uma perda tão dramática de desempenho, especialmente em um conjunto de resultados vazio?
EDIT: Só para esclarecer, não estou usando nenhum índice FreeText, apenas quis dizer que os campos são de texto livre no ponto de entrada, ou seja, não normalizados no banco de dados. Desculpe a confusão, palavras mal formuladas da minha parte.