|
|
Posted by groupy on 05/31/06 14:06
Thanks Tony i've just managed something..
Tony Rogerson כתב:
> > SELECT count(*),A.B,C,D
> > FROM tbl
> > GROUP BY A,B,C,D
> > HAVING count(*)>1
>
> If that query takes 2 weeks to run then you've got serious and I mean
> serious problems with your hardware, that type of query should take minutes
> if not seconds most desktops let alone servers.
>
> To aid performance for that specific query create your clustered index on A,
> B, C and D.
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
>
> "groupy" <liav.ezer@gmail.com> wrote in message
> news:1149065676.453810.137020@i39g2000cwa.googlegroups.com...
> > Ok, here is a asample table representing the problem more clearly
> >
> > A | B | C | D
> > -----------------
> > a1 b1 c1 d1
> > a1 b2 c2 d2
> > a3 b3 c1 d3
> > a4 b4 c4 d3
> > a5 b5 c5 d5
> > a6 b6 c6 d3
> >
> > Tha duplications are:
> > row 1+2 in param A
> > row 1+3 in param C
> > row 3+4+6 in param D
> > only row 5 is unique in all parameters.
> > conclusion: row 1+2+3+4+6 are the same user
> > goal: to find all duplicated rows & to delete them all accept one
> > instance to leave.
> >
> > Note:
> > Finding that row 1similar to 2 in A & deleting it will loose data
> > because we won't know that row 1 is ALSO similar to 3 on C & later on
> > finding that 3 is similar to 4 & 6 on D & so on
> >
> > The simple time consuming (about 2 weaks) query to acomplish the task
> > is:
> > SELECT count(*),A.B,C,D
> > FROM tbl
> > GROUP BY A,B,C,D
> > HAVING count(*)>1
> >
> > I THANK YOU ALL
> >
Navigation:
[Reply to this message]
|