Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Удаление повторяющихся записей из большой таблицы  [new]
xardas3000
Member

Откуда:
Сообщений: 4
Приветствую всех.

Есть таблица:
CREATE TABLE [dbo].[ArhTrend](
	[ID] [int] NULL,
	[VValue] [float] NULL,
	[TimeOf] [datetime] NULL,
	[Qual] [int] NULL,
	[KeyField] [int] IDENTITY(1,1) NOT NULL,
	[msrepl_tran_version] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK__ArhTrend__76CBA758] PRIMARY KEY CLUSTERED 
(
	[KeyField] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


В этой таблице затесались одинаковые записи. "Одинаковость" проверяется по 3 полям: [ID], [VValue], [TimeOf]. Т.е. если все 3 поля одинаковые в 2 строках-одну из них надо удалить.
В таблице около 140 000 000 записей. В таблицу постоянно пишутся данные, но если придется-можно отключить запись на 1-2 часа.
Диапазон даты, когда встречаются повторы известен (если это уменьшит время выполнения запроса): 2013-12-01 00:00:00.000 - 2014-01-01 00:00:00.000
Сервер Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). Железо: Xeon X5650 (2 процессора), 16 ГБ ОЗУ (из них самому SQL Server`у доступно 8-10 ГБ), 2 винта на 10 000 об/мин в зеркале.

Собственно проблема в том, что я не могу со 100% уверенностью найти повторяющиеся строки. Ищу разными запросами, потом простым запросом смотрю все записи в заданном диапазоне - и нахожу как повторы, так и уникальные записи (т.е. мой запрос возвращается вместе с повторами уникальные записи, которые удалять не надо).

Последняя моя попытка выглядит так:

select t1.[ID],t1.[VValue],t1.[TimeOf]
    from [Trendlog].[dbo].[ArhTrend] t1,
    (
      select [ID],[VValue],[TimeOf], max([KeyField]) KeyField
       from [Trendlog].[dbo].[ArhTrend]
       group by [VValue],[TimeOf],[ID]
     ) t2
     where t1.[ID]=t2.[ID] and t1.[VValue]=t2.[VValue] and t1.[TimeOf]=t2.[TimeOf]  and t1.KeyField<t2.KeyField;
     order by t1.[VValue],t1.[TimeOf],t1.[ID]


Данный запрос выполняется около 10 минут, выбирает порядка 8 миллионов строк, потом падает с ошибкой о нехватке памяти. Время выполнения запроса не критично (в разумных пределах-до 2 часов). Можно добавить индекс специально для этого запроса, не проблема.

Кто что посоветует? Запрос нужен на один раз, убрать повторы, главное точно знать что повторы уберутся, причем только повторы и ничего кроме них.
25 фев 14, 07:46    [15623456]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Например, в цикле небольшими порциями.
25 фев 14, 07:56    [15623466]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
xardas3000
Member

Откуда:
Сообщений: 4
tpg,

Вы немного не так поняли. главная проблема в том, что я не уверен в том, что не захвачу с повторами уникальные записи.
По-другому вопрос можно сформулировать так: что не так с моим запросом? как его исправить или переписать?
25 фев 14, 09:21    [15623650]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
WITH CTE(N)AS(SELECT ROW_NUMBER()OVER(PARTITION BY [ID], [VValue], [TimeOf] ORDER BY(SELECT 0))
DELETE CTE WHERE N>1;
25 фев 14, 09:26    [15623661]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну, так вы план то хоть посмотрите своего запроса - наверняка вам там студия индексов напредлагает.
Если скрипт таблицы полный (только один кластерный индекс) и других "правильных" индексов нет - немудрено.
25 фев 14, 09:26    [15623664]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
iap
WITH CTE(N)AS(SELECT ROW_NUMBER()OVER(PARTITION BY [ID], [VValue], [TimeOf] ORDER BY(SELECT 0))
DELETE CTE WHERE N>1;
Поправил:
WITH CTE(N)AS(SELECT ROW_NUMBER()OVER(PARTITION BY [ID], [VValue], [TimeOf] ORDER BY(SELECT 0) FROM [dbo].[ArhTrend])
DELETE CTE WHERE N>1;
25 фев 14, 09:27    [15623667]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
aleks2
Guest
iap
iap
WITH CTE(N)AS(SELECT ROW_NUMBER()OVER(PARTITION BY [ID], [VValue], [TimeOf] ORDER BY(SELECT 0))
DELETE CTE WHERE N>1;
Поправил:
WITH CTE(N)AS(SELECT ROW_NUMBER()OVER(PARTITION BY [ID], [VValue], [TimeOf] ORDER BY(SELECT 0) FROM [dbo].[ArhTrend])
DELETE CTE WHERE N>1;

Перепиши через exists.
25 фев 14, 09:31    [15623679]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
aleks2
iap
пропущено...
Поправил:
WITH CTE(N)AS(SELECT ROW_NUMBER()OVER(PARTITION BY [ID], [VValue], [TimeOf] ORDER BY(SELECT 0) FROM [dbo].[ArhTrend])
DELETE CTE WHERE N>1;


Перепиши через exists.
DELETE T
FROM [dbo].[ArhTrend] T
WHERE EXISTS
(
 SELECT *
 FROM [dbo].[ArhTrend] TT
 WHERE TT.KeyField>T.KeyField
   AND EXISTS(SELECT TT.ID,TT.VValue,TT.TimeOf INTERSECT SELECT T.ID,T.VValue,T.TimeOf)
);
25 фев 14, 09:43    [15623732]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
aleks2
Guest
declare @rc int = 1
while @rc > 0
begin
  delete top(100) T from [Trendlog].[dbo].[ArhTrend] T where exists(select * from [Trendlog].[dbo].[ArhTrend] TT where TT.KeyField<T.KeyField and TT.ID=T.ID and  TT.VValue=T.VValue and TT.TimeOf = T.TimeOf);
  set @rc = @@rowcount;
  while @@trancount >0 commit;
  -- по фкусу 
  -- waitfor delay '00:00:01'
end;


Но лучше

declare @ArhTrend table([KeyField] [int] NOT NULL primary key clustered)

insert @ArhTrend
  select  KeyField from [Trendlog].[dbo].[ArhTrend] T 
    where exists(select * from [Trendlog].[dbo].[ArhTrend] TT 
                                   where TT.KeyField<T.KeyField and TT.ID=T.ID and  TT.VValue=T.VValue and TT.TimeOf = T.TimeOf
                       );

declare @rc int = 1
while @rc > 0
begin
  delete top(100) T from [Trendlog].[dbo].[ArhTrend] T inner join @ArhTrend TT on TT.KeyField=T.KeyField 
  set @rc = @@rowcount;
  while @@trancount >0 commit;
  -- по фкусу 
  -- waitfor delay '00:00:01'
end;
25 фев 14, 09:53    [15623771]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
aleks2,

а где гарантия, что указанные поля не могут принимать значение NULL?
В структуре таблицы, наоборот, эти поля NULL
25 фев 14, 10:02    [15623821]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
aleks2
Guest
iap
aleks2,

а где гарантия, что указанные поля не могут принимать значение NULL?
В структуре таблицы, наоборот, эти поля NULL

Ну, я ишо блох не ловил.
25 фев 14, 10:19    [15623907]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 630
Я бы еще добавил периодическое удаление из
@ArhTrend

уже не нужных KeyField-ов

на объеме данных, который обозначает ТС - это может оказаться полезным.
25 фев 14, 10:22    [15623922]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4260
xardas3000
В этой таблице затесались одинаковые записи. "Одинаковость" проверяется по 3 полям: [ID], [VValue], [TimeOf]. Т.е. если все 3 поля одинаковые в 2 строках-одну из них надо удалить.
В таблице около 140 000 000 записей.

Кто что посоветует? Запрос нужен на один раз, убрать повторы, главное точно знать что повторы уберутся, причем только повторы и ничего кроме них.


Записей уже достаточно, чтобы секционировать таблицу. Сделайте функцию секционирования по диапазонам ID и чистите повторы посекционно. Я полагаю (могу конечно ошибаться, не зная вашей системы), что повторы появляются на максимальных значениях KeyField, т.е. начальные (возможно) чистить необязательно? Это сократит время операции.
25 фев 14, 11:10    [15624218]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4260
автор
максимальных значениях KeyField

имеется в виду одни из последних значений
25 фев 14, 11:11    [15624236]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4260
А так не быстрее получится искать
select [ID],[VValue],[TimeOf], max([KeyField]) KeyField
from [Trendlog].[dbo].[ArhTrend]
group by 1,2,3
having count(1)>1
25 фев 14, 11:22    [15624342]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4260
Еще можно попробовать добавить
where [TimeOf] between  '2013-12-01 00:00:00.000' and '2014-01-01 00:00:00.000'

Хотя between может преподнести сюрпризы в части быстродействия
25 фев 14, 11:26    [15624381]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
xardas3000
Member

Откуда:
Сообщений: 4
Всем спасибо.
Протестировал запрос от aleks2, нашлось 14кк повторов, причем есть и раньше запланированного интервала. Спросил у старожил системы-да, была проблема с дублями, ее решили, но из базы не удаляли, забили.
Запрос ищет повторы около 8-9 минут, далее идет удаление. Пока не удалял, сейчас запускаю.

Небольшой попутный вопрос: при поиске не на 100% работает винт, примерно 40-60%, процессор вообще примерно на 5% загружен.
Как вообще можно больше загрузить процессор, т.к. узкое место-винт?

К сообщению приложен файл. Размер - 7Kb
26 фев 14, 06:04    [15630134]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
xardas3000
Небольшой попутный вопрос: при поиске не на 100% работает винт, примерно 40-60%, процессор вообще примерно на 5% загружен.
Как вообще можно больше загрузить процессор, т.к. узкое место-винт?

Вы этим какую задачу хотите решить?
Думаете, что если загрузите процессор, то разгрузится дисковая система?
Обычно "узкость места" дисковой системы решают её модернизацией, опять же, если планы выполнения запросов оптимальны или почти оптимальны.
26 фев 14, 06:39    [15630152]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
xardas3000
Member

Откуда:
Сообщений: 4
Спасибо за помощь, запрос обработал всю таблицу за пару часов, нашлось около 14кк повторов. Только пришлось в цикле TOP(100) увеличить до 1кк, иначе с TOP(100) запрос работал 1 час, удалил 1кк записей, с TOP(1000000) за 2 часа удалились остальные 13кк.

В части быстродействия надеялся решить вопрос дополнительными индексами или другими софтовыми методами, но видимо лучше сделать не получится-индексы и так уже есть (кластерный по KeyField, обычные по ID, VValue, TimeOf). Добавлял по ID+TimeOf, ID+VValue+TimeOf - быстрее не стало. Ну и ладно, большая нагрузка на диск происходит только при полной выборке всей таблицы.
По сути выборки за месяц-два работают около 5 секунд, что вполне приемлемо, без индексов было по 5 минут и больше.
27 фев 14, 04:55    [15637068]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
При таких объемах эффективно будет секционирование. PRO версия сервера.
28 фев 14, 15:32    [15647146]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
aleks2
Guest
Владислав Колосов
При таких объемах эффективно будет секционирование. PRO версия сервера.

Наверняка оно и от геморроя помогает.
28 фев 14, 15:50    [15647323]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Владислав Колосов,
я конечно дико извенюсь..но вы решили во всех топиках с 1й страницы отметиться ?
28 фев 14, 15:54    [15647354]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
5-6 сообщений - далеко не все темы первой страницы... Держите себя в руках :)
28 фев 14, 16:14    [15647537]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
aleks2
Владислав Колосов
При таких объемах эффективно будет секционирование. PRO версия сервера.

Наверняка оно и от геморроя помогает.

автор
Диапазон даты, когда встречаются повторы известен
28 фев 14, 16:15    [15647548]     Ответить | Цитировать Сообщить модератору
 Re: Удаление повторяющихся записей из большой таблицы  [new]
aleks2
Guest
Владислав Колосов
aleks2
пропущено...

Наверняка оно и от геморроя помогает.

автор
Диапазон даты, когда встречаются повторы известен

Пока ты будешь "секционировать" - я уже все удалю.
Или ты полагаешь, что аффтор развлекается вставкой-удалением повторов в один и тот же интервал?
28 фев 14, 17:24    [15648106]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить