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)
)
Here is my CTE Query:
Here is the Output:
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:
-
Have I written the query incorrectly to handle 100,000 records? Is there a more efficient way to write the CTE?
-
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.
Best Answer
How does this approach do in comparison?
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 thatINCLUDE
sSampleTextLine
.In SQL Server 2017 you would be able to use a much more straightforward approach, almost guaranteed to be faster:
To avoid the
CONVERT
you could just make your #temp table column aMAX
type.