Post
Ask Question
ahmed salah

ahmed salah

  • 1k
  • 688
  • 43.4k

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

May 17 2022 2:29 AM

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

 


Answers (1)