Início / dba / Perguntas / 334533
Accepted
crokusek
Asked: 2024-01-04 07:21:34 +0800 CST

Como atrasar a avaliação de convert() até depois da adesão

  • 772

A consulta a seguir converte uma única string de CSV compactado que representa 13 mil linhas por 2 colunas. A coluna A é um bigint. A coluna B é um inteiro pequeno.

declare
    @dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36';  -- abbreviated, actually 13k rows

  with Input as
  (
      select Value,
             Row = row_number() over (order by (select null)) - 1
      from string_split(@dataCsv, ',') o
  )
  --insert into PubCache.TableName
  select 78064 as CacheId,
         convert(bigint, i.Value) as ObjectId,
         convert(smallint, i2.Value) as BrandId
    from Input i
    inner hash join Input i2    -- hash to encourage string_split() only once per column
      on i2.Row = i.Row + 1
    where i.Row % 2 = 0
    order by i.Row

Plano de Execução: https://www.brentozar.com/pastetheplan/?id=By0hYPmd6

Conforme mostrado no plano, a avaliação de convert() está ocorrendo antes da junção, então às vezes (dependendo do comprimento da entrada), ela falha com

A conversão do valor nvarchar '37645' estourou uma coluna INT2. Use uma coluna inteira maior.

Alterar temporariamente a conversão de smallint para int permite que a consulta seja concluída e a inspeção da saída da coluna BrandId mostra que ela sempre contém apenas o valor '36' para este exemplo.

Existe uma maneira simples de atrasar o convert(smallint, i2.Value) até depois da junção, para que apenas as posições CSV esperadas sejam convertidas?

Sei que existem outras maneiras de compactar um fluxo de string (como usar múltiplas variáveis ​​ou entrelaçar diferentes caracteres divididos, etc.), mas não estou interessado em resolver este exemplo dessa forma para fins desta questão. Obrigado!

sql-server

2 respostas

  1. Best Answer

    Se você quiser usar essa abordagem de auto-junção, eu usaria apenas try_convertpara que você não dependesse de onde ela convertfosse avaliada.

    TRY_CONVERT(smallint, '37645')retorna NULLem vez de erro, portanto, não importa se isso acontece em uma linha posteriormente filtrada.

    Além disso, sua abordagem para calcular um ordinal string_split não é confiável .

    As linhas de saída podem estar em qualquer ordem. Não é garantido que a ordem corresponda à ordem das substrings na string de entrada.

    E mesmo que não fosse esse o caso, order by (select null)ainda assim não garantiria nada.

    Supondo que você não esteja no SQL Server 2022 (portanto, o enable_ordinalargumento não está disponível para você), você pode ajustar o CSV para colocá-lo no formato de matriz JSON e obter um ordinal dessa forma.

    declare
    @dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36';  -- abbreviated, actually 13k rows

  with Input as
  (
    SELECT Value,
           Row = CAST([key] AS INT)
    FROM OPENJSON(CONCAT('[', @dataCsv, ']'))
  )
  --insert into PubCache.TableName
  select 78064 as CacheId,
         try_convert(bigint, i.Value) as ObjectId,
         try_convert(smallint, i2.Value) as BrandId
    from Input i
    inner hash join Input i2    -- hash to encourage string_split() only once per column
      on i2.Row = i.Row + 1
    where i.Row % 2 = 0
    order by i.Row

    Mas minha sugestão seria abandonar completamente a auto-junção e usar agregação condicional.

    Nesse caso, se você tiver certeza de que cada segundo elemento da string será válido, smallintvocê poderá reverter para convert. A CASEexpressão deve evitar que seja avaliada nos valores em que ordinal %2não é avaliada como 1.

    DECLARE @dataCsv NVARCHAR(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows

WITH Input
     AS (SELECT Value,
                Row = [key] / 2,
                Col = [key] % 2
         FROM   OPENJSON(CONCAT('[', @dataCsv, ']')))
--insert into PubCache.TableName
SELECT CacheId = 78064,
       ObjectId = MAX(CONVERT(BIGINT,  CASE WHEN Col = 0 THEN Value END)),
       BrandId = MAX(CONVERT(SMALLINT, CASE WHEN Col = 1 THEN Value END))
FROM   Input i
GROUP  BY Row
ORDER  BY Row
    • 6

  2. PIVOT é mais adequado para transformar uma coluna em várias colunas do que auto-junções ou agrupamentos. Com o PIVOT, você escreveria sua consulta de qualquer uma das duas maneiras a seguir.

    Usando STRING_SPLIT com enable_ordinal, se disponível:

    DECLARE @dataCsv NVARCHAR(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows
WITH input AS (
    SELECT value, 
        (ordinal + 1) / 2 row, 
        ordinal % 2 col
    FROM STRING_SPLIT(@dataCsv, ',', 1)
)
--insert into PubCache.TableName
SELECT 78064 CacheId,
    CAST([1] AS bigint) ObjectId,
    CAST([0] AS smallint) BrandId
FROM input 
PIVOT (MAX(value) FOR col IN ([0], [1])) As pvt

    Alternativamente, usando OPENJSON, conforme apontado por @martin-smith:

    DECLARE @dataCsv NVARCHAR(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows
WITH input AS (
    SELECT value, 
        [key] / 2 row, 
        [key] % 2 col
    FROM OPENJSON(CONCAT('[', @dataCsv, ']'))
)
--insert into PubCache.TableName
SELECT 78064 CacheId,
    CAST([0] AS bigint) ObjectId,
    CAST([1] AS smallint) BrandId
FROM input 
PIVOT (MAX(value) FOR col IN ([0], [1])) As pvt
    • 1

relate perguntas