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

Question

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

May 17, 2022 at 2:30 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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24384 More actions · Answer 1

Based on your terrible table designs, I cant think of anything that will help you.

Things that immediately stand out as what will hurt

The field sizes are way too big for SQL to index them. The max size for an index is 1700 bytes, which would be nvarchar(850).
SQL does not perform well with string comparisons.
CSV lists stored in a filed are a bad idea. "a,b,c,d" <> "a,c,b,d"

How to post data/code on a forum to get the best help.

ratbak Ten Centuries Points: 1358 More actions · Answer 2

I'd start by reviewing data types -- Do you really have unicode data that requires nvarchar? And do you really need max size (not inherently bad if strings really are typically long and/or get updated).

CAS numbers are only up to 10 characters, and don't appear to be unicode. Why is strCASNumber nvarchar(max) or nvarchar(3500)?
What is strMass? The name implies it would be a numeric value.
As already mentioned, using a column for lists is questionable. You might be better off with an association table, though that probably complicates comparison.

You have a table named chemicalhash, but ironically are not using any computed hash columns to resolve the issue with indexing string columns too long to be indexed. You can create computed columns that are hashes of long string columns, and create indexes on the compact computed columns (would have to exist in both tables being compared to get full benefit).

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Does the ChemicalHash table ONLY contain rows where the ChemicalID is NULL or does it have rows where the ChemicalID is not null?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

p,s. And, yes... I understand what you're trying to do and maybe even why. I just need for you to answer my question about the ChemicalID nulls/non-nulls above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 5

thank you for interset

what ou need to understand i will answer you

ratbak Ten Centuries Points: 1358 More actions · Answer 6

I suspect Jeff wants you to answer THIS (and in fact, any) question:

https://www.sqlservercentral.com/forums/topic/slow-execution-when-update-chemical-id-on-table-chemicalhash-so-how-to-enhance-i#post-4034307

Jeff Moden, May 17, 2022 at 1:10 pm:

Does the ChemicalHash table ONLY contain rows where the ChemicalID is NULL or does it have rows where the ChemicalID is not null?

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 7

Does the ChemicalHash table ONLY contain rows where the ChemicalID is NULL

yes

ChemicalHash table have chemical id is null and i will update it by chemical id

that exist on table fmdchemical

table will update is ChemicalHash and chemicalid on it is null

table i will get data from it is fmdchemical and chemicalid on it is not null