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

Question

Post reply

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

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

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

DesNorton SSC-Insane Points: 24619 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 Default port Points: 1423 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: 1004664 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: 1004664 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 Default port Points: 1423 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