does the estimated query plan reveal anything? also just as an aside your index is on `AccountId and NormalizedName but your query is on P.BusinessFileId ? is that an 'AccountId' ?
On Wed, Mar 14, 2018 at 5:50 AM Corneliu I. Tusnea <corne...@acorns.com.au> wrote: > 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 > > > > >
