2

I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.

Below is what I am working with:

CREATE TABLE #SampleTable
(
SampleRowID INT IDENTITY(1,1),
SampleUserNumber INT,
SampleLineNumber INT,
SampleTextLine VARCHAR(1000)
)

Sample Data

Here is my CTE Query:

CTE Query

Here is the Output:

Result

The CTE Query works and the result is correct, but my main issue is the performance and the amount of records I have to process. Right now I am dealing with 100,000 records at a bare minimum.

Questions:

  1. Have I written the query incorrectly to handle 100,000 records? Is there a more efficient way to write the CTE?

  2. Is there another way more efficient and better way to do this? [looping would be out of the question unless I have no other choice]

Sorry for all the images. I figured it would be much easier to show everything rather than write it out here.

Thank you.

  • 2
    You are better off to use for xml path or clr to see perf gain. CTE does in any way speed up perf. See this as well - stackoverflow.com/questions/13639262/… – Kin Shah Apr 17 at 15:18
  • Also please just put the query text in your question. We are more than happy to help you with formatting, but nobody is going to transcribe your query from a screen shot in order to compare performance or validate correct results. And don't discount loops (or really, anything) out of hand. Generally they are poor performers, but there are some cases where they are the best option. "It depends" applies to almost everything in SQL Server. – Aaron Bertrand Apr 17 at 15:40
  • @Kin I will check this out thank you! I did not see your reply or the user below you. I was scrolled down to the bottom. – HelloWorld2099 Apr 17 at 17:50
  • @AaronBertrand I did try a loop but I found that it was taking way to long, but I do agree with your statement "it depends". I was just looking for alternatives and better options. That is the only reason I did not paste the code directly due to formatting and I did not want to burden anyone with the formatting aspect. – HelloWorld2099 Apr 17 at 17:52
1

How does this approach do in comparison?

SELECT 
  SampleUserNumber, 
  CombinedMessage = STUFF(
    (SELECT N' ' + SampleTextLine FROM #SampleTable
       WHERE SampleUserNumber = t.SampleUserNumber
       ORDER BY SampleLineNumber
       FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
FROM #SampleTable AS t
GROUP BY SampleUserNumber
ORDER BY SampleUserNumber;

It would probably be useful for your #temp table to have a clustered index on SampleUserNumber, SampleLineNumber or at least a non-clustered on those two columns that INCLUDEs SampleTextLine.

In SQL Server 2017 you would be able to use a much more straightforward approach, almost guaranteed to be faster:

SELECT SampleUserNumber, 
  CombinedMessage = STRING_AGG(CONVERT(varchar(max),SampleTextLine), ' ') 
                    WITHIN GROUP (ORDER BY SampleLineNumber)
FROM #SampleTable
GROUP BY SampleUserNumber 
ORDER BY SampleUserNumber;

To avoid the CONVERT you could just make your #temp table column a MAX type.

  • The XML approach worked like a charm! Thank you! I have not tried the clustered index approach just yet, but I will see what happens in terms of performance. Lastly, unfortunately I am not using 2017 or have access to it but I did read up on the STRING_AGG function. Thank you yet again! – HelloWorld2099 Apr 17 at 17:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.