question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked BertZhoumsft-7490 commented

slow execution WHEN update chemical id on table chemicalhash so how to enhance it ?

I work on sql server 2017 i need update chemical id on table chemicalhash based on 3 columns compare to table fmd chemical

compare 3 columns strSubstances,strMass,strCASNumber from table chemicalhash

with 3 columns strSubstances,strMass,strCASNumber from table fmdchemical to get chemical id

but issue slow so how to enhance it



 CREATE TABLE [dbo].[chemicalhash](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [ChemicalID] [int] NULL,
     [strSubstances] [nvarchar](max) NULL,
     [strMass] [nvarchar](max) NULL,
     [strCASNumber] [nvarchar](max) NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
 GO
 SET IDENTITY_INSERT [dbo].[chemicalhash] ON 
    
 INSERT [dbo].[chemicalhash] ([id], [ChemicalID], [strSubstances], [strMass], [strCASNumber]) VALUES (1, NULL, N'3P04,Al2O3,C,C,Cr203,Cu,Cu,Fe,Mn,Others,Others,Others,Others,Pb,Si02,SiC,Silicone Resin,SiO2,Sn,Sn,Ti02', N'0.0004,0.0096,0.336,0.3992,0.5904,0.764,0.7904,1.5408,1.6176,1.6808,1.6808,2.9344,5.2392,7.2,7.9608,19.2072,32.08,65.3584,148.3384,232.7992,269.472', N'592910,592960,592961,593043,593043,593259,593290,593306,593306,593495,593501,593501,593505,593505,621442,621442,621442,621442,622089,624020,650559')
 INSERT [dbo].[chemicalhash] ([id], [ChemicalID], [strSubstances], [strMass], [strCASNumber]) VALUES (2, NULL, N'3P04,Al2O3,C,C,Cr203,Cu,Cu,Fe,Mn,Others,Others,Others,Others,Pb,Si02,SiC,Silicone Resin,SiO2,Sn,Sn,Ti02', N'0.0004,0.0096,0.336,0.3992,0.5904,0.764,0.7904,1.5408,1.6176,1.6808,1.6808,2.9344,5.2392,7.2,7.9608,19.2072,32.08,65.3584,148.3384,232.7992,269.472', N'592910,592960,592961,593043,593043,593259,593290,593306,593306,593495,593501,593501,593505,593505,621442,621442,621442,621442,622089,624020,650559')
 SET IDENTITY_INSERT [dbo].[chemicalhash] OFF


Second table

 CREATE TABLE [dbo].[fmdchemical](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [chemicalid] [int] NULL,
     [StrSubstance] [nvarchar](3500) NULL,
     [strmass] [nvarchar](3500) NULL,
     [strcasnumber] [nvarchar](3500) NULL
 ) ON [PRIMARY]
    
 GO
 SET IDENTITY_INSERT [dbo].[fmdchemical] ON 
    
 INSERT [dbo].[fmdchemical] ([id], [chemicalid], [StrSubstance], [strmass], [strcasnumber]) VALUES (826807, 748787, N'3P04,AL2O3,C,C,Cr203,Cu,Cu,Fe,Mn,Others,others,others,Others,Pb,Si02,SiC,Silicone Resin,SiO2,Sn,Sn,Ti02', N'0.0004,0.0096,0.336,0.3992,0.5904,0.764,0.7904,1.5408,1.6176,1.6808,1.6808,2.9344,5.2392,7.2,7.9608,19.2072,32.08,65.3584,148.3384,232.7992,269.472', N'592910,592960,592961,593043,593043,593259,593290,593306,593306,593495,593501,593501,593505,593505,621442,621442,621442,621442,622089,624020,650559')
 SET IDENTITY_INSERT [dbo].[fmdchemical] OFF

slow on this update statment

 update h set h.chemicalid=f.chemicalid from chemicalhash h
             inner join fmdchemical f on h.strsubstances=f.StrSubstance and h.strmass=f.strmass and h.strcasnumber=f.strcasnumber
sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ahmedsalah-1628

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Bert Zhou

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered ahmedsalah-1628 edited
   merge chemicalhash h
   using fmdchemical f on h.strsubstances=f.StrSubstance and h.strmass=f.strmass and h.strcasnumber=f.strcasnumber
  when matched then 
  update set h.chemicalid=f.chemicalid ;
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

are there are any way like hashbyte will be enhance it
or no need

as

first table

  UPDATE  chemicalhash
             SET     HashSub = HASHBYTES('SHA2_512',StrSubstance),
                     HashMass = HASHBYTES('SHA2_512',strMass),
                     HashCASNumber = HASHBYTES('SHA2_512',strcasnumber)

second table

   UPDATE  fmdchemical
                 SET     HashSub = HASHBYTES('SHA2_512',StrSubstance),
                         HashMass = HASHBYTES('SHA2_512',strMass),
                         HashCASNumber = HASHBYTES('SHA2_512',strcasnumber)


            

 update h set h.chemicalid=f.chemicalid from chemicalhash h
             inner join fmdchemical f on h.HashSub=f.HashSub and h.HashMass=f.HashMass and h.HashCASNumber=f.HashCASNumber



are this logic will be faster according to my case
because every table have 6 milion rows

0 Votes 0 ·
BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered

Hi,@ahmedsalah-1628
Welcome to Microsoft T-SQL Q&A Forum!

Have you tried creating a nonclustered index for the linked column? Like this:

 CREATE NONCLUSTERED INDEX IX_Index
 ON [dbo].[fmdchemical](chemicalid)INCLUDE(StrSubstance,strmass,strcasnumber)

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.