1
\$\begingroup\$

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

\$\endgroup\$
4
  • \$\begingroup\$ what are your indexes on? \$\endgroup\$
    – scsimon
    Jun 14 '17 at 15:04
  • \$\begingroup\$ 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? \$\endgroup\$ Jun 14 '17 at 15:12
  • \$\begingroup\$ 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. \$\endgroup\$
    – scsimon
    Jun 14 '17 at 15:31
  • \$\begingroup\$ The fork_id is not necessary assending or with a particular order. well, it probably a nonclustered? \$\endgroup\$ Jun 14 '17 at 15:38
3
\$\begingroup\$

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

\$\endgroup\$
7
  • \$\begingroup\$ sorry for the delay.. I have a bug with the "*" in the delete part. Failed to execute query. Error: Incorrect syntax near '*' \$\endgroup\$ Jun 15 '17 at 13:47
  • \$\begingroup\$ try without the * \$\endgroup\$
    – paparazzo
    Jun 15 '17 at 13:58
  • \$\begingroup\$ works now :-) but the time are 103s for Query succeeded: Affected rows: 487665. with an index on fork_id \$\endgroup\$ Jun 15 '17 at 14:02
  • \$\begingroup\$ @Jean-philippeEmond It takes time to delete rows. \$\endgroup\$
    – paparazzo
    Jun 15 '17 at 14:06
  • \$\begingroup\$ 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..) \$\endgroup\$ Jun 15 '17 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.