SQL Update with join

Question

Is there any other faster way to do an update besides with a join? Here's my query but it's a bit slow:

UPDATE @user_dupes
  SET ExternalEmail = ud2.Email
 FROM @user_dupes ud1
INNER JOIN(
SELECT Email, UserName 
  FROM @user_flat_emailtable_dupes
 WHERE EmailType = 2
   AND Email IS NOT NULL AND LEN(Email) > 0
) ud2
ON ud1.UserName = ud2.UserName

Thanks for any ideas

Those are table variables. This is how updates with joins are coded. This is t-sql. This works fine but its a bit slow. Just trying to see if there might be faster ways of getting a bulk update done based off of another table. — David Whitten
– David Whitten, Commented Mar 23, 2012 at 17:18
Depending on a the size of the data in the table varaiable, you might want to use a temp table instead. Typically they perform better for large data sets. And you can index them. — HLGEM
– HLGEM, Commented Mar 23, 2012 at 17:33

Adriano Carneiro · Accepted Answer · 2012-03-23 17:18:51Z

4

If you are using SQL Server, you were almost there. It's just a little fix:

UPDATE ud1 --little fix here!
  SET ExternalEmail = ud2.Email
 FROM @user_dupes ud1
INNER JOIN
(
SELECT Email, UserName 
  FROM @user_flat_emailtable_dupes
 WHERE EmailType = 2
   AND Email IS NOT NULL AND LEN(Email) > 0
) ud2
ON ud1.UserName = ud2.UserName

answered Mar 23, 2012 at 17:18

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MatBailie Over a year ago

+1 : Good spot. Also, should advise the OP to ensure the correct indexes exist, even on table variables, to ensure the join is performant.

David Whitten Over a year ago

Interesting. I'll check this out. Running the updated query now... Thanks

David Whitten Over a year ago

Yep, I'll check out the indexes as well. Thanks

Lamak Over a year ago

I always use this syntax for this type of UPDATE, but I wouldn't have guessed that it affects performance

David Whitten Over a year ago

Yeah, the recommended change did nothing to help performance. I mean, theres quite a bit of records to update. I'll look into the indexes to see if we can speed things up that way.

MatBailie · Accepted Answer · 2012-03-23 17:28:56Z

4

A couple of changes, on top of what @Adrian said...

UPDATE
  ud1   -- @Adrian's change.  Update the instance that you have already aliased.
SET
  externalEmail = ud2.Email
FROM
  @user_dupes                   AS ud1
INNER JOIN
  @user_flat_emailtable_dupes   AS ud2
    ON ud1.UserName = ud2.UserName
WHERE
      ud2.EmailType = 2      -- Removed sub-query, for layout, doubt it will help performance
  AND ud2.Email IS NOT NULL
  AND ud2.Email <> ''        -- Removed the `LEN()` function

But possibly the most important past is to ensure you have indexes. The JOIN is necessary for this logic (or correlated sub-queries, etc), so you want the join to be performant.

An Index of (UserName) on @user_dupes, and an Index of (EmailType, Email, UserName) on @user_flat_emailtable_dupes. (This assumes ud2 is the smaller table, after the filtering)

With the indexes as specified, the change from LEN(Email) > 0 to Email <> '' may allow an index seek rather than scan. The larger your tables the more apparent this will be.

answered Mar 23, 2012 at 17:28

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

3 Comments

jerry Over a year ago

if possible a unique index on user_dupes on username. and a unique index on user_flat_emailtable_dupes on username that includes emailtype and email. oh, coalesce(ud2.email,'')<>'' could help the supplied query a little.

MatBailie Over a year ago

@jerry - That won't help the query use indexed. By wrapping the field on a function you prevent index seeks. The two conditions are easier for the optimiser to resolve than the single coalesce condition.

Andriy M Over a year ago

If ud2.Email <> '', you can be sure it IS NOT NULL. (The explicit IS NOT NULL test is unnecessary here.)

Levin · Accepted Answer · 2012-03-23 18:12:19Z

I believe this query will do the same thing (although you'd have to be sure to form @user_flat_emailtable_dupes with no duplicate usernames). I haven't checked to see if they have different execution plans. It looks like you're refining junky input, I mention this partly because I do a lot of that and find MERGE useful (all the more useful for me since I don't know how UPDATE FROM works). And partly because I hadn't ever used MERGE with variables. It appears to be the case that at least the target table must be aliased, or the parser decides @ud1 is a scalar variable and it breaks.

MERGE @user_dupes AS ud1
USING @user_flat_emailtable_dupes AS ud2
    ON emailType = 2 
   AND COALESCE(ud2.email, '') <> ''
   AND ud2.username = ud1.username
WHEN MATCHED THEN UPDATE
    SET externalEmail = ud2.email
    ;

Collectives™ on Stack Overflow

SQL Update with join

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related