Post reply

How to use inner join instead of left join on query below in case of data not ex

  • December 27, 2021 at 11:25 pm

    Go to Answer

    #3967624

    I work on sql server 2014 I need to use inner join instead of left join

    and update Haschemical i case of exist by haschemical or No in case of not exist

    on statement below

    i need to use inner join instead of left join because data is very big on table

    ExtractReports.dbo.FinalComplanceDataDelivery may be 20 million so query take too much time to execute

    update r set r.HasChemical=case when cm.partid is not null then 'HasChemical' else 'No' end
         from ExtractReports.dbo.FinalComplanceDataDelivery r with(nolock)
         left join 
         Parts.ChemicalMaster cm with(nolock) on cm.partid=r.partid

     

    so How to do that Please ?

  • December 28, 2021 at 2:19 am

    #3967652

    You would have to use two separate queries to accomplish it if you replace the LEFT JOIN.

    UPDATE r
    SET r.HasChemical=CASE WHEN cm.partid IS NOT NULL THEN 'HasChemical' ELSE 'No' END
         FROM ExtractReports.dbo.FinalComplanceDataDelivery r
         LEFT JOIN 
         Parts.ChemicalMaster cm ON cm.partid=r.partid;

    Not sure this is any more efficient, but I'd check the query execution plans and time them to be sure.

    UPDATE r
    SET r.HasChemical = 'HasChemical'
    FROM ExtractReports.dbo.FinalComplianceDataDelivery r
    INNER JOIN Parts.ChemicalMaster cm ON cm.PartID = r.PartID;

    UPDATE r
    SET r.HasChemical = 'No'
    FROM ExtractReports.dbo.FinalComplianceDataDelivery r
    WHERE NOT EXISTS (SELECT 1 FROM Parts.ChemicalMaster pm WHERE pm.PartID = r.PartID);

    I would probably just use a BIT column for this... Are there other possible answers than True/False or Yes/No?

    • This reply was modified 2 years, 11 months ago by  pietlinden.

  • December 28, 2021 at 8:53 pm

    Answer

    #3967953

    Neither method is going to be any faster - the code is updating every row in the table ExtractReports.dbo.FinalComplanceDataDelivery.  If you want to improve performance, or more likely - reduce the impact of the statement - then batching the update into smaller chunks might be an option.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

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