Hello Try reducing the size of the input and output varchars to the bare minimum required.Then try:Remove the computer column and update a real field either by batch, sp or in the worst case a trigger Without an active profiler I'm guessing that the query optimizer is failing to reorder your where clause, because the computer column is too complex or there isn't an index for the field you are querying, businessfieldid, or a compound one. The index in the mail is for accountid and normalizedname Reorder the clause to be most restrictive at the end of the query . SQL is read right to left by most engines. Hope this helps Davy
Sent from my Samsung Galaxy smartphone. -------- Original message --------From: "Corneliu I. Tusnea" <[email protected]> Date: 13/03/2018 22:50 (GMT+01:00) To: ozDotNet <[email protected]> Subject: Fwd: Very slow query on Persisted Column with Function call 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 SCHEMABINDINGASBEGIN 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 @TempEND 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) PERSISTEDEND 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
