Удалить дубли в таблице

← →
Vlad © (2003-09-25 10:26) [0]

Доброго всем времени суток.
Есть таблица, в которой основные поля: ID (integer,уникальное), Код(integer), Дата(Date), Наименование(Varchar).
Помимо них есть еще штук 15 полей.
Нужно из таблицы удалить дублирующие записи, так, чтобы связка Код-Дата-Наименование стала уникальной.
Проблема в том что в таблице порядка 12 млн записей.
Кто нибудь подскажет наиболее быстрый алгоритм решения задачи, выполнение которого было бы <= 24 часа ?
Все перепробованные мной способы, по предварительным подсчетам, выполнялись бы как минимум трое суток.
Спасибо.

← →
DenK_vrtz © (2003-09-25 10:37) [1]

Думаешь реально на таких объемах быстрее сделать?
А с данными остальных 15 полей как быть? Они одинаковы?

← →
Johnmen © (2003-09-25 10:38) [2]

Стандартно
DELETE FROM Table A WHERE 1<(SELECT COUNT(*) FROM Table B WHERE (A.Код=B.Код) AND (A.Дата=B.Дата) AND (A.Наименование=B.Наименование))

← →
Vlad © (2003-09-25 10:47) [3]

>DenK_vrtz © (25.09.03 10:37) [1]
Данные остальных 15 полей во внимание не принимать. Главное чтоб вышеуказанная связка осталась уникальной.
>Johnmen © (25.09.03 10:38) [2]
Интересное решение. Что-то я с утра не соображу как эта хрень работать должна ? Т.е. мне еще одну такую же таблицу создать, куда перелить все данные из исходной чтоли ? :)

← →
Vlad © (2003-09-25 10:49) [4]

>Johnmen © (25.09.03 10:38) [2]
Все понял.
Туплю, знаете ли с утра :)
Спасибо.

← →
DenK_vrtz © (2003-09-25 10:56) [5]

Johnmen © (25.09.03 10:38) [2], как думаете, на вскидку, времени сколько займет обработать выше указанный объем?

← →
Vlad © (2003-09-25 11:02) [6]

Я время пока не прикидывал, но дома попробую на 100 тыс. записях такой запросик. Если минут за 10-15 отработает, то устроит.

← →
Vlad © (2003-09-25 11:03) [7]

Правда есть сомнения.... Count по определению медленно работает.

← →
DenK_vrtz © (2003-09-25 11:06) [8]

Vlad ©, результатики эксперимента выложи! ОК?!

← →
Johnmen © (2003-09-25 11:09) [9]

>DenK_vrtz © (25.09.03 10:56)

Даже навскидку не представляю :)

>Vlad © (25.09.03 11:03)

Для ускорения стоит поэкспериментировать :
1. Создать индексы на Код, Дата, Наименование
2. Создать составной индекс на Код, Дата, Наименование

О результатах - доложить лично, в этой конференции !
:)))

← →
Romkin © (2003-09-25 11:10) [10]

В IB есть singular
Я бы процедуру написал...

← →
Vlad © (2003-09-25 11:11) [11]

Индексы есть все, кроме составного. Попробую составить составной :)
>О результатах - доложить лично, в этой конференции !
Так точно !

← →
Johnmen © (2003-09-25 11:27) [12]

>Romkin © (25.09.03 11:10)

Да, можно и так попробовать
DELETE FROM Table A WHERE NOT SINGULAR (SELECT * FROM Table B WHERE (A.Код=B.Код) AND (A.Дата=B.Дата) AND (A.Наименование=B.Наименование))

Можно еще ч.-н. придумать с ХП.

← →
Sandman25 © (2003-09-25 11:28) [13]

[2] Johnmen © (25.09.03 10:38)
>Стандартно

Informix ругается, что "Cannot modify table or view used in subquery".
Это ANSI SQL?

← →
Vlad © (2003-09-25 11:35) [14]

А в Yaffil SINGULAR прокатит ?

← →
Johnmen © (2003-09-25 11:58) [15]

>Sandman25 © (25.09.03 11:28)

Стандартно для IB и клонов, как минимум.
А не идет скорее всего потому, что это работает на версионниках, а не на блокировочниках. Может быть еще связано с алгоритмами оптимизации запросов, а они разные...

← →
Sandman25 © (2003-09-25 12:10) [16]

