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

ahmed salah 3,216 Reputation points
2022-05-10T07:35:14.687+00:00

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..
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Bert Zhou-msft 3,421 Reputation points
    2022-05-10T08:07:20.81+00:00

    Hi,@ahmed salah

    Welcome to Microsoft T-SQL Q&A Forum!

    do all update on one step---Bulk update is an expensive operation in terms of query cost because a single update operation requires more resources. It also takes time to log the update in the transaction log. Additionally, long-running updates can cause blocking issues for other processes.
    To optimize update operations, you should try to minimize transaction size . Set your own batch size after analyzing the data and environment and see what makes the most sense.
    If you want to learn more about performance tuning issues, follow this link.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments