英文:
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_SERIES
和 SUBSTRING
来拆分字符。
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 ...
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))
, '') WITHIN GROUP (ORDER BY n.value)
FROM GENERATE_SERIES(CAST(1 AS bigint), LEN(@value) - 1, CAST(2 AS bigint)) n;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论