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

  • 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
  • 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"
  • 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).

     

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • thank you for interset

    what ou need to understand i will answer you

  • 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?

  • 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

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

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