Replace WHILE LOOP

Question

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

Answer 1

Hi,

Can you pls let me know your requirement pls.

-Sugumar Pannerselvam.

Answer 2

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

Answer 3

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

Answer 4

Oh thanks man.... it was a set based approach I needed. I guess this will be the right way. I will test it and report back.

---

ebro