1

I'm working with a database where the details of a phone call a user places with a client are saved into a single VARCHAR(MAX) column. For detailed reporting purposes (SSRS), however, all of the various attributes embedded within the VARCHAR column need to be turned into separate columns. To accomplish this parsing, I'm using a cursor in a stored procedure which calls a UDF that handles the extraction of the desired substring.

When querying a relatively small number of rows (no more than a few hundred), the performance of the query is decent. However, when querying a larger number of rows (several thousand), performance is terrible and the query will often time out. So, I'm looking for an approach that scales far better than what I have in place.

The VARCHAR column contains full HTML markup. I've removed irrelevant stuff to illustrate how the data I'm interested in is stored:

<table>
<tr>
<td class="key">Dial Result:</td><td id="DialResult" class="value">No Answer</td></tr>
<tr><td class="key">Client Name:</td><td id="ClientName" class="value">SMITH, BOB</td></tr>
<tr><td class="key">Number Dialed:</td><td id="NumberDialed" class="value">5555555555 [Day]</td></tr>
<tr><td class="key">Dial Count:</td><td id="DialCount" class="value">1</td></tr><tr><td class="key">Contact Made:</td><td id="ContactMade" class="value">No</td></tr>
<tr><td class="key">Campaign Called On:</td><td id="CampaignCalledOn" class="value">TEST CAMPAIGN</td></tr>
<tr><td class="key">Call Outcome:</td><td id="CallOutcome" class="value">No answer</td></tr>
<tr><td class="key">Email Sent:</td><td id="EmailSent" class="value">No</td></tr>
<tr><td class="key">Do Not Call Requested:</td><td id="DoNotCallRequested" class="value">No</td></tr>
<tr><td class="key">Product Purchased:</td><td id="ProductPurchased" class="value">No</td></tr>
<tr><td class="key">Order Number:</td><td id="OrderNumber" class="value">N/A</td></tr><tr><td class="key">Order Dollar Value:</td><td id="PurchaseAmount" class="value">0.00</td></tr>
<tr><td class="key">Purchased SKUs:</td><td id="PurchasedSKUs" class="value">N/A</td></tr>
</table>

Currently, I have a stored procedure which uses a cursor to iterate over rows in the "live" database table. The procedure cursor looks like this:

OPEN MainNoteCursor;
FETCH NEXT FROM MainNoteCursor INTO @SequenceNumber,@ClientId,@ContactNumber,@UserDisplayName,@CreatorId,@DateCol,@NoteText

WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
            BEGIN TRY
                DECLARE @NoteDate date = null
                DECLARE @DialResult varchar(255) = null
                DECLARE @ClientName varchar(255) = null
                DECLARE @NumberDialed varchar(255) = null
                DECLARE @DialCount int = null
                DECLARE @ContactMade varchar(3)  = null
                DECLARE @CampaignCalledOn varchar(255)  = null
                DECLARE @CallOutcome varchar(255) = null
                DECLARE @EmailSent varchar(3) = null
                DECLARE @DoNotCallRequested varchar(3) = null
                DECLARE @ProductPurchased varchar(3) = null
                DECLARE @OrderNumber varchar(255) = null
                DECLARE @PurchaseAmount money = null
                DECLARE @PurchasedSKUs varchar(255) = null

                SET @NoteDate = CONVERT(date, @DateCol)

                SET @DialResult = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="DialResult" class="value">'',''</td>'',1)
                SET @ClientName = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="ClientName" class="value">'',''</td>'',1)
                SET @NumberDialed = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="NumberDialed" class="value">'',''</td>'',1)
                SET @DialCount = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="DialCount" class="value">'',''</td>'',1)
                SET @ContactMade = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="ContactMade" class="value">'',''</td>'',1)
                SET @CampaignCalledOn = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="CampaignCalledOn" class="value">'',''</td>'',1)
                SET @CallOutcome = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="CallOutcome" class="value">'',''</td>'',1)
                SET @EmailSent = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="EmailSent" class="value">'',''</td>'',1)
                SET @DoNotCallRequested = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="DoNotCallRequested" class="value">'',''</td>'',1)
                SET @ProductPurchased = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="ProductPurchased" class="value">'',''</td>'',1)
                SET @OrderNumber = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="OrderNumber" class="value">'',''</td>'',1)
                SET @PurchaseAmount = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="PurchaseAmount" class="value">'',''</td>'',1)
                SET @PurchasedSKUs = [dbo].[FN_PARSE_TEXT] (@NoteText,''<TD id="PurchasedSKUs" class="value">'',''</td>'',1) 

                INSERT INTO @Return
                    ([SequenceNumber],[ClientId],[ContactNumber],[UserDisplayName],[CreatorId],
                    [DateCol],[NoteDate],[NoteText],[DialResult],[ClientName],[NumberDialed],[DialCount],
                    [ContactMade],[CampaignCalledOn],[CallOutcome],[EmailSent],[DoNotCallRequested],
                    [ProductPurchased],[OrderNumber],[PurchaseAmount],[PurchasedSKUs])
                VALUES
                    (@SequenceNumber,@ClientId,@ContactNumber,@UserDisplayName,@CreatorId,
                    @DateCol,@NoteDate,@NoteText,@DialResult,@ClientName,@NumberDialed,@DialCount,
                    @ContactMade,@CampaignCalledOn,@CallOutcome,@EmailSent,@DoNotCallRequested,
                    @ProductPurchased,@OrderNumber,@PurchaseAmount,@PurchasedSKUs)  
            END TRY             

            BEGIN CATCH
                --Nothing to do.
            END CATCH       
        FETCH NEXT FROM MainNoteCursor INTO @SequenceNumber,@ClientId,@ContactNumber,@UserDisplayName,@CreatorId,@DateCol,@NoteText
    END

