0

We are trying to save result of a query in database by tokenizing a column but query is taking more than 3 minutes to finish his objective. My question is, do we have any faster way to tokenize a column?

Query:

Select *
from
(
        SELECT TblTwo.ID Desc_ID,
           (CASE WHEN TblTwo.Description_Type = 'Default' THEN coalesce(tblOne.Code + ' ','') ELSE '' END) + TblTwo.[Description] AS String   
        FROM TableOne tblOne
        INNER JOIN TableTwo TblTwo ON tblOne.CodeID = TblTwo.CodeID
        WHERE TblTwo.Description_Type IN ('Default','Custom')
)ResultOne   ---- Return almost 20000 rows and take 1 sec to execute 
CROSS APPLY dbo.udf_TokenString(String) ---- return almost 194379 rows and take 3 mins to execute

Result of drived table ResultOne :
Desc_ID String
1 HCD.39877
2 HCD.39777 Personal test history of documents
4 CD.397 Unspecified document text
5 N.7 This is end of file

Script of function:

CREATE FUNCTION [dbo].udf_TokenString (@string VARCHAR(MAX))
RETURNS
@Results TABLE
(
    Item VARCHAR(255),
    location INT,
    Sequence INT IDENTITY PRIMARY KEY
)
AS
BEGIN


    DECLARE @Len INT, @Start INT, @end INT, @Cursor INT,@length INT

    SELECT @Cursor=1, @len=LEN(@string)

    WHILE @cursor<@len
       BEGIN

       SELECT @start=PATINDEX('%[^.A-Za-z0-9][A-Za-z0-9%]%',
                       ' '+SUBSTRING (@string,@cursor,50)
                       )-1

       IF @start<0 break               

       SELECT @length=PATINDEX('%[^.A-Z''a-z0-9-%]%',SUBSTRING (@string,@cursor+@start+1,50)+' ')  
       INSERT INTO @results(item, location)
           SELECT  SUBSTRING(@string,@cursor+@start,@length), @cursor+@start
       SELECT @Cursor=@Cursor+@Start+@length+1

       END
    RETURN

END


GO
  • 2
    Yeah, that is about the slowest way to split strings. See sqlperformance.com/2012/07/t-sql-queries/split-strings or, better yet, stop storing these awful comma-separated strings in the first place : sqlperformance.com/2012/08/t-sql-queries/… – Aaron Bertrand Oct 21 '15 at 11:32
  • @AaronBertrand actually source is a table which we get from a third party which contain code and their descriptions. What we do is we store those descriptions after splitting and its per day as many times as client wants activity, therefore i need a better solution – AA.SC Oct 21 '15 at 11:51
  • If you have to split the string then the absolute fastest way would be to use a CLR and reference a .NET library to do it. SQL is just going to suck at string manipulation. Or use the import process (SSIS?, other?) to properly normalize the data prior to putting it in your database. – Jonathan Fite Oct 21 '15 at 14:46
  • @AA.SC Why are you using PATINDEX('%[^.A-Za-z0-9][A-Za-z0-9%]%', ? Do you have multiple delimiters? Do you really have a variable number of these 1 or more delimiters in a row? Meaning, is your incoming data, if splitting on a space, sometimes separated by 3 spaces or 5 or a ? or a ^? I can't see any other purpose in using that PATINDEX construct unless this is the case. – Solomon Rutzky Oct 22 '15 at 1:49