0

Which will best for performance separate update or do all update on one step ?

 4 months ago
source link: https://www.codeproject.com/Questions/5331615/Which-will-best-for-performance-separate-update-or
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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:

Expand ▼   Copy Code
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

Copy Code
   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..

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK