Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
Есть таблица похожая на эту:

CREATE TABLE [dbo].[Files](
	[ID] [nchar](98) NOT NULL,
	[DosName] [nvarchar](255) NOT NULL,
	[CRC32] [nchar](8) NOT NULL,
	[MD5] [nchar](32) NOT NULL,
	[SHA1] [nchar](40) NOT NULL,
	[CRC32MD5SHA1] [nchar](80) NOT NULL,
 CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


С индексированным полем CRC32MD5SHA1:

CREATE NONCLUSTERED INDEX [Ind_CRC32MD5SHA1] ON [dbo].[Files]
(
	[CRC32MD5SHA1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Нужно получить список Files.ID только тех записей которые повторяются в Files по полю CRC32MD5SHA1.
Делаю по средством группировки:

SELECT CRC32MD5SHA1, Count(*) AS CC, Sum(FileSize) as SS
FROM Files
GROUP BY CRC32MD5SHA1
HAVING Count(*) > 1
ORDER BY CC DESC


Выполняется много минут (47 мин 50 сек). Попробовал так:

SELECT TOP 100 CRC32MD5SHA1, Count(*) AS CC, Sum(FileSize) as SS
FROM Files
GROUP BY CRC32MD5SHA1
HAVING Count(*) > 1
ORDER BY CC DESC


Тоже не быстро.

Количество записей в таблице 3 млн с небольшим.
Более быстрых способов поиска нет?
Может тогда попробовать проверять записи при их добавлении в таблицу и дубликаты сохранять в другой специально созданной для этих целей таблице ? Ну.., сделать жалкое подобие OLAP )
4 авг 13, 18:15    [14660660]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
aleks2
Guest
Быстрее чем это - вряд ли

select f.* from Files f right outer join
(SELECT CRC32MD5SHA1
FROM Files
GROUP BY CRC32MD5SHA1
HAVING Count(*) > 1 ) x
on x.CRC32MD5SHA1 = f.CRC32MD5SHA1


Но задача изначально идиотская. Три миллиона - это не три десятка. Их читать нада.
4 авг 13, 18:30    [14660692]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
WITH CTE AS
(
 SELECT
  [CRC32MD5SHA1]
 ,N=ROW_NUMBER()OVER(PARTITION BY [CRC32MD5SHA1] ORDER BY(SELECT 0))
 ,СС=COUNT(*)OVER(PARTITION BY [CRC32MD5SHA1])
 ,SS=SUM(FileSize)OVER(PARTITION BY [CRC32MD5SHA1])
 FROM [dbo].[Files]
)
SELECT [CRC32MD5SHA1],CC,SS FROM CTE WHERE N=2;
Ума не приложу, зачем тут PK типа NVARCHAR(98), а не INT
4 авг 13, 18:32    [14660695]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
Если автору действительно:
автор
Нужно получить список Files.ID

то как насчет такого варианта?
select distinct
  F1.ID
from Files F1
  inner join Files F2 on F2.CRC32MD5SHA1 = F1.CRC32MD5SHA1 and F2.ID != F1.ID
4 авг 13, 19:08    [14660781]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Индекс сделать таким:
CREATE NONCLUSTERED INDEX [Ind_CRC32MD5SHA1] ON [dbo].[Files]
(
	[CRC32MD5SHA1] ASC
) include (FileSize) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Запрос оставить первоначальный.
4 авг 13, 19:18    [14660810]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
такая табличка должна занимать пример 1.1-1.2 гектара
если она вычитывается за 48 минут - то какая-то уж слишком малая скорость считывания с диска получается, не?

что там за план такой, можно увидеть?
4 авг 13, 19:54    [14660925]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37059
XMalon
[MD5] [nchar](32) NOT NULL

64 байта под md5? O_o
4 авг 13, 20:51    [14661166]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37059
CRC32MD5SHA1 - параноя? Вы выдели хоть раз хотя бы два разных файла, у которых md5 одинаковая?
4 авг 13, 20:54    [14661177]     Ответить | Цитировать Сообщить модератору
 Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
iap
Ума не приложу, зачем тут PK типа NVARCHAR(98), а не INT


Ну да, преувеличил )) Буду исправлять на Int
4 авг 13, 21:18    [14661258]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
locky
такая табличка должна занимать пример 1.1-1.2 гектара
если она вычитывается за 48 минут - то какая-то уж слишком малая скорость считывания с диска получается, не?

что там за план такой, можно увидеть?


в таблице несколько больше полей чем описано выше и файл БД занимает 9,6 ГБ

К сообщению приложен файл. Размер - 38Kb
4 авг 13, 21:25    [14661288]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич
XMalon
[MD5] [nchar](32) NOT NULL

64 байта под md5? O_o


Ага ) Получилось храню строку с MD5 хэшом в юникоде, равно как CRC32 и SHA1. Уже исправляю это недоразумение
4 авг 13, 21:28    [14661293]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич
CRC32MD5SHA1 - параноя? Вы выдели хоть раз хотя бы два разных файла, у которых md5 одинаковая?


Нет, не видел. Тогда буду сверять по [MD5], но поля [CRC32] и [SHA1] оставлю. А вот поле [CRC32MD5SHA1] на удаление.
4 авг 13, 21:31    [14661302]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
ROLpogo
Если автору действительно:
автор
Нужно получить список Files.ID

то как насчет такого варианта?
select distinct
  F1.ID
from Files F1
  inner join Files F2 on F2.CRC32MD5SHA1 = F1.CRC32MD5SHA1 and F2.ID != F1.ID


