Parte da minha carga de trabalho usa uma função CLR que implementa o algoritmo de hash assustador para comparar linhas para ver se algum valor de coluna foi alterado. A função CLR usa uma string binária como entrada, então preciso de uma maneira rápida de converter linhas em uma string binária. Espero fazer hash de cerca de 10 bilhões de linhas durante a carga de trabalho completa, então gostaria que esse código fosse o mais rápido possível.
Tenho cerca de 300 tabelas com esquemas diferentes. Para os propósitos desta questão, suponha uma estrutura de tabela simples de 32 INTcolunas anuláveis. Forneci dados de exemplo, bem como uma maneira de comparar os resultados na parte inferior desta pergunta.
As linhas devem ser convertidas na mesma string binária se todos os valores das colunas forem iguais. As linhas devem ser convertidas em strings binárias diferentes se algum valor de coluna for diferente. Por exemplo, código tão simples quanto o seguinte não funcionará:
CAST(COL1 AS BINARY(4)) + CAST(COL2 AS BINARY(4)) + ..
Ele não manipula NULLs corretamente. Se COL1for NULL para a linha 1 e COL2for NULL para a linha 2, ambas as linhas serão convertidas em uma string NULL. Acredito que o tratamento correto de NULLs é a parte mais difícil de converter a linha inteira corretamente. Todos os valores permitidos para as colunas INT são possíveis.
Para antecipar algumas perguntas:
- Se for importante, na maioria das vezes (90%+) as colunas não serão NULL.
- Eu tenho que usar o CLR.
- Eu tenho que hash tantas linhas. Não consigo persistir os hashes.
- Acredito que não posso usar o modo batch para a conversão devido à presença da função CLR.
Qual é a maneira mais rápida de converter 32 INTcolunas anuláveis em uma string BINARY(X)ou ?VARBINARY(X)
Dados de amostra e código conforme prometido:
-- create sample data
DROP TABLE IF EXISTS dbo.TABLE_OF_32_INTS;
CREATE TABLE dbo.TABLE_OF_32_INTS (
COL1 INT NULL,
COL2 INT NULL,
COL3 INT NULL,
COL4 INT NULL,
COL5 INT NULL,
COL6 INT NULL,
COL7 INT NULL,
COL8 INT NULL,
COL9 INT NULL,
COL10 INT NULL,
COL11 INT NULL,
COL12 INT NULL,
COL13 INT NULL,
COL14 INT NULL,
COL15 INT NULL,
COL16 INT NULL,
COL17 INT NULL,
COL18 INT NULL,
COL19 INT NULL,
COL20 INT NULL,
COL21 INT NULL,
COL22 INT NULL,
COL23 INT NULL,
COL24 INT NULL,
COL25 INT NULL,
COL26 INT NULL,
COL27 INT NULL,
COL28 INT NULL,
COL29 INT NULL,
COL30 INT NULL,
COL31 INT NULL,
COL32 INT NULL
);
INSERT INTO dbo.TABLE_OF_32_INTS WITH (TABLOCK)
SELECT 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, NULL, -876545321
FROM
(
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
GO
-- procedure to test performance
CREATE OR ALTER PROCEDURE #p AS
BEGIN
SET NOCOUNT ON;
DECLARE
@counter INT = 0,
@dummy VARBINARY(8000);
WHILE @counter < 10
BEGIN
SELECT @dummy = -- this code is clearly incomplete as it does not handle NULLs
CAST(COL1 AS BINARY(4)) +
CAST(COL2 AS BINARY(4)) +
CAST(COL3 AS BINARY(4)) +
CAST(COL4 AS BINARY(4)) +
CAST(COL5 AS BINARY(4)) +
CAST(COL6 AS BINARY(4)) +
CAST(COL7 AS BINARY(4)) +
CAST(COL8 AS BINARY(4)) +
CAST(COL9 AS BINARY(4)) +
CAST(COL10 AS BINARY(4)) +
CAST(COL11 AS BINARY(4)) +
CAST(COL12 AS BINARY(4)) +
CAST(COL13 AS BINARY(4)) +
CAST(COL14 AS BINARY(4)) +
CAST(COL15 AS BINARY(4)) +
CAST(COL16 AS BINARY(4)) +
CAST(COL17 AS BINARY(4)) +
CAST(COL18 AS BINARY(4)) +
CAST(COL19 AS BINARY(4)) +
CAST(COL20 AS BINARY(4)) +
CAST(COL21 AS BINARY(4)) +
CAST(COL22 AS BINARY(4)) +
CAST(COL23 AS BINARY(4)) +
CAST(COL24 AS BINARY(4)) +
CAST(COL25 AS BINARY(4)) +
CAST(COL26 AS BINARY(4)) +
CAST(COL27 AS BINARY(4)) +
CAST(COL28 AS BINARY(4)) +
CAST(COL29 AS BINARY(4)) +
CAST(COL30 AS BINARY(4)) +
CAST(COL31 AS BINARY(4)) +
CAST(COL32 AS BINARY(4))
FROM dbo.TABLE_OF_32_INTS
OPTION (MAXDOP 1);
SET @counter = @counter + 1;
END;
SELECT cpu_time
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
END;
GO
-- run procedure
EXEC #p;
(Ainda estarei usando o hash assustador neste resultado binário. A carga de trabalho usa junções de hash e o valor de hash é usado para uma das compilações de hash. Não quero um valor binário longo na compilação de hash porque requer muito memória.)
Na minha máquina (SQL Server 2017), a seguinte função C# SQLCLR é executada cerca de 30% mais rápido que a
binary(5)ideia, 35% mais rápido queCONCAT_WSe na metade do tempo de resposta automática.Requer
UNSAFEpermissão e usa ponteiros. A implementação está muito especificamente ligada aos dados de teste.Para fins de teste, a maneira mais fácil de fazer esse assembly inseguro funcionar é definir o banco de dados
TRUSTWORTHYe desabilitar a opção de configuração de segurança clr strict, se necessário.Código compilado
Por conveniência, os
CREATE ASSEMBLYbits compilados estão em https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8eEsboço de Função T-SQL
Código fonte
A fonte C# está em https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f
Se você compilar isso por conta própria, deverá usar uma biblioteca de classes (.dll) como o tipo de projeto de destino e marcar a opção de compilação Permitir código não seguro.
Solução combinada
Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.
An example implementation based on a table with a mixture of column data types is at https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460. This includes an unsafe inlined version of the Spooky Hash algorithm derived from Jon Hanna's SpookilySharp and the original public domain C source code by Bob Jenkins.
Uma
INTcoluna tem quatro bytes de valores permitidos que correspondem exatamente ao tamanho de umBINARY(4). Em outras palavras, cada valor possível de um BINARY(4) corresponde a um valor possível de umaINTcoluna. Portanto, a menos que haja um valor que não seja permitido naINTcoluna, não há substituto seguro para um NULL. Se uma coluna é NULL ou não, deve ser codificado separadamente. Ele simplesmente não pode caber dentro de um arquivoBINARY(4).Uma maneira de fazer isso é com um bitmap NULL. Considere o seguinte código:
Se oito colunas são ou não NULL cabe em um único byte. Essas expressões podem ser comparadas entre as linhas para verificar se todas as mesmas colunas são NULL ou não NULL. Com essas informações adicionais, torna-se seguro substituir um valor de coluna NULL por qualquer coisa que não seja NULL. Achei
CAST(ISNULL(COL1, 0) AS BINARY(4))o mais rápido, embora outras variaçõesISNULL(CAST(COL1 AS VARBINARY(4)), 0x)sejam possíveis.É difícil provar algo definitivamente, mas achei os seguintes detalhes os mais rápidos:
Na minha máquina, o benchmark leva cerca de 27,5 segundos de CPU. Infelizmente, a etapa de bitmap NULL leva cerca de um terço desse tempo. Seria bom se houvesse uma maneira mais rápida de fazer isso.
Aqui está a solução completa:
Que tal usar
BINARY(5)e converter NULLs em algo fora do alcance para INTs:Em meus testes, concat_ws foi um pouco mais rápido (18 segundos) do que sua solução de bitmap nula (26 segundos). Haverá mais dados para embaralhar para que você possa ver alguma degradação de desempenho em outros lugares e se quiser misturar isso com colunas de caracteres, você deve escolher o delimitador com sabedoria.
Se você puder garantir com antecedência que não armazene algum int específico, como,
-2,147,483,648então, você pode fazer algo como: