Оптимизация SELECT DISTINCT

(По материалам статьи Neil Boyle на swynk.com "Speed up SELECT DISTINCT queries")

Нил пишет, что многие используют опцию DISTINCT в инструкции select для фильтрации дубликатов. Например, простой запрос для базы данных PUBS:

select DISTINCT
au_fname,
au_lname
from authors

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

select DISTINCT
au_fname,
au_lname
from authors a join titleAuthor t
on t.au_id = a.au_id

Здесь нам нужно видеть только уникальные имена авторов, которые написали книги. Запрос будет работать, как требуется, но мы можем повысить его эффективность, если перепишем его так:

select au_fname, au_lname
    from authors a
        where exists (
                                 select *
                                 from titleAuthor t
                                 where t.au_id = a.au_id
                                )

Причина более быстрой отработки запроса в том, что предложение EXISTS возвратит имя сразу же, когда найдена первая книга, и никакие другие книги этого автора далее не будут сканироваться (мы уже получили имя автора, и это всё, что нам нужно). С другой стороны, запрос DISTINCT возвращает одну копию имени автора для, каждой его книги и продолжил бы работу, пока не обработал бы весь список и не отсёк дубликаты согласно предложению DISTINCT. Вы можете исследовать план выполнения каждого из представленных запросов, чтобы увидеть причину повышения эффективности последнего примера.
Повышение эффективности зависит от соотношения количества попаданий строк в LEFT и RIGHT (или INNER и OUTER) таблиц. Следующий запрос будет работать в любой SQL Server базе данных. Пробуйте вставить эти два запроса в Query Analyser и сравнить их планы выполнения, а именно, как соотносятся I/O этой пары запросов для различных базах данных. Второй запрос обычно будет более эффективным, хотя фактическая эффективность может меняться.

select DISTINCT o.name
from sysobjects o
join sysindexes i
on o.id = i.id
where o.type = 'U'

select o.name
from sysobjects o
where o.type = 'U'
and exists (
                    select 1
                    from sysindexes i
                    where o.id = i.id
                    )

Вы должны понять особенности объединения двух (или больше) таблиц, чтобы использовать это для эффективного выполнения. Следующие два запроса для базы данных Northwind предназначены для возврата ID заказчика, у которого установлена скидка больше чем 2 процента для любой позиции. На первый взгляд они будут вести себя так же, как в предыдущем примере (форматы запросов схожи), но полученные фактические результаты окажутся другими.

select DISTINCT customerID
from orders o
join [order details] od
on o.OrderID = od.OrderID
where discount > 0.02

select customerID from orders o
where exists (
                        select *
                        from [order details] od
                        where o.OrderID = od.OrderID
                        and discount > 0.02
                       )

Разница эффективности выполнения этих запросов в том, что OrderID, который определяет зависимость между двумя таблицами, не является именем заказчика. Второй запрос возвратит множество имён заказчика - одно для каждой позиции, полученной заказчиком. Пробуйте добавить столбец OrderID в список SELECT, чтобы увидеть это.

[В начало]