Removing Duplicates - 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

General SQL Server Forums

New to SQL Server Programming

Removing Duplicates

Author

Topic

jjz
Starting Member

31 Posts

Posted - 2010-05-02 : 07:09:06

Hi

I need assistance

i've been running a stored proc for over 2 days because of this piece of code.

is there any other way that i can write this

delete a
from dbo.SW_newlyDetected a
where a.entry_id <
(select max( entry_id )
from dbo.SW_newlyDetected b
where a.machineName = b.machineName
and ((a.serialNumber = b.serialNumber) or (a.serialNumber is null))
and a.detected_application = b.detected_application
and ((a.detected_version = b.detected_version) or (a.detected_version is null))
and a.detected_type = b.detected_type)

jjz
Starting Member

31 Posts

Posted - 2010-05-02 : 07:11:20

additional info

my table new_detected has 1214351 records that is the reason it's taking way too long to run. i need another quicker wait to test this.

i am on SQL 2005

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-02 : 08:26:38

[code]DELETE D
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY machineName, serialNumber, detected_version, detected_type
ORDER BY entry_id DESC)
FROM dbo.SW_newlyDetected)D(recID)
WHERE D.recID > 1;[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 09:32:34

and if by any chance you're using version less than 2005, use


DELETE D
FROM dbo.SW_newlyDetected D
LEFT JOIN(SELECT machineName, serialNumber, detected_version, detected_type,MAX( entry_id ) AS MaxEntryID
          FROM dbo.SW_newlyDetected
          GROUP BY machineName, serialNumber, detected_version, detected_type)D1
ON D1.machineName  = D.machineName
AND D1.serialNumber = D.serialNumber
AND D1.detected_version = D.detected_version
AND D1.detected_type = D.detected_type
AND D1.MaxEntryID = D.entry_id 
WHERE D1.machineName IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-02 : 10:07:57

Also try this:

DELETE FROM a
FROM dbo.SW_newlyDetected a
     INNER JOIN dbo.SW_newlyDetected b
        ON a.machineName = b.machineName
        AND (a.serialNumber = b.serialNumber OR (a.serialNumber IS NULL AND b.serialNumber IS NULL))
        AND a.detected_application = b.detected_application
        AND (a.detected_version = b.detected_version OR (a.detected_version IS NULL AND b.detected_version IS NULL))
        AND a.detected_type = b.detected_type
        AND a.entry_id < b.entry_id;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 10:12:03

quote:

Originally posted by ms65g

Also try this:

DELETE FROM a
FROM dbo.SW_newlyDetected a
     INNER JOIN dbo.SW_newlyDetected b
        ON a.machineName = b.machineName
        AND (a.serialNumber = b.serialNumber OR (a.serialNumber IS NULL AND b.serialNumber IS NULL))
        AND a.detected_application = b.detected_application
        AND (a.detected_version = b.detected_version OR (a.detected_version IS NULL AND b.detected_version IS NULL))
        AND a.detected_type = b.detected_type
        AND a.entry_id < b.entry_id;

it involves a hidden RBAR and can hurt performance
see

http://www.sqlservercentral.com/articles/T-SQL/61539/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-02 : 10:28:31

Thanks, But how about this?


DELETE D
FROM dbo.SW_newlyDetected D
OUTER APPLY (SELECT 1
               FROM dbo.SW_newlyDetected D1
              WHERE a.machineName = b.machineName
		AND (a.serialNumber = b.serialNumber OR (a.serialNumber IS NULL AND b.serialNumber IS NULL))
		AND a.detected_application = b.detected_application
		AND (a.detected_version = b.detected_version OR (a.detected_version IS NULL AND b.detected_version IS NULL))
		AND a.detected_type = b.detected_type
		HAVING MAX(entry_id) = D.entry_id) T(i)
WHERE T.i IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 10:33:06

i think this looks fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources