Início / dba / Perguntas / 334406
Accepted
sovguy
Asked: 2023-12-29 06:26:20 +0800 CST

Práticas recomendadas para pesquisar SQL Server NVARCHAR col usando o termo curinga WHERE LIKE

  • 772

Eu tenho uma tabela SQL bastante simples (atualmente Azure SQL, pode ficar lá ou acabar na instância local do SQL Server 19, se isso for importante), cerca de 100.000 registros, que são principalmente para leitura, mas serão gravados gradualmente.

CREATE TABLE [dbo].[xCatalog](
    [catalogID] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](255) NOT NULL,
    [dateA] [datetime] NULL,
    [dateB] [datetime] NULL,
    [textA] [nvarchar](25) NULL,
    [textB] [nvarchar](255) NULL,
    [textC] [nvarchar](255) NULL,
 CONSTRAINT [PK_xCatalog] PRIMARY KEY CLUSTERED 
(
    [catalogID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Digno de nota é o campo [title] nvarchar(255) , que será o que os usuários pesquisarão principalmente usando uma consulta formatada como abaixo usando "oceano" como termo de pesquisa de exemplo [title]

SELECT TOP(500) [catalogID],[title],[dateA],[dateB],[textA],[textB],[textC] 
FROM [dbo].[xCatalog] 
WHERE ([title] LIKE '%ocean%') 
ORDER BY [catalogID] ASC

Ele funciona razoavelmente bem, de 1 a 3 segundos, mas estou realmente interessado em mergulhar em como/se melhorá-lo, pois, francamente, tenho mais do que apenas uma compreensão superficial de coisas como índices e outras otimizações.

A consulta está sendo gerada por um programa front-end que não acredito que possa alterar, por isso está selecionando uma quantidade limitada classificada pela chave para retornar à rolagem virtual e por que não estou explorando a substituição de LIKE por Contém ou qualquer coisa assim.

Então, estou realmente tentando chegar a isso do lado do banco de dados e apenas entender quais seriam as melhores práticas e quais são as opiniões das pessoas sobre esse tipo de caso, no que diz respeito a que tipo de índice usar ou se mudar para ColumnStore seria aplicável (provavelmente muito pequeno?), etc. Também estamos usando um banco de dados SQL do Azure de nível básico, então talvez para esse tipo de operação nos beneficiássemos de aumentar um pouco isso.

Qualquer coisa assim para que eu possa saber daqui para frente e poder explicar com segurança que fiz x ou y para ajudá-lo, mas também talvez a pesquisa de caracteres curinga em texto grande às vezes seja cara, então quais deveriam ser minhas expectativas.

Apresentarei o plano de execução simples, caso seja útil:

plano de execução de consulta insira a descrição da imagem aqui

Obrigado a todos pela contribuição! Eu sei que há muitas perguntas semelhantes que continuarei pesquisando, mas às vezes parece que são um pouco diferentes e imagino qual é o mal em perguntar também.

sql-server

1 respostas

  1. Best Answer

    O plano de execução parece razoável – o PK existente é o melhor possível. Podemos ler a tabela em ordem e causar um curto-circuito ao encontrar 500 linhas correspondentes. Se você não pode alterar a consulta, não há muito o que fazer em termos de otimização - mais memória, CPU mais rápida e armazenamento mais rápido podem ajudar.

    Se você puder alterar a consulta, há algumas opções a serem observadas:

    • Pesquisa FullText (CONTÉM)

    Se isso funcionar para você, use-o. Porém , não faz a mesma coisa que LIKE %%. Funciona bem para pesquisar texto geral, mas nem sempre funciona bem com códigos, etc.

    Com esta técnica, você pode manter o comportamento exato de LIKE %% com desempenho significativamente melhorado. Infelizmente, não há suporte integrado para ngrams/trigrams, então isso dá muito trabalho. Você também tem sobrecarga e armazenamento extras associados ao índice ngrams. Para uma tabela com 100 mil linhas, provavelmente não vale a pena. Eu usei e pode funcionar bem - mas o texto completo é uma opção muito mais simples, a menos que você realmente precise do comportamento de LIKE %%. Você também pode considerar usar outro sistema como o ElasticSearch, que pode usar ngrams/trigrams.

    • Usando um índice mais restrito para obter os 500 primeiros

    Algo assim pode ajudar a extrair um pouco mais de desempenho do LIKE %% se você realmente precisar.

    /* A narrower index might help.  Essentially a duplicate of PK though */

/*
CREATE UNIQUE NONCLUSTERED INDEX IX_xCatalog_catalogID_title ON dbo.xCatalog(catalogID) INCLUDE(title)
*/

CREATE TABLE #Matches(
    catalogID INT PRIMARY KEY
)

/* Get items that match criteria */
INSERT  INTO #Matches(
    catalogID
)
SELECT TOP(500) [catalogID]
FROM [dbo].[xCatalog] 
WHERE ([title] LIKE '%ocean%') 
ORDER BY [catalogID] ASC

/* Join back to table to return other columns */
SELECT TOP(500) C.[catalogID],C.[title],C.[dateA],C.[dateB],C.[textA],C.[textB],C.[textC] 
FROM #Matches M 
JOIN [dbo].[xCatalog] C ON M.catalogID = C.catalogID
ORDER BY M.[catalogID] ASC
    • Outro

    Há uma variedade de outras coisas que podem funcionar dependendo de suas necessidades. Adicionar tags para pesquisa pode funcionar, por exemplo. Talvez você possa armazenar em cache consultas de pesquisa comuns - o que deve funcionar muito bem se os dados forem relativamente estáticos.

    • 2

relate perguntas