Optimizing a Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimizing a Stored Proc

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-02-25 : 10:31:35

I've been running this stored proc, but it is taking way too long to run, even with indexes, etc. on the table. Is this a horribly inefficient stored proc? If so, does anyone have any suggestions for speeding it up.
It is running on a table called: NewRandomAddresses

Which has:
ID|EMail|Dupe

and it is just trying to flag duplicate records so I can delete them.

CREATE PROCEDURE RemoveDupesTemp AS
set nocount on
update NewRandomAddresses set dupe='Y' WHERE ID IN
(SELECT ID FROM NewRandomAddresses AS F WHERE Exists (SELECT email,Count(ID)
FROM NewRandomAddresses WHERE email = F.email GROUP BY email
HAVING Count(ID) > 1))
AND ID NOT IN

(SELECT Min(ID) FROM NewRandomAddresses AS F WHERE
Exists (SELECT email, Count(ID) FROM NewRandomAddresses WHERE email = F.email GROUP BY email HAVING Count(ID) > 1) GROUP BY email);
set nocount off

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-25 : 10:43:18

set nocount on
update NewRandomAddresses set dupe='Y'
from NewRandomAddresses a
where exists
(select * from NewRandomAddresses a2 where a.email = a2.email and a2.id > a.id)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources