Блог

Главная - Вопросы по программированию - Обновление многих строк с помощью соединения чрезвычайно медленное

Обновление многих строк с помощью соединения чрезвычайно медленное

#sql #sqlite #join #sql-update #common-table-expression

Вопрос:

У меня есть таблица с пятью соответствующими полями — id , source , iid , track_hash , alias . Я хочу сгруппировать все записи в группы с общим track_hash , а затем для каждой строки сохранить в id поле строку с самой низкой source (с разрывом связей в пользу самой высокой iid ) записью из ее группы alias . Для этого я написал следующий запрос:

 with best as 
(SELECT id as bid, track_hash FROM
    (SELECT id, track_hash,
        RANK () OVER ( 
            PARTITION BY track_hash
            ORDER BY source asc, iid DESC
        ) rank
        from albums
    )
    where rank = 1
)
select bid, a.* from albums a inner join best
on a.track_hash = best.track_hash

Это занимает вполне разумные 2 секунды на 24 тыс. строк. Теперь, вместо того , чтобы просто видеть это id , я хочу на самом деле сохранить это. Для этого я использовал следующий очень похожий запрос:

 with best as 
(SELECT id as bid, track_hash FROM
    (SELECT id, track_hash,
        RANK () OVER ( 
            PARTITION BY track_hash
            ORDER BY source asc, iid DESC
        ) rank
        from albums
    )
    where rank = 1
)
update albums
set alias = bid FROM albums a inner join best
on a.track_hash = best.track_hash

Однако это занимает от 1 до 10 минут, и я действительно не понимаю, почему. Разве движок не должен сопоставлять каждую строку с ее best.id / alias в любом случае, что именно я и делаю со своим обновлением? Почему это происходит и что я делаю не так?

План запроса выглядит следующим образом:

 MATERIALIZE 1
CO-ROUTINE 4
SCAN TABLE albums USING INDEX track_hash_idx
USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
SCAN SUBQUERY 4
SCAN TABLE albums USING COVERING INDEX track_hash_idx
SEARCH SUBQUERY 1 USING AUTOMATIC PARTIAL COVERING INDEX (rank=?)
SEARCH TABLE albums AS a USING COVERING INDEX track_hash_idx (track_hash=?)

Ответ №1:

Вам не нужно присоединяться к albums (снова).
UPDATE ... FROM Синтаксис фактически обеспечивает неявное соединение albums с best :

 UPDATE albums AS a
SET alias = b.bid 
FROM best AS b
WHERE a.track_hash = b.track_hash

Комментарии:

1. Спасибо, это сработало. Однако почему я не испытывал замедления при выполнении выбора? Кроме того, почему использование явного соединения вместо неявного вызывает такое значительное замедление?

2. @Dariush оператор SELECT верен: он соединяет таблицу с cte. В инструкции UPDATE, которую вы использовали, было избыточное соединение с альбомами: вы присоединили альбомы к соединению альбомов и cte.

Метки: