Запрос на удаление дубликатов SQL с миллионами строк для повышения производительности

Это было приключение. Я начал с зацикливания повторяющегося запроса, расположенного в моем предыдущем вопросе, но каждый цикл проходил бы по всем 17 миллионам записей, то есть это занимало бы недели (просто выполнение *select count * from MyTable* занимает у моего сервера 4:30 минуты с использованием MSSQL 2005). Я блистал информацией с этого сайта и на этот пост.

И пришли по запросу ниже. Вопрос в том, является ли этот тип запроса для 17 миллионов записей для любого типа производительности? Если нет, то что?

SQL QUERY:

DELETE tl_acxiomimport.dbo.tblacxiomlistings
WHERE RecordID in 
(SELECT RecordID
    FROM tl_acxiomimport.dbo.tblacxiomlistings
    EXCEPT
    SELECT RecordID
    FROM (
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude,           Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
    FROM tl_acxiomimport.dbo.tblacxiomlistings
    ) al WHERE Rank = 1)
Источник

11 ответов

Решение

Видя QueryPlan поможет.

Это возможно?

SELECT m.*
into #temp
FROM tl_acxiomimport.dbo.tblacxiomlistings m 
inner join (SELECT RecordID, 
                   Rank() over (Partition BY BusinessName, 
                                             latitude,  
                                             longitude,            
                                             Phone  
                                ORDER BY webaddress DESC,  
                                         caption1 DESC,  
                                         caption2 DESC ) AS Rank
              FROM tl_acxiomimport.dbo.tblacxiomlistings
           ) al on (al.RecordID = m.RecordID and al.Rank = 1)

truncate table tl_acxiomimport.dbo.tblacxiomlistings

insert into tl_acxiomimport.dbo.tblacxiomlistings
     select * from #temp
Источник

Что-то не так с вашей БД, сервером, хранилищем или какой-то их комбинацией. 4:30 для выбранного количества * кажется ОЧЕНЬ высоким.

Запустите DBCC_SHOWCONTIG, чтобы увидеть, насколько фрагментирована ваша таблица, это может привести к значительному снижению производительности по сравнению с таблицей такого размера.

Кроме того, чтобы добавить комментарий RyanKeeter, запустите план показа и, если есть какие-либо просмотры таблицы, создайте индекс для поля PK в этой таблице.

Источник

Не было бы проще сделать:

DELETE tl_acxiomimport.dbo.tblacxiomlistings
WHERE RecordID in 
(SELECT RecordID
   FROM (
        SELECT RecordID,
            Rank() over (Partition BY BusinessName,
                                  latitude,
                                  longitude,
                                  Phone
                         ORDER BY webaddress DESC,
                                  caption1 DESC,
                                  caption2 DESC) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        )
  WHERE Rank > 1
  )
Источник

То есть вы удаляете все записи, которые не заняли первое место? Возможно, стоило бы сравнить объединение с первым подзапросом (с этим также может работать в 2000 году, так как рейтинг только 2005 и выше)

Вам нужно удалить все дубликаты за одну операцию? Я предполагаю, что вы выполняете какое-то домашнее задание, возможно, вы сможете выполнить его по частям.

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

Источник

Запустите это в анализаторе запросов:

SET SHOWPLAN_TEXT ON

Затем попросите анализатор запросов выполнить ваш запрос. Вместо выполнения запроса SQL Server сгенерирует план запроса и поместит его в набор результатов.

Покажите нам план запроса.

Источник

Если я правильно понял, ваш запрос такой же, как

DELETE tl_acxiomimport.dbo.tblacxiomlistings
FROM
    tl_acxiomimport.dbo.tblacxiomlistings allRecords
    LEFT JOIN (   
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude, Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        WHERE Rank = 1) myExceptions
    ON allRecords.RecordID = myExceptions.RecordID
WHERE
    myExceptions.RecordID IS NULL

Я думаю, что это должно работать быстрее, я стараюсь избегать использования предложения IN в пользу JOIN, где это возможно.

Вы можете на самом деле проверить скорость и результаты, просто позвонив SELECT * или же SELECT COUNT(*) со стороны, как например,

SELECT *
FROM
    tl_acxiomimport.dbo.tblacxiomlistings allRecords
    LEFT JOIN (   
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude, Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        WHERE Rank = 1) myExceptions
    ON allRecords.RecordID = myExceptions.RecordID
WHERE
    myExceptions.RecordID IS NULL

Это еще одна причина, по которой я бы предпочел подход JOIN. Надеюсь, это поможет

Источник

Приведенное выше предложение сначала выбрать временную таблицу - ваш лучший выбор. Вы также можете использовать что-то вроде:

set rowcount 1000

перед запуском удаления. Он остановится после удаления 1000 строк. Затем запустите его снова и снова, пока не получите 0 удаленных записей.

Источник

17 миллионов записей - ничто. Если для выбора счетчика (*) требуется 4:30, то возникает серьезная проблема, возможно связанная с нехваткой памяти на сервере или действительно старым процессором.

Для производительности починить машину. Накачайте его до 2 ГБ. В наши дни оперативная память настолько дешева, что ее стоимость намного меньше вашего времени.

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

Источник

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

Источник

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

Источник

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

SET ROWCOUNT 5000
WHILE 1 = 1
НАЧАТЬ
    начать тран
            УДАЛИТЬ ИЗ??? ГДЕ???
            IF @@rowcount = 0
            НАЧАТЬ
               COMMIT
               ПЕРЕРЫВ
            КОНЕЦ
    COMMIT
КОНЕЦ
УСТАНАВЛИВАЙТЕ ROWCOUNT 0
Источник
Другие вопросы по тегам