Fastest technique to deleting duplicate data
After searching stackoverflow.com I found several questions asking how to remove duplicates, but none of them addressed speed.
In my case I have a table with 10 columns that contains 5 million exact row duplicates. In addition, I have at least a million other rows with duplicates in 9 of the 10 columns. My current technique is taking (so far) 3 hours to delete these 5 million rows. Here is my process:
-- Step 1: **This step took 13 minutes.** Insert only one of the n duplicate rows into a temp table
select
MAX(prikey) as MaxPriKey, -- identity(1, 1)
a,
b,
c,
d,
e,
f,
g,
h,
i
into #dupTemp
FROM sourceTable
group by
a,
b,
c,
d,
e,
f,
g,
h,
i
having COUNT(*) > 1
Next,
-- Step 2: **This step is taking the 3+ hours**
-- delete the row when all the non-unique columns are the same (duplicates) and
-- have a smaller prikey not equal to the max prikey
delete
from sourceTable
from sourceTable
inner join #dupTemp on
sourceTable.a = #dupTemp.a and
sourceTable.b = #dupTemp.b and
sourceTable.c = #dupTemp.c and
sourceTable.d = #dupTemp.d and
sourceTable.e = #dupTemp.e and
sourceTable.f = #dupTemp.f and
sourceTable.g = #dupTemp.g and
sourceTable.h = #dupTemp.h and
sourceTable.i = #dupTemp.i and
sourceTable.PriKey != #dupTemp.MaxPriKey
Any tips on how to speed this up, or a faster way? Remember I will have to run this again for rows that are not exact duplicates.
Thanks so much.
UPDATE:
Я должен был остановить шаг 2 от выполнения в 9-часовой отметке.
Я попробовал метод OMG Ponies, и он закончился через 40 минут.
Я попробовал свой шаг 2 с пакетным удалением Andomar, оно прошло 9 часов, прежде чем я остановил его.
ОБНОВИТЬ:
Запустил аналогичный запрос с одним меньшим полем, чтобы избавиться от другого набора дубликатов, и запрос выполнялся всего 4 минуты (8000 строк) с использованием метода OMG Ponies.
Я попробую технику cte при следующей возможности, однако я подозреваю, что метод OMG Ponies будет непростым.
6 ответов
А как насчет EXISTS:
DELETE FROM sourceTable
WHERE EXISTS(SELECT NULL
FROM #dupTemp dt
WHERE sourceTable.a = dt.a
AND sourceTable.b = dt.b
AND sourceTable.c = dt.c
AND sourceTable.d = dt.d
AND sourceTable.e = dt.e
AND sourceTable.f = dt.f
AND sourceTable.g = dt.g
AND sourceTable.h = dt.h
AND sourceTable.i = dt.i
AND sourceTable.PriKey < dt.MaxPriKey)
Можете ли вы позволить себе, чтобы исходная таблица была недоступна на короткое время?
Я думаю, что самым быстрым решением является создание новой таблицы без дубликатов. В основном подход, который вы используете с временной таблицей, но вместо этого создаете «обычную» таблицу.
Затем отбросьте исходную таблицу и переименуйте промежуточную таблицу, чтобы она имела то же имя, что и старая таблица.
Узким местом при массовом удалении строк обычно является транзакция, которую SQL Server должен создать. Вы могли бы значительно ускорить его, разделив удаление на более мелкие транзакции. Например, чтобы удалить 100 строк за раз:
while 1=1
begin
delete top 100
from sourceTable
...
if @@rowcount = 0
break
end
... на основе комментария OMG Ponies выше, метода CTE, который немного более компактен. Этот метод творит чудеса с таблицами, где у вас (по какой-либо причине) нет первичного ключа - где у вас могут быть строки, идентичные для всех столбцов.
;WITH cte AS (
SELECT ROW_NUMBER() OVER
(PARTITION BY a,b,c,d,e,f,g,h,i ORDER BY prikey DESC) AS sequence
FROM sourceTable
)
DELETE
FROM cte
WHERE sequence > 1
Ну, много разных вещей. Сначала будет что-то вроде этой работы (сделайте выбор или убедитесь, что, возможно, даже поместите в собственную временную таблицу, #recordsToDelete):
delete
from sourceTable
left join #dupTemp on
sourceTable.PriKey = #dupTemp.MaxPriKey
where #dupTemp.MaxPriKey is null
Затем вы можете проиндексировать временные таблицы, поместите индекс на prikey
Если у вас есть записи во временной таблице тех, которые вы хотите удалить, вы можете удалять партиями, что часто бывает быстрее, чем блокировка всей таблицы с помощью удаления.
Вот версия, в которой вы можете объединить оба шага в один.
WITH cte AS
( SELECT prikey, ROW_NUMBER() OVER (PARTITION BY a,b,c,d,e,f,g,h,i ORDER BY
prikey DESC) AS sequence
FROM sourceTable
)
DELETE
FROM sourceTable
WHERE prikey IN
( SELECT prikey
FROM cte
WHERE sequence > 1
) ;
Кстати, а есть ли у вас индексы, которые можно временно удалить?