[15] Johnmen © (25.09.03 11:58)

Я думаю, проблема в неоднозначности - конечный результат зависит от того, "с какого конца" таблицы началось выполнение запроса. При одной стратегии останется первая запись, при другой - последняя. То есть нарушается реляционность (независимость от расположения в таблице), происходит переход к навигационному подходу.

← →
kaif © (2003-09-25 14:11) [17]

2 Johnmen © (25.09.03 10:38) [2]

DELETE FROM Table A
WHERE 1<(SELECT COUNT(*) FROM Table B
WHERE (A.Код=B.Код) AND
(A.Дата=B.Дата) AND
(A.Наименование=B.Наименование))

Я боюсь, что этот запрос удалит все записи, которые попадаются неоднократно. А человеку скорее всего нужно удалить избыточные дубликаты, оставив по одной записи от имеющихся дубликатов. Нарушение реляционности конечно требуется, так как неясно, каким экземплярам записей отдать предпочтение.

Так что я боюсь, что это решение ошибочное. Я бы не рискнул применить его на 12 млн. А скорость должна оказаться приемлемой, так как вложенный запрос IB скорее всего выполнит 1 раз.
Но еще раз повторюсь, по-моему тут другая задача.

← →
Vlad © (2003-09-25 14:14) [18]

>kaif © (25.09.03 14:11) [17]
Ничего, у меня бэкап есть :)

← →
kaif © (2003-09-25 14:19) [19]

Я бы сделал так:
1. добавил бы 1 поле типа INTEGER DEFAULT 0.
2. создал бы неуникальный составной индекс на 3 поля.
3. написал бы хранимую процедуру примерно такую:
обхожу таблицу в порядке этого индекса и вставляю 1 каждый
раз, как только значение трех полей повторяется.
4. Проверяю на глаз, все ли правильно получилось.
5. Удаляю все записи одним DELETE, в которых это поле = 1
6. Удаляю поле.

Транзакцию подтверждаю после каждого действия.
Если процент дубликатов небольшой, то многоверсионный мезханизм не должен заметно увеличить файл. Если дубликатов много - тогда не знаю.

← →
Vlad © (2003-09-25 14:23) [20]

>kaif © (25.09.03 14:11) [17]
Ты ошибаешься. Удалит именно избыточные.
И вложенный запрос будет выполнен ровно столько раз сколько записей в базе.

← →
Johnmen © (2003-09-25 14:25) [21]

>kaif © (25.09.03 14:11)

Нет. Запрос отработает именно так, как хотел автор.
Вложенный запрос отработает столько раз, сколько записей в таблице.

← →
kaif © (2003-09-25 14:26) [22]

Процедура, отмечающая дубликаты:

CREATE PROCEDURE AAA
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE CODE INTEGER;
DECLARE VARIABLE DATA DATE;
DECLARE VARIABLE NAME VARCHAR(100);
DECLARE VARIABLE LAST_CODE INTEGER;
DECLARE VARIABLE LAST_DATA DATE;
DECLARE VARIABLE LAST_NAME VARCHAR(100);
BEGIN
for select id, code, data, name
from mytable
order by code, data, name //индекс
into :id, :code, :data, :name
do
begin
if ((code = last_code) and
(data = last_data) and
(name = last_name)) then
update mytable set u_field = 1 where id = :id;

last_code = code;
last_data= data;
last_name = name;
end
END

← →
kaif © (2003-09-25 14:30) [23]

2 Johnmen © (25.09.03 14:25) [21]

Да, действительно. Извиняюсь. Я проглядел, что в условии позапроса идет сравнение с полями таблицы основного запроса.

Но тогда он выполнится 12 млн раз!

Я ошибся из-за того, что я никогда не применяю таких запросов (со сравнением полей таблиц запроса и подзапроса)

Дико извиняюсь.

← →
Vlad © (2003-09-25 14:31) [24]

>kaif © (25.09.03 14:26) [22]
Дык пробовал я такое уже. С индексами.
160 часов - к бабке не ходи !

← →
Johnmen © (2003-09-25 14:38) [25]

>kaif © (25.09.03 14:30)

Да перестань извиняться. Со всеми бывает :)
Я могу еще и не такое просмотреть...:)))

← →
kaif © (2003-09-25 14:43) [26]