CLOSE MainNoteCursor;
DEALLOCATE MainNoteCursor;

SELECT * FROM @Return

So, you can see that the cursor is parsing the full note body each time, extracting the values between two string delimiters, inserting the values into a table, and then returning the table.

When executed in SSMS, I get a result that looks like this:

enter image description here

The code in the FN_PARSE_TEXT uses CHARINDEX(), LEN(), SUBSTRING(), etc. to read the text between the "Start string" and the "end string". I won't post the full function because there's a lot of housekeeping code that's not relevant, but it can be distilled down to this:

--********************************************************************************
-- CHARINDEX returns the position of the fist character in @StartKey, but we need to
-- start reading after the *last* character in @StartKey.  Re-adjust the position
-- at which we''ll start reading the string.
--********************************************************************************

SET @StartKeyIndex = @StartKeyIndex + @StartKeyLength
SET @ReadLength = @EndKeyIndex - @StartKeyIndex

--********************************************************************************
-- Start / End positions have been determined, so now read out the calculated number
-- of characters and return to the calling code.
--********************************************************************************

SET @ReturnValue = LTRIM(RTRIM(SUBSTRING(@noteText, @StartKeyIndex, @ReadLength)))

So, after all of that background information (I can provide more detail, if needed), I'm looking for a better approach.

I've thought of creating an overnight batch process that does all of the parsing during off-hours and then dumping the data into a flattened table. I would then report of the flattened data instead of trying to parse out the details in real time. This, of course, opens its own can of worms and I'd ideally like to continue to hit the live data. Depending on the date range the users supply when running the query, upwards of 10,000 rows may be returned.

Another alternative I've thought of would be to use a CLR method to handle the looping and parsing - but I don't have a lot of experience there and I'm not sure that would be any more efficient than what I'm doing now.

