SQL Duplicate Delete Query over Millions of Rows for Performance

Something's up with your DB, server, storage or some combination thereof. 4:30 for a select count * seems VERY high.

Run a DBCC_SHOWCONTIG to see how fragmented your table is, this could cause a major performance hit over a table that size.

Also, to add on to the comment by RyanKeeter, run the show plan and if there are any table scans create an index for the PK field on that table.

Wouldn't it be more simple to do:

DELETE tl_acxiomimport.dbo.tblacxiomlistings
WHERE RecordID in 
(SELECT RecordID
   FROM (
        SELECT RecordID,
            Rank() over (Partition BY BusinessName,
                                  latitude,
                                  longitude,
                                  Phone
                         ORDER BY webaddress DESC,
                                  caption1 DESC,
                                  caption2 DESC) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        )
  WHERE Rank > 1
  )

Run this in query analyzer:

SET SHOWPLAN_TEXT ON

Then ask query analyzer to run your query. Instead of running the query, SQL Server will generate a query plan and put it in the result set.

Show us the query plan.

17 million records is nothing. If it takes 4:30 to just do a select count(*) then there is a serious problem, probably related to either lack of memory in the server or a really old processor.

For performance, fix the machine. Pump it up to 2GB. RAM is so cheap these days that its cost is far less than your time.

Is the processor or disk thrashing when that query is going? If not, then something is blocking the calls. In that case you might consider putting the database in single user mode for the amount of time it takes to run the cleanup.

So you're deleting all the records that aren't ranked first? It might be worth comparing a join against a top 1 sub query against (which might also work in 2000, as rank is 2005 and above only)

Do you need to remove all the duplicates in a single operation? I assume that you're preforming some sort of housekeeping task, you might be able to do it piece-wise.

Basically create a cursor that loops all the records (dirty read) and removes dupes for each. It'll be a lot slower overall, but each operation will be relatively minimal. Then your housekeeping becomes a constant background task rather than a nightly batch.

The suggestion above to select into a temporary table first is your best bet. You could also use something like:

set rowcount 1000

before running your delete. It will stop running after it deletes the 1000 rows. Then run it again and again until you get 0 records deleted.

if i get it correctly you query is the same as

DELETE tl_acxiomimport.dbo.tblacxiomlistings
FROM
    tl_acxiomimport.dbo.tblacxiomlistings allRecords
    LEFT JOIN (   
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude, Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        WHERE Rank = 1) myExceptions
    ON allRecords.RecordID = myExceptions.RecordID
WHERE
    myExceptions.RecordID IS NULL

I think that should run faster, I tend to avoid using "IN" clause in favor of JOINs where possible.

You can actually test the speed and the results safely by simply calling SELECT * or SELECT COUNT(*) on the FROM part like e.g.

SELECT *
FROM
    tl_acxiomimport.dbo.tblacxiomlistings allRecords
    LEFT JOIN (   
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude, Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        WHERE Rank = 1) myExceptions
    ON allRecords.RecordID = myExceptions.RecordID
WHERE
    myExceptions.RecordID IS NULL

That is another reason why I would prefer the JOIN approach I hope that helps

This looks fine but you might consider selecting your data into a temporary table and using that in your delete statement. I've noticed huge performance gains from doing this instead of doing it all in that one query.

Remember when doing a large delete it is best to have a good backup first.(And I also usually copy the deleted records to another table just in case, I need to recover them right away. )

Other than using truncate as suggested, I've had the best luck using this template for deleting lots of rows from a table. I don't remember off hand, but I think using the transaction helped to keep the log file from growing -- may have been another reason though -- not sure. And I usually switch the transaction logging method over to simple before doing something like this:

SET ROWCOUNT 5000
WHILE 1 = 1
BEGIN
    begin tran
            DELETE FROM ??? WHERE ???
            IF @@rowcount = 0
            BEGIN
               COMMIT
               BREAK
            END
    COMMIT
END
SET ROWCOUNT 0