2 Vlad © (25.09.03 14:31) [24]
Может быть скопировать все записи, имеющие дубликаты в отдельную таблицу с аналогичной структурой?
Затем убить все записи в первой таблице, используя какой-нибудь неправильный запрос (который я имел в виду) с однократным поздапросом.
Вторую таблицу отфильтровать методом, который предлагает Johnmen.
Затем вставить из второй таблицы записи обратно в первую.

Однако опять тот же вопрос. Какой процент дубликатов?

← →
Polevi © (2003-09-25 14:48) [27]

DELETE FROM Table WHERE ID NOT IN (
SELECT MAX(ID) FROM Table
GROUP BY Код, Дата, Наимнование)

← →
kaif © (2003-09-25 14:50) [28]

А просто переписать все в другую таблицу?
Предположим, IB может сделать 1000 вставок в секунду. 3,6 миллиона вставок за час, то есть 12 миллионов за 4 часа максимум.

insert into table2 select * from AAA;

CREATE PROCEDURE AAA
RETURNS( все поля.....)
AS
DECLARE VARIABLE LAST_CODE INTEGER;
DECLARE VARIABLE LAST_DATA DATE;
DECLARE VARIABLE LAST_NAME VARCHAR(100);
BEGIN
last_code = 0;
last_data= "01.01.1800";
last_name = "";

for select id, code, data, name
from mytable
order by code, data, name //индекс
into :id, :code, :data, :name, ......
do
begin
if ((code <> last_code) or
(data <> last_data) or
(name <> last_name)) then
begin
suspend;
last_code = code;
last_data= data;
last_name = name;
end
end
END

← →
Johnmen © (2003-09-25 14:51) [29]

>Vlad ©

Когда ожидать отчета о проведенных экспериментах ?
Лично меня интересует сравнение времени [2] и [12].
Да и с [27] тоже...

← →
Vlad © (2003-09-25 14:56) [30]

>kaif © (25.09.03 14:43) [26]
Процент дубликатов мал. Думаю не более 1 млн.
>скопировать все записи, имеющие дубликаты в отдельную таблицу
а чем это быстрее чем просто их удалить ? Все равно время на выборку таких записей будет оч. велико.

>Polevi © (25.09.03 14:48) [27]
Пробовал. Group By сильно гадит по времени. Поскольку индексы тут уже роли не играют. Full Scan так сказать...

← →
kaif © (2003-09-25 14:56) [31]

у меня хранимые процедуры обычно обходят набор со скоростью порядка 30тыс-100тыс записей в секунду. Вставка (insert) блоком в одном запросе insert into ... seletc from происходит достаточно быстро, не хуже 1000 записей в секнду. Правда я не пробовал работать с 12 миллионами. Возможно, вставка в такую таблицу происходит гораздо медленнее, особенно если в ней 15 полей.

Есть еще какой-то левый способ работать с таблицей через номера строк буквально. Как-то об этом шла речь на форуме. Некоторые используют какое-то секретное в IB внутренне для быстрых апдейтов.

← →
kaif © (2003-09-25 15:00) [32]

А индексировать удается за приемлемое время?

← →
Vlad © (2003-09-25 15:03) [33]

>kaif © (25.09.03 15:00) [32]
Попробую твой способ, но кажется нечто подобное я уже пробовал.
Индексы создаются за приемлемое время. Скажем так, 4 индекса по varchar-полям сегодня утром создал за 40 минут

← →
Vlad © (2003-09-25 15:05) [34]

>Johnmen © (25.09.03 14:51) [29]
Завтра же утром отпишу подробный отчет :)
Самому интересно.

← →
Vlad © (2003-09-26 09:56) [35]

DenK_vrtz ©, Johnmen ©, kaif © и все кто участвовал.
Вам еще интересны результаты моих лабораторных опытов, касательно обсуждаемой вчера темы ?

← →
DenK_vrtz © (2003-09-26 09:59) [36]

Канэчно!!! :-) С нетерпением ждем(c)

← →
Johnmen © (2003-09-26 10:02) [37]

Мне интересно !!!

← →
Vlad © (2003-09-26 10:12) [38]

