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

Question

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

I would, personally, suggest you need to completely rethink this function. User defined-functions *can* perform quite poorly (when not inlinable) and loops *do* perform poorly in T-SQL (and it also makes your function not inlinable). Using an inline Table Value Function (iTVF) would be a far better solution here, and would likely be *far* more performant. YOu would likely be better off explaining the logic here so we can try and help you write an inlinable set-based solution. — Thom A, Jul 03 '23 at 13:14
Also...the image datatype was deprecated with the release of sql server 2005. — Sean Lange, Jul 03 '23 at 13:16
I know you better describe the problem than the solution. But it's an out of the box software. It stores a certain information this way and I need to search for it over millions of rows. Thank you! — stackedyellowangel, Jul 03 '23 at 13:54

Charlieface · Accepted Answer · 2023-07-03T14:09:48.603

5

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))
      , '') WITHIN GROUP (ORDER BY n.value)
FROM GENERATE_SERIES(CAST(1 AS bigint), LEN(@value) - 1, CAST(2 AS bigint)) n;

edited Jul 03 '23 at 14:09

answered Jul 03 '23 at 13:22

Charlieface

52,284
6
19
43

Thank you, it works! I fiddled around with these data types but could not get it to work. I know that image is deprecated but it's an out of the box software. – stackedyellowangel Jul 03 '23 at 13:52
1 mio. rows in 38 seconds now! And I can drop the scalar function ! – stackedyellowangel Jul 03 '23 at 13:58
You could probably change the data type of the column without the software noticing, it's perfectly compatible in almost every case. The only time it would ever have an issue is if it was looking at the column schema and specifically searching for `image` as opposed to `varbinary`. But most client libraries map `image` to a byte array, os if it's looking for that it won't see a difference. – Charlieface Jul 03 '23 at 13:59
1 mio. rows in 29 seconds without the scalar function! – stackedyellowangel Jul 03 '23 at 14:00

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

1 Answers1