VERY chalanging question

[groupy]

input: 1.5 million records table consisting users with 4 nvchar
fields:A,B,C,D
the problem: there are many records with dublicates A's or duplicates
B's or duplicates A+B's or duplicates B+C+D's & so on. Mathematicly
there are 16-1 posibilities for each duplication.

aim: find the duplicates & filter them, leave only the unique users
which don't have ANY duplication.

We can do it by a simple select query that logicly checks the
duplication in a OR operator.
But it takes about 16 days in a very fast PC.
The DB is in sql-server, converting it to Oracle might acomplish it to
8 days.

How can i do it in a few hours?
Remeber that filtering first the users with parameter A & than by
parameter B & so on will result an error in the final result because it
will loose the information regarding the filtered users - maybe in
parameter C they are equal to other users in the table...

THANK YOU

[jsfromynr]

Hi There,

Sorry!!!! for providing incorrect answer
correct one that you may like to try is
Create Table myData
(
a varchar(2),
b varchar(2),
c varchar(2),
d varchar(2)
)
insert into myData
Select 'a1', 'b1', 'c1', 'd1'
Union
Select 'a1', 'b2', 'c2', 'd2'
Union
Select 'a1', 'b1', 'c3', 'd3'
Union
Select 'a4', 'b4', 'c4', 'd3'
Union
Select 'a5', 'b5', 'c5', 'd5'
Union
Select 'a6', 'b6', 'c6', 'd3'
Alter Table myData add myid int identity(1,1)
Select * from myData
Delete from myData Where myID in
(
Select myID From myData MA ,(Select A from myData group by A having
count(*)>1) AA Where MA.A = AA.A
Union All
Select myID From myData MB ,(Select B from myData group by B having
count(*)>1) BB Where MB.B = BB.B
Union All
Select myID From myData MC ,(Select C from myData group by C having
count(*)>1) CC Where MC.C = CC.C
Union All
Select myID From myData MD ,(Select D from myData group by D having
count(*)>1) DD Where MD.D = DD.D
)
Select * from myData

With Warm regards
Jatinder Singh

Erland Sommarskog wrote:

groupy (li*******@gmai l.com) writes:

A | B | C | D
--------------------
a1 b1 c1 d1
a1 b2 c2 d2
a1 b1 c3 d3
a4 b4 c4 d3
a5 b5 c5 d5
a6 b6 c6 d3

The duplications are:
rows 1+2+3 on A
row 1+3 on B
rows 3+4+6 on D
the only unique (in all params) row is 5
note: finding first that row 1 similar to 2 on A & deleting it will
loose information because we WON'T know if row 1 similar to row 3 on B.
The same goes for the deletion of row 3 : it will cause lose of data
regarding it's similarity to row 4 on D
The Simple query for retriving all duplicated rows which consumes most
time is:
SELECT COUNT(*),A,B,C, D
FROM tbl
GROUP BY A,B,C,D
HAVING count(*)>1
It takes about 2 weaks on a 1.5 million rows, while all fields are
nvchars & the DB is in SQL-Server

I sincerely doubt that this statement takes two weeks to run for 1.5
million rows. Had you said 1.5 milliard rows, I could maybe have
believed it.

Anyway, first index each column individually. Then try:

DELETE tbl
FROM tbl a
JOIN tbl b ON a.A = b.A
WHERE a.B > b.B OR
a.C > b.C OR
a.D > b.D

DELETE tbl
FROM tbl a
JOIN tbl b ON a.B = b.B
WHERE a.C > b.C OR
a.D > b.D

DELETE tbl
FROM tbl a
JOIN tbl b ON a.C = b.C
WHERE a.D > b.C

After this operation, you still have the rows that have the same values
in four columns. But it is not clear from your description whether you
have such duplicates. If you have this maybe the best:

ATLER TABLE tbl ADD ident int IDENTITY

DELETE tbl
FROM tbl a
JOIN tbl b ON a.A = b.A
WHERE a.ident > b.ident

DELETE tbl
FROM tbl a
JOIN tbl b ON a.B = b.B
WHERE a.ident > b.ident

DELETE tbl
FROM tbl a
JOIN tbl b ON a.C = b.C
WHERE a.ident > b.ident

ALTER TABLE tbl DROP COLUMN ident

Note: all the above is untested. For tested solutions (at least with
regards to correctness), please post:

o CREATE TABLE statement for the table.
o INSERT statements with sample data.
o The desired result given the sample.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[groupy]

Thank you all very much, i think i managed something..

[Madhivanan]

How long does it take to run the query now?

Madhivanan
groupy wrote:

Thank you all very much, i think i managed something..

[groupy]

It takes about 2 hours...