Answered by:
Replace WHILE LOOP

-
What would better replace the while loop down here? This code is part of a SP and the performance is so bad that I have to find a way to replace it by some other script. Any ideas gurus?
SELECT
cluster_id,name,parent_cluster_id,0,@processId FROM #tblCluster WITH(NOLOCK);
SET @row_count = @@rowcount
SET @rCount = 1;
WHILE(@rCount <= @row_count)
BEGIN
SET @documentList = NULL
SELECT @clusterId = cluster_id, @documentList = documentlist
FROM #tblCluster WITH (nolock)
WHERE cluster_id = @rCount;
IF @documentList is not NULL and @documentList <> ''
BEGIN
INSERT INTO stg_cluster_documentAAA(cluster_id,document_id,is_loaded,process_id)
SELECT @clusterId , i.document_id ,0, @processId
FROM dbo.NvarcharListToTable(@documentList,',') AS t
INNER JOIN imp_system AS i WITH(NOLOCK)
ON t.number = i.external_document_id
END
Set @rCount = @rCount + 1;
END
set @returnCode = 0;
ebro
Question
Answers
-
Hello ebro,
I don't have your database, so I can't test it. But the statement below should comes near to a set based solution to replace the complete code; whereby I don't understand the usage of the table valued function "NvarcharListToTable"; seems the result isn't used in any way???
INSERT INTO stg_cluster_documentAAA (cluster_id,document_id,is_loaded,process_id) SELECT CL.cluster_id ,i.document_id ,0 , @processId FROM #tblCluster AS CL CROSS APPLY dbo.NvarcharListToTable(CL.documentlist ,',') AS t INNER JOIN imp_system AS i WITH(NOLOCK) ON t.number = i.external_document_id
Olaf Helper
Blog Xing- Marked as answer by ebrolove Friday, July 26, 2013 5:16 PM
All replies
-
-
Hello ebro,
I don't have your database, so I can't test it. But the statement below should comes near to a set based solution to replace the complete code; whereby I don't understand the usage of the table valued function "NvarcharListToTable"; seems the result isn't used in any way???
INSERT INTO stg_cluster_documentAAA (cluster_id,document_id,is_loaded,process_id) SELECT CL.cluster_id ,i.document_id ,0 , @processId FROM #tblCluster AS CL CROSS APPLY dbo.NvarcharListToTable(CL.documentlist ,',') AS t INNER JOIN imp_system AS i WITH(NOLOCK) ON t.number = i.external_document_id
Olaf Helper
Blog Xing- Marked as answer by ebrolove Friday, July 26, 2013 5:16 PM
-
The SP is basically meant to shred an XML and then insert that to a table in SQL server. After that the code above comes in and starts its magic that I am not even clear. I actually was seeking help to understand what the while loop is doing in the code above.
ebro
-