Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on sql server 2017 i need to ask
are separate update will be best or do all update on one step ?

so using only one update to update all columns
or
using only one update for every column update ?

What I have tried:

UPDATE  TT
           SET     TT.strSubstances = CAST (STUFF(( SELECT ','+ CAST(CC.Substance AS VARCHAR(3500))
                                                    FROM   Parts.ChemicalProfiles CC with(nolock)
                                                    WHERE  CC.ChemicalID = TT.ChemicalID
                                                    ORDER BY CC.Substance
                                                  FOR
                                                    XML PATH('')
                                                  ), 1, 1, '') AS NVARCHAR(3500)) ,
                   TT.strMass = CAST (STUFF(( SELECT   ','
                                                       + convert(VARCHAR(3500),CC.Mass,128)--CAST(CC.Mass AS VARCHAR(3500))
                                              FROM     Parts.ChemicalProfiles CC with(nolock)
                                              WHERE    CC.ChemicalID = TT.ChemicalID
                                              ORDER BY CC.Mass
                                            FOR
                                              XML PATH('')
                                            ), 1, 1, '') AS NVARCHAR(3500)),
                  TT.strCASNumber = CAST (STUFF(( SELECT   ','
                                                       + CAST(CC.CASNumber AS VARCHAR(3500))
                                              FROM     Parts.ChemicalProfiles CC with(nolock)
                                              WHERE    CC.ChemicalID = TT.ChemicalID
                                              ORDER BY CC.CASNumber
                                            FOR
                                              XML PATH('')
                                            ), 1, 1, '') AS NVARCHAR(3500)),

                   TT.strHomogeneousMaterialName = CAST (STUFF(( SELECT   ','
                                                       + CAST(CC.HomogeneousMaterialName AS VARCHAR(3500))
                                              FROM     Parts.ChemicalProfiles CC with(nolock)
                                              WHERE    CC.ChemicalID = TT.ChemicalID
                                              ORDER BY CC.HomogeneousMaterialName
                                            FOR
                                              XML PATH('')
                                            ), 1, 1, '') AS NVARCHAR(3500)),
                                            --strHomogeneousMaterialMass
                   TT.strHomogeneousMaterialMass = CAST (STUFF(( SELECT   ','
                                                       + convert(VARCHAR(3500),CC.HomogeneousMaterialMass,128)--CAST(CC.Mass AS VARCHAR(3500))
                                              FROM     Parts.ChemicalProfiles CC with(nolock)
                                              WHERE    CC.ChemicalID = TT.ChemicalID
                                              ORDER BY CC.HomogeneousMaterialMass
                                            FOR
                                              XML PATH('')
                                            ), 1, 1, '') AS NVARCHAR(3500))
           FROM    #ChemeicalIDCounts TT

OR
DO separate update as below
   UPDATE  TT
            SET     TT.strSubstances = CAST (STUFF(( SELECT ','+ CAST(CC.Substance AS VARCHAR(3500))
                                                     FROM   Parts.ChemicalProfiles CC with(nolock)
                                                     WHERE  CC.ChemicalID = TT.ChemicalID
                                                     ORDER BY CC.Substance
                                                   FOR
                                                     XML PATH('')
                                                   ), 1, 1, '') AS NVARCHAR(3500)) 
            FROM    #ChemeicalIDCounts TT

			 UPDATE  TT
            SET     TT.strMass = CAST (STUFF(( SELECT   ','
                                                        + convert(VARCHAR(3500),CC.Mass,128)--CAST(CC.Mass AS VARCHAR(3500))
                                               FROM     Parts.ChemicalProfiles CC with(nolock)
                                               WHERE    CC.ChemicalID = TT.ChemicalID
                                               ORDER BY CC.Mass
                                             FOR
                                               XML PATH('')
                                             ), 1, 1, '') AS NVARCHAR(3500))
            FROM    #ChemeicalIDCounts TT

etc..
Posted
Updated 9-May-22 21:40pm

1 solution

Here is an article on checking how long quieries take: Usage details of the SET STATISTICS TIME ON statement in SQL Server[^]

Run both sets of queries and compare the results.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900