I have read articles (https://sqlperformance.com/2012/07/t-sql-queries/split-strings - for example) that talk about things like CTE, but I don't really have much experience there, so I'm not sure about how to make the leap. Most of the "string parsing" articles I've read are geared towards scenarios where the delimiter is the same - things like parsing comma-separated strings. But since I have "rolling" delimiters, I'm not sure how to deal with that.

Anyway, if you're still with me, any input would be greatly appreciated.

Improve this question
5
  • Ahh combinatorial explosion. Can you cast the HTML as XML and use xml methods to extract the data you need? Commented Sep 28, 2017 at 19:08
  • Are you allowed to make any DDL changes to the data base? Add columns or tables? Commented Sep 28, 2017 at 19:19
  • @TabAlleman - Technically, yes. However, this is a customization to a CRM application, so I don't have complete and total control over every aspect of this scenario. But, I would be interested in what you're thinking along those lines... Commented Sep 28, 2017 at 19:29
  • 1
    SQL has native XML manipulation functions. There's no need to build a CLR or mess with string manipulation functions. Cast NoteText as xml, then use XML methods to shred the values you need. learn.microsoft.com/en-us/sql/t-sql/xml/xml-data-type-methods Commented Sep 28, 2017 at 19:36
  • @Xedni - I'm looking into this and I've gotten it to work in terms of extracting the values from my <td> tags using the "id" attribute - @xml.value('(/html/body/table/tr/td[@id="ClientName"])[1]', 'varchar(max)' ) as ClientName, But won't I still need a cursor to loop over all of the results? Does casting as XML reduce the complexity of iterating over 10,000 rows when compared to my manual string parsing function? I'm happy to give it a try, but don't want to change for the sake of changing without there being an obvious performance boost. Commented Sep 28, 2017 at 20:05

3 Answers 3

Reset to default
2

Just to offer a tangible example of how you can do this with XML, here's how I'd do it. I only have one sample document to go off of here, but if you had multiple, the same would apply. You'd just get back multiple rows for each primary key.

;with src (Id, NoteText) as
(
    select 1, cast('<table>
<tr>
<td class="key">Dial Result:</td><td id="DialResult" class="value">No Answer</td></tr>
<tr><td class="key">Client Name:</td><td id="ClientName" class="value">SMITH, BOB</td></tr>
<tr><td class="key">Number Dialed:</td><td id="NumberDialed" class="value">5555555555 [Day]</td></tr>
<tr><td class="key">Dial Count:</td><td id="DialCount" class="value">1</td></tr><tr><td class="key">Contact Made:</td><td id="ContactMade" class="value">No</td></tr>
<tr><td class="key">Campaign Called On:</td><td id="CampaignCalledOn" class="value">TEST CAMPAIGN</td></tr>
<tr><td class="key">Call Outcome:</td><td id="CallOutcome" class="value">No answer</td></tr>
<tr><td class="key">Email Sent:</td><td id="EmailSent" class="value">No</td></tr>
<tr><td class="key">Do Not Call Requested:</td><td id="DoNotCallRequested" class="value">No</td></tr>
<tr><td class="key">Product Purchased:</td><td id="ProductPurchased" class="value">No</td></tr>
<tr><td class="key">Order Number:</td><td id="OrderNumber" class="value">N/A</td></tr><tr><td class="key">Order Dollar Value:</td><td id="PurchaseAmount" class="value">0.00</td></tr>
<tr><td class="key">Purchased SKUs:</td><td id="PurchasedSKUs" class="value">N/A</td></tr>
</table>' as xml)
)

select 
    a.*,
    t.c.value('td[1]', 'varchar(max)')
from src a
cross apply a.NoteText.nodes('table/tr') as t(c)

Documentation for XML shredding in SQL can be a little hit or miss, mostly because SQL is implementing xquery which is independent of both SQL and Microsoft. That said, for doing basic stuff, generally the MSDN documentation gets you started pretty well.

Here are a couple good articles

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for this - I like the approach. I commented on the original post, but I'll reply here as well. In a scenario where I will have several thousand rows to parse at the same time, would a cursor be the best way to handle that? Like, I should be able to put this in a loop and have it perform better than my manual string parsing function, correct? Hope that makes sense!
I don't think you should need to loop over anything. The only thing that might get you is reduced CPU and memory usage, but at the cost of overal duration and code complexity. I would start doing it all in one go, and only look into batching the parsing if it becomes unmanagable to do it all in a single query.
Yeah, I realized my question about the cursor is irrelevant because this is a CTE, so no looping is needed.
1

If you can make modifications to the database, I would do anything I could to avoid doing this parsing at query time. Especially if this is a query that if performed often by users for something like a CRM application.

I would either add all of these frequently-selected columns to my table, or I would create a separate table for them that can be linked to the primary key of this table.

Then, ideally, I would populate these columns at INSERT time. Preferably by having the INSERT go through a stored procedure, but if necessary, by putting an Insert Trigger on the table.

If I can't do it at INSERT time, then I'd run a job that does the parsing and populates the columns as often as practicality requires/allows.

With most CRM applications, SELECT performance is more critical than INSERT performance, because it impacts multiple rows, while INSERTS usually happen one at a time. So I would try to shift the performance burden there if at all possible.

DISCLAIMER: I've never played with the XML functions that Xedni references in his comments. So I cannot say what the performance difference would be between my suggestion and his.

Improve this answer

3 Comments

There are a few extenuating circumstances due to some design limitations of the underlying database which make this approach a bit more of a headache - but this is likely going to be the approach I'm going to use. The XML parsing suggestions are nice, and elegant, but I'm struggling to handle some of the internal complexities which I omitted from my post.
The XML parsing sounds elegant, but if you've never done it before, and the XML is complex enough, the trade off in learning curve and development time may not be worth it. Up to you to decide where your tipping point is.
I'm going with this as the solution to my problem as the XML-based approaches still left some things to be desired. Although this approach involves more effort, it offers the most robust solution to the problem. During the creation of the Phone Note in the UI customization I've created, I will "mirror" the data to a table that stores the data in the format required by the reports. The reports will then be pointed at this new flattened table, so the queries should run very quickly since there won't be any complex parsing that occurs at run-time.
0

You can use XML to convert your values and after pivot the result 

DECLARE @xml xml = '<table>
<tr>
<td class="key">Dial Result:</td><td id="DialResult" class="value">No Answer</td></tr>
<tr><td class="key">Client Name:</td><td id="ClientName" class="value">SMITH, BOB</td></tr>
<tr><td class="key">Number Dialed:</td><td id="NumberDialed" class="value">5555555555 [Day]</td></tr>
<tr><td class="key">Dial Count:</td><td id="DialCount" class="value">1</td></tr><tr><td class="key">Contact Made:</td><td id="ContactMade" class="value">No</td></tr>
<tr><td class="key">Campaign Called On:</td><td id="CampaignCalledOn" class="value">TEST CAMPAIGN</td></tr>
<tr><td class="key">Call Outcome:</td><td id="CallOutcome" class="value">No answer</td></tr>
<tr><td class="key">Email Sent:</td><td id="EmailSent" class="value">No</td></tr>
<tr><td class="key">Do Not Call Requested:</td><td id="DoNotCallRequested" class="value">No</td></tr>
<tr><td class="key">Product Purchased:</td><td id="ProductPurchased" class="value">No</td></tr>
<tr><td class="key">Order Number:</td><td id="OrderNumber" class="value">N/A</td></tr><tr><td class="key">Order Dollar Value:</td><td id="PurchaseAmount" class="value">0.00</td></tr>
<tr><td class="key">Purchased SKUs:</td><td id="PurchasedSKUs" class="value">N/A</td></tr>
</table>'


Select 
 Tbl.Col.value('@id','varchar(max)') Id
,Tbl.Col.value('.','varchar(max)') Value
FRom
 @xml.nodes('/table/tr/td[@class="value"]') Tbl(Col)  


SELECT 
 DialResult
,ClientName
,NumberDialed
,DialCount
,ContactMade
,CampaignCalledOn
,CallOutcome
,EmailSent
,DoNotCallRequested
,ProductPurchased
,OrderNumber
,PurchaseAmount
,PurchasedSKUs
FROM
(
Select 
 Tbl.Col.value('@id','varchar(max)') Id
,Tbl.Col.value('.','varchar(max)') Value
FRom
 @xml.nodes('/table/tr/td[@class="value"]') Tbl(Col) ) T Pivot 
 (Max(Value) FOR  Id In(DialResult
,ClientName
,NumberDialed
,DialCount
,ContactMade
,CampaignCalledOn
,CallOutcome
,EmailSent
,DoNotCallRequested
,ProductPurchased
,OrderNumber
,PurchaseAmount
,PurchasedSKUs)) p;

Result for first select 

Id                                                 Value
-------------------------------------------------- --------------------------------------------------
DialResult                                         No Answer
ClientName                                         SMITH, BOB
NumberDialed                                       5555555555 [Day]
DialCount                                          1
ContactMade                                        No
CampaignCalledOn                                   TEST CAMPAIGN
CallOutcome                                        No answer
EmailSent                                          No
DoNotCallRequested                                 No
ProductPurchased                                   No
OrderNumber                                        N/A
PurchaseAmount                                     0.00
PurchasedSKUs                                      N/A

Result for second select 

DialResult                                         ClientName                                         NumberDialed                                       DialCount                                          ContactMade                                        CampaignCalledOn                                   CallOutcome                                        EmailSent                                          DoNotCallRequested                                 ProductPurchased                                   OrderNumber                                        PurchaseAmount                                     PurchasedSKUs
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
No Answer                                          SMITH, BOB                                         5555555555 [Day]                                   1                                                  No                                                 TEST CAMPAIGN                                      No answer                                          No                                                 No                                                 No                                                 N/A                                                0.00                                               N/A

(1 row(s) affected)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.