update 300 million records with grouping for dupes

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 2005 Forums

Transact-SQL (2005)

update 300 million records with grouping for dupes

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2011-03-16 : 20:45:24

whenever there are dupes for o_dt, o_sy, o_st, o_ty, o_ex then update the original table with 1. target records = 300 million - query is running for 4 hrs and not yet done - is there a better way to do this. thanks

update Options_raw
set dupe = 1
from options_raw x,
( select o_dt, o_sy, o_st, o_ty, o_ex
from Options_raw
group by o_dt, o_sy, o_st, o_ty, o_ex
having COUNT(o_ty) > 1 )y
where
x.o_dt = y.o_dt and
x.o_sy = y.o_sy and
x.o_st = y.o_st and
x.o_ty = y.o_ty and
x.o_ex = y.o_ex

non clustered indexes on o_dt, o_sy, o_st, o_ty, o_ex and the dupe column is a bit column

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-17 : 10:51:27

I would probably start with a temp table of the grouped records. Only insert the IDs that have a duplicate. Then an update statement that INNER JOINs on that temp table would set the flag. Not sure that it would speed it up, but I can't imagine that it would take 4 hours that way.

Hey, it compiles.

dpais
Yak Posting Veteran

60 Posts

Posted - 2011-03-23 : 13:37:59

thanks ajthepoolman.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources