Como alterar as configurações do gerenciador de configuração do servidor SQL usando o TSQL?

Question

ttugates

Asked: 2023-01-13 12:14:21 +0800 CST2023-01-13 12:14:21 +0800 CST 2023-01-13 12:14:21 +0800 CST

Como melhorar a performance desta Função Escalar para busca com ranking por palavra

772

Eu tenho uma função que pesquisa e classifica para ser usada em estoque e números de peças. Tentamos a pesquisa de texto completo e a pesquisa cognitiva do Azure e foi uma longa jornada. Preciso corresponder e classificar palavras arbitrárias definidas como "Delimitado por espaço". Em tabelas com mais de 75 mil registros, a consulta leva de 3 a 5 segundos.

O seguinte demonstra o que tenho funcionando, mas gostaria de otimizar:

Configurar:

IF OBJECT_ID(N'dbo.MyTable', N'U') IS NOT NULL DROP TABLE MyTable;

CREATE TABLE MyTable (
    Id int NOT NULL,
    Data varchar(255),
    PRIMARY KEY (Id)
);

INSERT INTO MyTable VALUES (1, 'A CAR'), (2, 'A BIKE'), (3, 'CAR WASH'), (4, 'CAR BIKE RACK'), (5, 'A HOUSE');

IF OBJECT_ID('dbo.fn__SearcRankMyTable') IS NOT NULL DROP FUNCTION fn__SearcRankMyTable
GO

A função:

