Fwd: Very slow query on Persisted Column with Function call

[Corneliu I. Tusnea]

Hi,

I have a table with a persisted column that is build using a function call:

This is the function:
CREATE FUNCTION os.RemoveNonAlphaNumericCharacters(
    @Temp NVARCHAR(2048)
)
RETURNS VARCHAR(2048)
WITH SCHEMABINDING
AS
BEGIN

    DECLARE @KeepValues AS NVARCHAR(100)
    SET @KeepValues = '%[^a-z^0-9]%'
    WHILE PatIndex(@KeepValues, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END

and this is the column:
IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = N'NormalizedName' AND
OBJECT_ID = OBJECT_ID(N'dbo.Product'))
BEGIN
    ALTER TABLE dbo.Product
    -- Maximum Index is 900
    ADD NormalizedName AS LEFT(os.RemoveNonAlphaNumericCharacters(Name), 500
)
    PERSISTED
END

Then I have an index on that column:
    CREATE NONCLUSTERED INDEX IX_Product_AccountId_NormalizedName ON
dbo.Product
    (
        AccountId,
        NormalizedName
    ) INCLUDE(Name)
    WITH (ONLINE = ON)

All quite simple really.

When I try to execute a simple search using that column the search is very
very slow and I can see in the list of executing session a call to the os.
RemoveNonAlphaNumericCharacters

SELECT TOP 1 * FROM dbo.Product P WITH(NOLOCK)
WHERE P.BusinessFileId = 51678
AND P.NormalizedName = 'test'   -- should use the index


This is what I see is running:


I'm stuck and I don't know how to fix this atm.
I really expected the function to be called only during the insert (or
maybe updates) and not during the search.

I could remove the calculated column, clean the value in C# before I save
it and remove the function all together but that requires a fair bit of
code changes.

Thoughts?

PS> This is running on an Azure SQL P4 DB.

Thanks,
Corneliu