Início / dba / Perguntas / 330828
Accepted
Steven M
Asked: 2023-09-01 23:25:47 +0800 CST

Existe alguma maneira de melhorar o desempenho em pesquisas semelhantes que tenham% à esquerda do valor pesquisado?

  • 772

Eu tenho uma consulta que faz uma declaração semelhante em uma coluna que armazena locais de caminho completo de arquivos em um computador.

Exemplo

select * 
from table 
where fullpath like '%hi.exe'

Que nunca parece usar um índice e é muito lento comparado a fazer fullpath = 'value' (obviamente)

Mas minha pergunta é: existe alguma maneira ou ideia de como acelerar os resultados da consulta ou fazer com que ela use índices?

Não vejo a mesma lentidão se o like estiver no lado direito (ex select * from table where fullpath like 'hi%'

EDITAR: Microsoft SQL Server 2017 Standard Edition

sql-server

3 respostas

  1. Best Answer

    FWIW, não acho que as perguntas duplicadas fornecidas anteriormente fossem realmente duplicadas desta pergunta específica e, em vez disso, responderam a uma pergunta tangencialmente mais complexa sobre como melhorar a pesquisa com curingas duplos ( contém tipos de pesquisas). Algumas das respostas provavelmente também podem ser aplicadas aqui, mas são excessivamente complicadas para este problema muito mais fácil, daí o meu voto para reabrir a questão. Tentei localizar uma duplicata adequada com a solução mais simples, mas não consegui encontrar uma.

    Existe alguma maneira de melhorar o desempenho em pesquisas semelhantes que tenham% à esquerda do valor pesquisado?

    Supondo que você queira dizer estritamente apenas o tipo de pesquisa começa com (apenas curingas iniciais) e não contém o tipo de pesquisa, existem algumas soluções bastante simples.

    Uma solução envolve armazenar uma cópia da coluna invertida (uma coluna computada pode ajudar aqui) e então criar um índice na coluna invertida. Em seguida, você pode reverter a ordem dos curingas para torná - la uma pesquisa que termina com a coluna invertida. Isso seria logicamente equivalente e também sargável , permitindo assim que o índice fosse usado de maneira eficiente.

    Código de exemplo:

    -- Step 1: Add a computed column with the reversed column
ALTER TABLE YourTable
ADD fullpath_reversed AS REVERSE(fullpath);

-- Step 2: Index the newly created reversed column 
CREATE NONCLUSTERED INDEX IX_YourTable_fullpath_reversed ON YourTable (fullpath_reversed);

-- Step 3: Query the newly created column efficiently 
select * 
from YourTable 
where fullpath_reversed like 'exe.ih%';

    Observe que você não deve usar SELECT *nenhum código não ad hoc, especialmente quando estiver preocupado com o desempenho. Liste apenas as colunas que realmente lhe interessam para aquela consulta específica. Tem melhor desempenho por ler menos dados do que o necessário e potencialmente resultar em um melhor plano de consulta, com índices escolhidos e operações de índice usadas.

    • 9

  2. Se você normalmente pesquisa nomes de arquivos, armazene-os separadamente em vez de incorporá-los em arquivos fullpath.

    Talvez seja melhor armazenar o caminho, o nome do arquivo e talvez a extensão separadamente, dependendo das consultas.

    Usará menos espaço do que uma cópia invertida do arquivo fullpath.

    • 6

  3. Mantenha uma coluna separada com menos largura apenas para o nome do arquivo. Além disso, se você tiver vários tipos de extensão de arquivo, poderá manter o sinalizador tinyint em uma coluna separada.

    É apenas uma tarefa única durante o processo de inserção/atualização.

    Dessa forma, você pode evitar Likecompletamente o operador.

    SARGableé apenas um dos fatores para o otimizador usar o índice

    Outro fator principal é a largura da coluna. Menos a largura da coluna, haverá menos número de páginas de índice. Portanto, o otimizador terá que ler menos páginas, portanto, maior será a chance de usar o Índice.

    O otimizador geralmente faz um plano bom o suficiente e um plano econômico. Em alguns casos, mesmo que o índice não seja usado, o plano ainda é bom o suficiente.

    Não faça índice na coluna do tipo de dados varchar com mais largura. Se a tabela contiver milhões de linhas posteriormente, esse índice será desperdiçado e poderá levar mais tempo para executar até mesmo consultas pequenas que retornem menos número de linhas

    • 1

relate perguntas