CREATE FUNCTION [dbo].[fn__SearcRankMyTable]
(
  @Id INT, 
  @Query VARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
  DECLARE @Result INT;
  WITH T AS ( 
      SELECT B.[value] FROM MyTable
      CROSS APPLY STRING_SPLIT([Data], ' ') as A
      CROSS JOIN STRING_SPLIT(@Query, ' ') AS B
      WHERE Id = @id AND A.value LIKE b.[value] + '%'
    )
  SELECT @Result =  COUNT(Distinct(value)) FROM T
  RETURN @Result
END
GO

Consulta de uso pretendido:

SELECT S.*, Rank FROM MyTable S
CROSS APPLY ( SELECT [dbo].[fn__SearcRankMyTable](S.iD, 'CAR BIKE RACK')) ca (Rank)
WHERE RANK > 0
ORDER BY Rank DESC;

Esta consulta acima para "CAR BIKE RACK"produz o seguinte conforme desejado:

Comportamento de pesquisa definido:

Suponha que a linha já tenha calculado a coluna (dados) de palavras distintas:
Para cada palavra de consulta que corresponda ao início de uma palavra na linha de dados, incremente a classificação. A classificação mais alta possível será o número de palavras na consulta.
A consulta de pesquisa pode ter no máximo 4 palavras, pode ser passada dividida em palavras do lado do servidor como passada se ajudar.

Próximas coisas para tentar:

Eu li que as funções com valor escalar são lentas (mais) / thread único. Tente converter para a função Table Valued.
Dentro do CROSS APPLY STRING_SPLIT([Data], ' ') as Ae o último Distinctseria bom ter uma maneira de resgatar. Esta será minha próxima abordagem.

Existem outras considerações/tecnologias/abordagens que devo considerar?

Otimização com RETURN SELECT usando WHERE EXISTS (60% mais lento, infelizmente)

CREATE FUNCTION [dbo].[fn__SearcRankMyTable]
(
  @Id INT, 
  @Query VARCHAR(MAX)
)
RETURNS INT
AS
BEGIN 
    RETURN (SELECT COUNT(*) FROM STRING_SPLIT(@Query, ' ') as A
    WHERE EXISTS (SELECT value FROM STRING_SPLIT((SELECT DATA FROM MyTable WHERE Id = @Id), ' ') WHERE [value] LIKE a.[value] + '%' ));
END
GO

3 respostas

Voted

Martin Smith · Answer 1 · 2023-01-17T09:42:32+08:00

Para os seguintes dados de exemplo

INSERT INTO MyTable VALUES ('A CAR'), 
                           ('A BIKE'), 
                           ('CAR WASH'), 
                           ('CAR BIKE RACK'), 
                           ('A HOUSE');

INSERT INTO MyTable
SELECT TOP 75000 REPLACE(NEWID(), '-', ' ' )
FROM sys.all_columns c1, sys.all_columns c2

No WPR, seu método atual se parece com isso

Há uma classificação para a classificação final e outras classificações internas para a contagem distinta, bem como o tempo de CPU gasto na divisão da string (potencialmente piorada ao lidar com tipos de dados LOB)

Um método mais rápido que funciona com sua estrutura existente seria

DECLARE @Query VARCHAR(8000) = 'car BIKE RACK';

WITH QSplit([1], [2], [3], [4]) As
(
SELECT ' ' + [1], 
       ' ' + [2], 
       ' ' + [3], 
       ' ' + [4]
FROM string_split(UPPER(@Query), ' ', 1)
PIVOT (MAX(value) FOR ordinal IN ([1], [2], [3], [4])) AS P
)
SELECT m.*, Rank
FROM MyTable m
CROSS JOIN QSplit
CROSS APPLY (VALUES (CONCAT(' ', UPPER(Data) COLLATE Latin1_General_100_BIN2))) V(SpacePadded)
CROSS APPLY (VALUES (IIF(CHARINDEX([1],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([2],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([3],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([4],SpacePadded) > 0,1,0))               
                ) V2(Rank)
WHERE Rank > 0 
ORDER BY Rank DESC;

Esse

Define @Querycomo um tipo de dados não máximo, pois não parece necessário maxe pode ser mais lento.
Só divide @Queryuma vez em vez de refazer a divisão para cada linha emMyTable
Remove a sobrecarga da UDF escalar (não embutida)
Simplifica o cálculo RANKpara remover alguns operadores do plano de execução - incluindo a classificação interna.
Usa um agrupamento binário na expectativa de que isso acelere a CHARINDEXpesquisa (NB: Se você puder garantir que todos os Dataserão armazenados em uma forma canônica de maiúsculas, faça-o e remova essa UPPER(Data)chamada para evitar gastar tempo de CPU fazendo isso em tempo de execução)

Com esses dados fictícios, descobri que o método revisado é executado em cerca de 100 ms na minha máquina (o método original era de cerca de 3 segundos).

A cláusula de agrupamento binário fez uma redução de 500ms para 100ms, mesmo com a sobrecarga adicional de chamada, UPPERmas se os dados puderem ser armazenados em letras maiúsculas na tabela, isso economizará algum tempo adicional de CPU, pois não há necessidade de chamar, UPPER(Data)o que removerá a operação destacada (reduz para ~ 65 ms para mim).

stecyk · Answer 2 · 2023-01-23T12:42:58+08:00

você pode tentar uma tabela de contagem para uma chamada de função de valor de tabela ...

    CREATE function [dbo].[tvf_DelimitedString] 
(
    @string varchar(8000)
    , @demiliter char(1)
)

returns table with schemabinding as
return

with tally (n)
    as
        ( -- declare a tally table of up to 8000 records and limit it to the length of the incoming variable
            select top(isnull(datalength(@string), 0))
                row_number() over(order by (select null))
            from (values (0), (0), (0), (0), (0), (0), (0), (0)) as a(n) -- 8
            cross join (values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) as b(n) -- 80
            cross join (values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) as c(n) -- 800
            cross join (values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) as d(n) -- 8000
                    
        )

, cteStartPos (n1) 
    as 
        (-- this notates the starting position for the first character of each word in the string)
            select 1 
            union all
            select t.n+1 
            from Tally t 
            where substring(@String,t.N,1) = @demiliter
        )
, cteLength (n1,l1)
    as
        ( -- this notates the length of each word in the string
            select s.n1
            -- is null | null if handles instances where the incoming string doesn't have a space
            , isnull(nullif(charindex(@demiliter, @string, s.n1), 0)-s.n1, 8000)
            from cteStartPos as s
        )
-- split the string based on the delimiter
 select ItemNumber = row_number() over(order by l.n1)
        , Item = substring(@String, l.n1, l.l1)
   from cteLength as l
;
GO

então você pode chamar a função de valor da tabela semelhante a como você chama a função string_split

  declare @string varchar(8000) = 'car bike rack'
 SELECT myt.data, count(rankto.item) as Ranked
   FROM dbo.Mytable as myt
   cross apply  dbo.tvf_DelimitedString (data,' ') split
   cross apply  dbo.tvf_DelimitedString (@string, ' ') as rankto
   where rankto.item = split.item
   group by myt.data
   order by ranked

Basicamente, peguei sua tabela de amostra e criei uma tabela de registros de 100k no SQL Express e obtive um conjunto de resultados em 1 segundo. Se você puder fazer o pedido na camada do aplicativo, isso também pode ajudar no desempenho.

stecyk · Answer 3 · 2023-01-23T13:52:21+08:00

stecyk

2023-01-23T13:52:21+08:002023-01-23T13:52:21+08:00

ao usar a função de divisão de string, quanto mais longa a string que você passar, pior será o plano de execução, porque o mecanismo não tem ideia de quantas palavras há em sua string.

[B.Ozar fez uma postagem no blog sobre isso recentemente.][1]

create function dbo.tvf_SSrank (
    @string varchar(8000)
    , @demiliter char(1)
    )
    
    returns table with schemabinding
    return
    
    select data, count(t1.value) as ranked
    from dbo.MyTable as tbl
    cross apply string_split(tbl.data, @demiliter) as t1
    cross apply string_split(@string,@demiliter) as t2
    where t1.value = t2.value
    group by data

0

Como melhorar a performance desta Função Escalar para busca com ranking por palavra

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Como melhorar a performance desta Função Escalar para busca com ranking por palavra

3 respostas

relate perguntas