Delete duplicate entries with lower IDs

Question

I have a function that deletes duplicate entries. The highest ID is kept and the older ones are removed.

function:

DELETE [tableName]
FROM [tableName]
INNER JOIN (SELECT * , ROW_NUMBER() OVER 
 (PARTITION BY [fork_id] ORDER BY ID DESC) 
 AS RowNumber FROM [tableName])
Numbered ON [tableName].ID = Numbered.ID
WHERE RowNumber > 1

For example, it changes

|------|---------|--------|
| ID | fork_id | Car |
|------|---------|--------|
| 1 | 2 | AUDI | <--- removed
| 2 | 1 | AUDI |
| 3 | 2 | BMW |
|------|---------|--------|

to

|------|---------|--------|
| ID | fork_id | Car |
|------|---------|--------|
| 2 | 1 | AUDI |
| 3 | 2 | BMW |
|------|---------|--------|

The problem with that query is the execution time exceed time when with have many rows (more than 50k) in the table.

I have a primary key for the ID column

I'm In the sql server, I have a limitation about execution time.

A connection can be cut off by the server for a number of reasons:

1. Idle connection longer than 5 minutes.
2. Long running query.
3. Long running open transaction.
4. Excessive resource usage.

sources

• S3S: what are your indexes on?
• Jean-philippe Emond: I don't have any index except the primary key. Primary keys are always indexed by default. should I do a Index with the fork_idcolumn?
• S3S: well, if ID, fork_id is unique (paired) a clustered index could help. I'm curious as to how long it takes on 100K records.
• Jean-philippe Emond: The fork_id is not necessary assending or with a particular order. well, it probably a nonclustered?

Answer 1

This would probably be better on DBA
You can use a CTE
An index on fork_id should help

with cte as 
( SELECT ROW_NUMBER() OVER (PARTITION BY [fork_id] ORDER BY ID DESC) AS RowNumber 
 FROM [tableName]
)
delete from cte 
WHERE RowNumber > 1 

Optimize

select * from cte 
WHERE RowNumber > 1

If that is fast it is volume thing and you could delete in batches

• Jean-philippe Emond: sorry for the delay.. I have a bug with the "*" in the delete part. Failed to execute query. Error: Incorrect syntax near '*'
• paparazzo: try without the *
• Jean-philippe Emond: works now :-) but the time are 103s for Query succeeded: Affected rows: 487665. with an index on fork_id
• paparazzo: @Jean-philippeEmond It takes time to delete rows.
• Jean-philippe Emond: could I use like select * into AnotherTable from cte WHERE RowNumber > 1 to move all data into another table instead of delete? it could be more quick? (not working but..)