Тестировал при количестве записей - 100 тысяч.
Вобщем результаты были выше всяких похвал.
По совету Johnmen © добавил составной индекс.
Результат:
[2] - 2 сек.
[12] - 3 сек.
[27] - запрос повел себя немного странно. Загрузка ЦП - 99%, при этом обращения к жесткому диску были оч. редкими, судя по лампочке. Короче на 10-й минуте ожидания снял задачу. Чтоб убедиться проверил еще раз. Тоже самое.
А теперь, внимание, смертельный номер. Количество записей 12 МЛН.
Используется запрос [2].
Результат - 1 час 20 мин. !!!
Johnmen ©, ты - гений ! Как я сам не догадался про составной индекс ?! Без него все было на несколько порядков медленнее.

В любом случае, спасибо всем кто участвовал.
Конечно не ожидал я такого результата.
При проверке на 100 тыс. надо было видеть мое выражение лица!
Готовый к самому худшему, налил кофе, нарезал бутербродов, взял секундомер, приготовился ждать. И на тебе ! 2 секунды :)))

>Vlad ©
>DenK_vrtz ©

Мужики, не смущайте меня :) Мне неловко...

>Vlad ©

Если несложно, приведи планы выполнения запросов [2] и [12].
Мне почему-то представлялось, что [12] д.б. быстрее [2]...
Но на этот вопрос дадут ответ планы.

>Johnmen © (26.09.03 10:32) [41]
К сож. YaP упорно молчит про планы :) Так уж он устроен.
Пишет только то, что использовался тот самый составной индекс.

>Vlad ©

Кстати, я тут подумал на предложениями kaif ©, и вот предлагаю попробовать такую схему (только если борьба за скорость):
1. CREATE TABLE Temp (ID INT)
2. Хр.процедура, общая схема :
DECLARE VARIABLE i1 INT DECLARE VARIABLE v1, v2, v3, vo1, vo2, vo3 VARCHAR vo1=""; vo2=""; vo3=""; FOR SELECT ID,f1,f2,f3 FROM Table INTO :i1,:v1,:v2,:v3 ORDER BY f1,f2,f3 DO BEGIN IF ((v1=vo1) AND (v2=vo2) AND (v3=vo3)) THEN INSERT INTO Temp VALUES(:i1); vo1=v1; vo2=v2; vo3=v3; END
3. Отрабатываем процедуру
4. DELETE FROM Table WHERE ID IN (SELECT ID FROM Temp)
5. DROP TABLE Temp

Очень интересует скорость ! :)

А зачем при этом создавать временную таблицу ?
Почему нельзя чтобы ХП просто возвращала список ID, которые принадлежат дублирующим записям ?
Тогда: DELETE FROM Table WHERE ID IN (SELECT ID FROM Хранимая_процедура)

>Vlad © (26.09.03 11:42)

Да, можно и так попробовать. Но у меня есть некоторые сомнения по поводу оптимизации запроса.
Вобщем, попробуй и так и со врем.табл., если будет время/желание.

Ок. Дома ради интереса попробую. В понедельник тогда отпишу.
Хотя вобщем моя цель уже достигнута. Дубли удалены. Их, кстати, как я и подозревал оказалось почти 1 млн.

>Johnmen © (26.09.03 12:08) [46]
Кстати, по поводу планов. Ведь я использую Yaffil, а там, в отличие от самого IB существенно оптимизирован алгоритм их построения. Так что наши с тобой результаты могут отличаться, если ты используешь IB

Потрясающий результат!
Johnmen, мое почтение.
Зря, видно, я стараюсь обходиться самыми примитивными запросами.

Конечно, мне интересен и этот результат.
Хотя исходная задача уже решена блестяще (успеть менее, чем за сутки удалить дубликаты).
Я предлагал альтернативное решение только потому что не верил в скоростную приемлемость решения Johnmen...

Грамотный запрос + Индексы = сила!!! :-)

Был тут у меня отчет. 22 минуты выполнялся(типа, сделал попробовал). Создал три! индекса, одну вьюшку подумал-переписал.

Результат - 45 сек!

>DenK_vrtz © (26.09.03 15:13) [52]
Уточню.
Грамотный запрос + нужные индексы + грамотный оптимизатор = великая сила :)

Все это конечно интересно, но при создании индексов также необходимо учитывать насколько интенсивно происходит удаление-вставка в таблицу. А то может получиться что выборка летает, а при добавлении записи или каскадном удалениии сидим и ждем.

Удалить дубли в таблице Найти похожие ветки