Scalar function with WHILE loop to inline function

Question

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 have read that CTE is a good option. Any idea how to tackle this problem.?

How about storing the pre-calculated value in another column and use that column directly? If you have a high read to write ratio then it's worth calculating the value at insert/update and store it in a redundant column. — CyberDude
– CyberDude, Commented Jun 27, 2018 at 19:08
That require changes in the application side also right?I am not allowed to make any changes in the application code.Also for fixing a single query application team is not going to approve that.Your suggestion is valid and if it was implemnted earlier,it would have been much easier. — user9516827
– user9516827, Commented Jun 27, 2018 at 19:14
Well you could solve this only on the DB side using triggers. — CyberDude
– CyberDude, Commented Jun 27, 2018 at 19:35

Alan Burstein · Accepted Answer · 2018-06-28 00:28:48Z

I already did this; you can read more about it here.

The function:

CREATE FUNCTION dbo.alphaNumericOnly8K(@pString varchar(8000)) 
RETURNS TABLE WITH SCHEMABINDING AS RETURN
/****************************************************************************************
Purpose:
 Given a varchar(8000) string or smaller, this function strips all but the alphanumeric 
 characters that exist in @pString.

Compatibility: 
 SQL Server 2008+, Azure SQL Database, Azure SQL Data Warehouse & Parallel Data Warehouse

Parameters:
 @pString = varchar(8000); Input string to be cleaned

Returns:
 AlphaNumericOnly - varchar(8000) 

Syntax:
--===== Autonomous
 SELECT ca.AlphaNumericOnly
 FROM dbo.AlphaNumericOnly(@pString) ca;

--===== CROSS APPLY example
 SELECT ca.AlphaNumericOnly
 FROM dbo.SomeTable st
 CROSS APPLY dbo.AlphaNumericOnly(st.SomeVarcharCol) ca;

Programmer's Notes:
 1. Based on Jeff Moden/Eirikur Eiriksson's DigitsOnlyEE function. For more details see:
    http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360

 2. This is an iTVF (Inline Table Valued Function) that performs the same task as a 
    scalar user defined function (UDF) accept that it requires the APPLY table operator. 
    Note the usage examples below and see this article for more details: 
    http://www.sqlservercentral.com/articles/T-SQL/91724/ 

    The function will be slightly more complicated to use than a scalar UDF but will yeild
    much better performance. For example - unlike a scalar UDF, this function does not 
    restrict the query optimizer's ability generate a parallel query plan. Initial testing
    showed that the function generally gets a 

 3. AlphaNumericOnly runs 2-4 times faster when using make_parallel() (provided that you 
    have two or more logical CPU's and MAXDOP is not set to 1 on your SQL Instance).

 4. This is an iTVF (Inline Table Valued Function) that will be used as an iSF (Inline 
    Scalar Function) in that it returns a single value in the returned table and should 
    normally be used in the FROM clause as with any other iTVF.

 5. CHECKSUM returns an INT and will return the exact number given if given an INT to 
    begin with. It's also faster than a CAST or CONVERT and is used as a performance 
    enhancer by changing the bigint of ROW_NUMBER() to a more appropriately sized INT.

 6. Another performance enhancement is using a WHERE clause calculation to prevent 
    the relatively expensive XML PATH concatentation of empty strings normally 
    determined by a CASE statement in the XML "loop".

 7. Note that AlphaNumericOnly returns an nvarchar(max) value. If you are returning small 
    numbers consider casting or converting yout values to a numeric data type if you are 
    inserting the return value into a new table or using it for joins or comparison 
    purposes.

 8. AlphaNumericOnly is deterministic; for more about deterministic and nondeterministic
    functions see https://msdn.microsoft.com/en-us/library/ms178091.aspx

Usage Examples:
--===== 1. Basic use against a literal

 SELECT ao.AlphaNumericOnly 
 FROM samd.alphaNumericOnly8K('xxx123abc999!!!') ao;

--===== 2. Against a table 
 DECLARE @sampleTxt TABLE (txtID int identity, txt varchar(100));
 INSERT @sampleTxt(txt) VALUES ('!!!A555A!!!'),(NULL),('AAA.999');

 SELECT txtID, OldTxt = txt, AlphaNumericOnly
 FROM @sampleTxt st
 CROSS APPLY samd.alphaNumericOnly8K(st.txt);

---------------------------------------------------------------------------------------
Revision History:
 Rev 00 - 20150526 - Inital Creation - Alan Burstein
 Rev 00 - 20150526 - 3rd line in WHERE clause to correct something that was missed
                   - Eirikur Eiriksson
 Rev 01 - 20180624 - ADDED ORDER BY N; now performing CHECKSUM conversion to INT inside
                     the final cte (digitsonly) so that ORDER BY N does not get sorted.
****************************************************************************************/ 
WITH 
E1(N) AS 
(
  SELECT N 
  FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))x(N)
), 
iTally(N) AS 
( 
  SELECT TOP (LEN(ISNULL(@pString,CHAR(32)))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM E1 a CROSS JOIN E1 b CROSS JOIN E1 c CROSS JOIN E1 d 
) 
SELECT AlphaNumericOnly = 
( 
  SELECT SUBSTRING(@pString,CHECKSUM(N),1) 
  FROM iTally 
  WHERE 
     ((ASCII(SUBSTRING(@pString,CHECKSUM(N),1)) - 48) & 0x7FFF) < 10 
  OR ((ASCII(SUBSTRING(@pString,CHECKSUM(N),1)) - 65) & 0x7FFF) < 26 
  OR ((ASCII(SUBSTRING(@pString,CHECKSUM(N),1)) - 97) & 0x7FFF) < 26 
  ORDER BY N
  FOR XML PATH('') 
);

Note the examples in the code comments:

--===== 1. Basic use against a literal    
 SELECT ao.AlphaNumericOnly 
 FROM samd.alphaNumericOnly8K('xxx123abc999!!!') ao;

--===== 2. Against a table 
 DECLARE @sampleTxt TABLE (txtID int identity, txt varchar(100));
 INSERT @sampleTxt(txt) VALUES ('!!!A555A!!!'),(NULL),('AAA.999');

 SELECT txtID, OldTxt = txt, AlphaNumericOnly
 FROM @sampleTxt st
 CROSS APPLY samd.alphaNumericOnly8K(st.txt);

Returns:

AlphaNumericOnly
-------------------
xxx123abc999

txtID       OldTxt        AlphaNumericOnly 
----------- ------------- -----------------
1           !!!A555A!!!   A555A
2           NULL          NULL
3           AAA.999       AAA999

It's the fastest of it's kind. It runs extra fast with a parallel execution plan. To force a parallel execution plan, grab a copy of make_parallel by Adam Machanic. Then you would run it like this:

--===== 1. Basic use against a literal    
 SELECT ao.AlphaNumericOnly 
 FROM dbo.alphaNumericOnly8K('xxx123abc999!!!') ao
 CROSS APPLY dbo.make_parallel();

--===== 2. Against a table 
 DECLARE @sampleTxt TABLE (txtID int identity, txt varchar(100));
 INSERT @sampleTxt(txt) VALUES ('!!!A555A!!!'),(NULL),('AAA.999');

 SELECT txtID, OldTxt = txt, AlphaNumericOnly
 FROM @sampleTxt st
 CROSS APPLY dbo.alphaNumericOnly8K(st.txt)
 CROSS APPLY dbo.make_parallel();

Vinay Manikanda · Accepted Answer · 2018-06-27 20:23:30Z

0

Surely there is scope to improve this. test it out.

   ;WITH CTE AS (
    SELECT (CASE WHEN PATINDEX('%[^A-Z0-9]%', D.Name) > 0
           THEN STUFF(D.Name, PATINDEX('%[^A-Z0-9]%', D.Name), 1, '')
           ELSE D.NAME
           END ) NameString
    FROM @dept D
    UNION ALL
    SELECT STUFF(C.NameString, PATINDEX('%[^A-Z0-9]%', C.NameString), 1, '')
    FROM CTE C
    WHERE PATINDEX('%[^A-Z0-9]%', C.NameString) > 0
    )

Select STUFF((SELECT ',' + E.NameString from CTE E
WHERE PATINDEX('%[^A-Z0-9]%', E.NameString) = 0
FOR XML PATH('')), 1, 1, '') AS NAME

edited Jun 27, 2018 at 20:23

answered Jun 27, 2018 at 19:33

Vinay Manikanda

11 bronze badge

2 Comments

user9516827 Over a year ago

I tried with CASE,but it didn't format the data correctly.When using function ,i get the phone number like 5472154896.But when using case ,i get like 547)215 4896.I think with WHILE it is iterating through the string and doing the stuff,but with CASE it is not.That i what i think.

user9516827 Over a year ago

it is a column inside view,i cannot use CTE in there.If i can alter the view by adding inline table valued function it will satisfy my requirement i think so.

Collectives™ on Stack Overflow

Scalar function with WHILE loop to inline function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related