1

Hi I have a view which is used in lots of search queries in my application. The issue is application queries which use this view is is running very slow.I am investigating this and i found out a particular portion on the view definition which is making it slow.

create view Demoview AS

Select 
    p.Id as Id,
    ----------,
    STUFF((SELECT ',' + [dbo].[OnlyAlphaNum](colDesc)
        FROM dbo.ContactInfoDetails cd
        WHERE pp.FormId = f.Id AND ppc.PageId = pp.Id 
        FOR XML PATH('')), 1, 1, '') AS PhoneNumber,
    p.FirstName as Fname,
From
---

This is one of the column in the view. The scalar function [OnlyAlphaNum] is making it slow,as it stops parallel execution of the query.

The function is as below;

CREATE FUNCTION [dbo].[OnlyAlphaNum]
(

@String VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN

   WHILE PATINDEX('%[^A-Z0-9]%', @String) > 0

        SET @String = STUFF(@String, PATINDEX('%[^A-Z0-9]%', @String), 1, '')

RETURN @String

END

How can i convert it into an inline function.? I tried with CASE ,but not successful.I learned that while loop functions cannot me converted to ITVF.It can be converted to MTVF.Another option is Tally Table which i have no idea about. Can someone help me to convert it to TVF?

  • There are a couple examples here: DigitsOnlyEE and AlphaNumericOnly – Erik Darling Jun 27 '18 at 22:34
  • Refactoring that function is not your only problem. The WHERE clause for the subquery on ContactInfoDetails does not reference any columns from ContactInfoDetails (alias "cd"), so you would get all rows concatenated. – Mister Magoo Jun 27 '18 at 23:07
  • 1
    Why not put an extra column in the table that is populated with the result of the function (either by a computed column or at write time)? This column could be indexed and then your query doesn't need a function at all. – Aaron Bertrand Jun 27 '18 at 23:20
  • Hi @sp_BlitzErik : Thanks much.This is all i want.I tried it with a sample database and it worked.When i try to use it at workplace i get this Cannot find either column "dbo" or the user-defined function or aggregate "dbo.AlphaNumericOnly", or the name is ambiguous. I have the tvf in my user database and i can run it against a literal value and if i use that inside my view column it gives this error. – user9516827 Jun 28 '18 at 13:57
  • 1
    You should ask a new question and post the code that's causing the error. Sadly, I can't see your computer screen from here. – Erik Darling Jun 28 '18 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.