调整一个SQL标量函数,该函数执行大量简单操作。

huangapple go评论65阅读模式
英文:

Tune a SQL scalar function that does simple operations a lot of times

问题

我有一个图像数据类型的列,其值看起来类似于这样:

0x...32004200460054004F00560031004800360053005100380031006500300043004300550055003500350034003300370038005600420047003400310047004F004A00460030004C003100370030005200380054003600370045004F00320032004E005600360039004C00...

我必须只使用图像值的特定序列。我需要将其转换为类似于字符的数据类型(VARCHAR?)如下:

2BFTOV1H6SQ81e0CCUU554378VBG41GOJF0L170R8T67EO22NV69L

转换如下进行:

省略每一对中的第二对(它始终是00)
剩下的表示所需结果的ASCII代码(32 -> 2, 42 -> B, ...)。

我需要调整此过程,因为我必须应用数百万次甚至数千万次。现在我在3分钟内生成100万行。

我当前的尝试是这个SQL标量函数:

英文:

I have a column of data type image those values look similar to this:

0x...32004200460054004F00560031004800360053005100380031006500300043004300550055003500350034003300370038005600420047003400310047004F004A00460030004C003100370030005200380054003600370045004F00320032004E005600360039004C00...

I have to use only a certain sequence of the image value. And I need to convert it to a character like data type (VARCHAR?) like this:

2BFTOV1H6SQ81e0CCUU554378VBG41GOJF0L170R8T67EO22NV69L

The convertion is done as follows:

Ommit every second pair (it's always 00)
What remains represents the ASCII codes of the desired result (32 -> 2, 42 -> B, ...).

I need to tune this because I have to apply it millions of millions of times. Right now I make 1 mio. rows in 3 minutes.

My current attempt is this SQL scalar function:

ALTER FUNCTION [dbo].[F_Get_CClip](@pi_content_referral_blob IMAGE)
RETURNS VARCHAR(53)
AS

BEGIN
DECLARE @l_content_referral_blob VARCHAR(107),
        @l_position INTEGER,
        @l_text_ascii VARCHAR(53)

IF NOT @pi_content_referral_blob IS NULL AND LEN(CONVERT(VARBINARY(MAX), @pi_content_referral_blob)) > 187
BEGIN
	SET @l_content_referral_blob = SUBSTRING(CONVERT(VARCHAR(188), CONVERT(VARBINARY(188), @pi_content_referral_blob)), 29, 105)
	SET @l_position = 1
	SET @l_text_ascii = '' 

	WHILE @l_position < LEN(@l_content_referral_blob) + 1
	BEGIN
		SET @l_text_ascii = @l_text_ascii + SUBSTRING(@l_content_referral_blob, @l_position, 1)
		SET @l_position = @l_position + 2
	END
END
ELSE IF @pi_content_referral_blob IS NULL
	SET @l_text_ascii = NULL
ELSE
	SET @l_text_ascii = ''

RETURN @l_text_ascii
END

答案1

得分: 5

这整个函数是没有意义的。你可以简化为以下几个步骤: image -> varbinary(max) -> nvarchar(max) -> varchar(max)

SELECT
  CAST(
    CAST(
      CAST(
        YourImageColumn
        AS varbinary(max)
      )
      AS nvarchar(max)
    )
    AS varchar(max)
  )
FROM ...

请注意,image 数据类型在许多年前就已被弃用,你可以安全地将所有列转换为 varbinary(max)

如果你真的真的想在这里使用一个函数,你可以将它转换为一个表值函数,并使用 GENERATE_SERIESSUBSTRING 来拆分字符。

CREATE OR ALTER FUNCTION dbo.ConvertToVarchar(@value varbinary(max))
RETURNS TABLE
AS RETURN

SELECT
  Result =
    STRING_AGG(
      CAST(SUBSTRING(@value, n.value, CAST(1 AS bigint)) AS varchar(max))
      , '') WITHIN GROUP (ORDER BY n.value)
FROM GENERATE_SERIES(CAST(1 AS bigint), LEN(@value) - 1, CAST(2 AS bigint)) n;
英文:

This entire function is pointless. You can just cast in a number of steps: image -> varbinary(max) -> nvarchar(max) -> varchar(max)

SELECT
  CAST(
    CAST(
      CAST(
        YourImageColumn
        AS varbinary(max)
      )
      AS nvarchar(max)
    )
    AS varchar(max)
  )
FROM ...

db<>fiddle

Note that the image datatype was deprecated many years ago, and you can safely convert all columns to varbinary(max).


If you really really wanted to use a function here, you could turn it into a Table Valued Function, and use GENERATE_SERIES and SUBSTRING to break out the characters.

CREATE OR ALTER FUNCTION dbo.ConvertToVarchar(@value varbinary(max))
RETURNS TABLE
AS RETURN

SELECT
  Result =
    STRING_AGG(
      CAST(SUBSTRING(@value, n.value, CAST(1 AS bigint)) AS varchar(max))
      , &#39;&#39;) WITHIN GROUP (ORDER BY n.value)
FROM GENERATE_SERIES(CAST(1 AS bigint), LEN(@value) - 1, CAST(2 AS bigint)) n;

huangapple
  • 本文由 发表于 2023年7月3日 21:11:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76605089.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定