Table Update Timeout Where somefield IN (2500 records)

Transact-SQL
1

Inherited another database and trying to fix a timeout issue.
I found where it is occurring but am unsure how to fix it.

UPDATE myTable SET Active = 1
WHERE myField IN (SELECT ID FROM anotherTable)

This is the very short version.
The SELECT ID FROM anotherTable brings back about 2500 records and each record looks like this: 625285092988
The myTable count can be over 1000 records.

I am unsure how to address this as this procedure can take a few hours to run and that's just wrong.

Any and all suggestions are welcomed.

Thanks,

Zath

2

Try one of the following. #1 probably won't give you any better performance, but will eliminate any problems associated with nulls. #2 might be slightly better if there are not duplicate ID's in the anothertable. #3 would be my preferred approach if this is a one-time update.

-- 1.
UPDATE m SET Active = 1
FROM
	myTable m
WHERE EXISTS
(
	SELECT * FROM anotherTable a
	WHERE a.ID = m.myField
);

GO

-- 2.
UPDATE m SET Active = 1
FROM
	myTable m
	INNER JOIN anotherTable a ON 
		a.ID = m.myField;
GO

-- 3.		
CREATE TABLE #tmp(Id VARCHAR(32) NOT NULL PRIMARY KEY);
INSERT INTO #tmp SELECT DISTINCT ID FROM anotherTable;

UPDATE m SET Active = 1
FROM
	myTable m
	INNER JOIN #tmp a ON 
		a.ID = m.myField;

DROP TABLE #tmp;
GO
3

Thanks for your input. Your suggestion #3 worked a lot better for one part of the store procedure. Now to optimize another part of it but you gave me some great suggestion to work with.