We help IT Professionals succeed at work.
Get Started

Optimize Amazon SQL Server RDS Update Query

223 Views
Last Modified: 2018-11-30
I am looking at whether my update below is the most effecient way of running the update
Both are populated from live data
@tableROllAPoll will generally have 500-2500 records
@tableActiveRecords will have 300,000+ records

I have the @table structures included as well
DECLARE @tableROllAPoll TABLE
(
    IndividualID BIGINT,
    matchname VARCHAR(1000),
    matchaddres VARCHAR(1000),
    matchEmail VARCHAR(1000),
    HomeNumber BIGINT,
    Mobile BIGINT,
    matchOP VARCHAR(1000),
    MatchAll_IndividualID BIGINT,
    NamePhoneEmail_IndividualID BIGINT,
    NamePhoneAddress_IndividualID BIGINT,
    NameAddressEmail_IndividualID BIGINT,
    PhoneAddressEmail_IndividualID BIGINT,
    NamePhone_IndividualID BIGINT,
    NameEmail_IndividualID BIGINT,
    PhoneEmail_IndividualID BIGINT
);


DECLARE @tableActiveRecords TABLE
(
    OriginID INT,
    OriginName VARCHAR(1000),
    IndividualID BIGINT,
    matchname VARCHAR(1000),
    matchaddres VARCHAR(1000),
    matchEmail VARCHAR(1000),
    HomeNumber BIGINT,
    Mobile BIGINT
);

Open in new window



UPDATE rp
SET rp.NamePhoneEmail_IndividualID = namephoneemail.IndividualID
FROM @tableROllAPoll rp
    JOIN @tableActiveRecords namephoneemail ON rp.matchname = namephoneemail.matchname
                                               AND rp.matchEmail = namephoneemail.matchEmail
                                               AND rp.Mobile = namephoneemail.Mobile
                                               AND rp.Mobile > 0;

Open in new window

Comment
Watch Question
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE