主页 / dba / 问题 / 334533
Accepted
crokusek
Asked: 2024-01-04 07:21:34 +0800 CST

如何将 Convert() 评估延迟到加入之后

  • 772

以下查询转换表示 13k 行 x 2 列的打包 CSV 的单个字符串。A 列是一个 bigint。B 列是一个smallint。

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

执行计划： https://www.brentozar.com/pastetheplan/?id =By0hYPmd6

如计划所示，convert() 的评估发生在连接之前，因此有时（取决于输入的长度），它会失败

nvarchar 值“37645”的转换溢出了 INT2 列。使用更大的整数列。

暂时更改从smallint 到int 的转换可以完成查询，并且检查BrandId 列输出表明，在本示例中它始终只包含值“36”。

有没有一种简单的方法可以将转换（smallint，i2.Value）延迟到连接之后，以便仅转换那些预期的CSV位置？

我意识到还有其他方法来打包字符串流（例如使用多个变量，或交错不同的分割字符等），但我对出于此问题的目的以这种方式解决此示例不感兴趣。谢谢！

sql-server

2 个回答

  1. Best Answer

    如果您想使用这种自连接方法，我只需使用try_convert，这样您就不必依赖于convert评估的位置。

    TRY_CONVERT(smallint, '37645')返回NULL而不是错误，因此如果连续发生这种情况随后被过滤掉也没关系。

    此外，您计算序数的方法string_split 也不可靠。

    输出行可以按任意顺序排列。不保证顺序与输入字符串中子字符串的顺序匹配。

    即使事实并非如此，order by (select null)仍然不能保证任何事情。

    假设您不在 SQL Server 2022 上（因此该enable_ordinal参数对您不可用），您可以调整 CSV 以将其转换为 JSON 数组格式，并以这种方式提供序数。

    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

    但我的建议是完全放弃自连接并使用条件聚合。

    在这种情况下，如果您确定字符串中的每隔一个元素都是有效的，smallint您可以恢复为convert. 该表达式应防止对不计算CASE为 的值进行计算。ordinal %21

    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 更适合将一列更改为多列。使用 PIVOT，您可以通过以下两种方式之一编写查询。

    将 STRING_SPLIT 与 enable_ordinal 一起使用（如果可用）：

    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

    或者，使用 OPENJSON，正如 @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

相关问题