На самом деле нет, это я погорячился. Вполне достаточно списка [CRC32MD5SHA1]. Ну а теперь уже и вовсе [MD5].
4 авг 13, 21:36    [14661324]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Гавриленко Сергей Алексеевич
CRC32MD5SHA1 - параноя? Вы выдели хоть раз хотя бы два разных файла, у которых md5 одинаковая?


я видел. Неоднократно.
Да и ты видел. тоже неоднократно
Например, если взять одну и ту же файлопомойку вроде вконтактика - одна и та же картинка с разными названиями так загружена сотни раз. если не более.
4 авг 13, 21:47    [14661361]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
XMalon
locky
такая табличка должна занимать пример 1.1-1.2 гектара
если она вычитывается за 48 минут - то какая-то уж слишком малая скорость считывания с диска получается, не?

что там за план такой, можно увидеть?


в таблице несколько больше полей чем описано выше и файл БД занимает 9,6 ГБ


Хороший план, ожидаемый.
Ну так - почему же такой небольшой объем данных с диска - вычитывается так долго то? Проблем с железом нету?
С блокировками?
4 авг 13, 21:48    [14661363]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
цукмцукмуцк
Guest
XMalon,

Вангую, HASH MATCH выгружает хеш таблицу в temp... юзай пример с row_number() как выше анон заметил...
4 авг 13, 21:51    [14661375]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
locky
Гавриленко Сергей Алексеевич
CRC32MD5SHA1 - параноя? Вы выдели хоть раз хотя бы два разных файла, у которых md5 одинаковая?


я видел. Неоднократно.
Да и ты видел. тоже неоднократно
Например, если взять одну и ту же файлопомойку вроде вконтактика - одна и та же картинка с разными названиями так загружена сотни раз. если не более.


Ну это нормально (по крайней мере для меня). Мне главное чтобы файлы с разным содержимым не оказались с одинаковыми хэшами.
Я ведь когда выполню запрос
SELECT MD5, Count(*) AS CC
FROM Files
GROUP BY CRC32MD5SHA1
HAVING Count(*) > 1
ORDER BY CC DESC

увижу список MD5 хэшей по которым имеются несколько записей в Files (ну то есть несколько файлов на диске с разными именами но одинаковым MD5). И потом через запрос
SELECT DosName
FROM Files
WHERE MD5 = '...'

посмотрю список этих файлов по каждому MD5 из предыдущего запроса с группировкой
4 авг 13, 22:00    [14661404]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
locky
XMalon
пропущено...


в таблице несколько больше полей чем описано выше и файл БД занимает 9,6 ГБ


Хороший план, ожидаемый.
Ну так - почему же такой небольшой объем данных с диска - вычитывается так долго то? Проблем с железом нету?
С блокировками?


Проблемы с железом ... а как проверить ? Диск новый, кроме СУБД к нему никто не обращается. Диск SATA 6Gb/s 3TB, подключен к соответствующему порту
4 авг 13, 22:03    [14661417]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Родилась идея "О побочном влиянии индекса на агрегацию большого числа уникальных значений при недостатке оперативной памяти"
4 авг 13, 22:06    [14661426]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
locky
Родилась идея "О побочном влиянии индекса на агрегацию большого числа уникальных значений при недостатке оперативной памяти"


А память то я ему действительно урезал до 2ГБ. Попробую добавить. Больше 6 ГБ все равно не дам, у меня всего 8
4 авг 13, 22:17    [14661459]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
ololpfghugfd
Guest
XMalon,

ты пример от iap принципиально не видишь? хеш матч в плане твоего запроса скорее всего выгружает хеш таблицу на диск... план гавно, так как я понимаю повторяющихся строк мало... пили пример от iap:)
4 авг 13, 22:45    [14661524]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
ololpfghugfd
XMalon,

ты пример от iap принципиально не видишь? хеш матч в плане твоего запроса скорее всего выгружает хеш таблицу на диск... план гавно, так как я понимаю повторяющихся строк мало... пили пример от iap:)


в том примере, если ты обратил внимание, тоже есть sum() group by()
Хотя не факт что там будет использован hash
4 авг 13, 22:56    [14661553]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
XMalon
Member

Откуда:
Сообщений: 19
ololpfghugfd
XMalon,

ты пример от iap принципиально не видишь? хеш матч в плане твоего запроса скорее всего выгружает хеш таблицу на диск... план гавно, так как я понимаю повторяющихся строк мало... пили пример от iap:)


на данный момент повторяющихся строк около 150 000 (не самих строк а 150 000 уникальных хэшей по которым встречаются повторения).

пример от iap я вижу, просто пока я его еще не понял )) такие запросы я еще никогда не писал, вот сижу разбираюсь, немного осталось, почти понял. Поэтому пока по этому запросу ничего и не отписал
4 авг 13, 23:05    [14661583]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Если сделать индекс, как уже было предложено (14660810), то hash из плана уйдет и будет stream aggregate + index scan. А индекс существенно уже таблицы.
4 авг 13, 23:13    [14661625]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дублей в таблице с 3 млн записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37059
locky
Например, если взять одну и ту же файлопомойку вроде вконтактика - одна и та же картинка с разными названиями так загружена сотни раз. если не более.
md5 от содержимого будет одинаковым. Я бы начал заботиться о коллизиях, если речь шла хотя бы о трех миллиардов файлов.

Ну и в любом случае, md5 + crc32 имхо будет достаточно для большинства задач, которые можно выдумать.

З.Ы. Все хеши надо хранить в binary. nchar - это жесть как она есть.

Сообщение было отредактировано: 4 авг 13, 23:14
4 авг 13, 23:13    [14661626]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить