none
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

    Friday, July 26, 2013 2:40 PM

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
    Friday, July 26, 2013 2:53 PM

All replies

  • Hi,

    Can you pls let me know your requirement pls.

    -Sugumar Pannerselvam.

    Friday, July 26, 2013 2:47 PM
  • 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
    Friday, July 26, 2013 2:53 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

    Friday, July 26, 2013 2:54 PM
  • 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

    Friday, July 26, 2013 5:16 PM