Оптимизация SQL запроса

#sql #sql_server

Добрый день! 

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

  select * from docs d
    where d.id not in(select dd.docs_id_parent from docs_duplicate dd)


Есть - ли алтернативные(быстрее чем этот) варианты запроса?

Таблица docs

------------------------------
|id| start_date | end_date   |
------------------------------
|1 | 01-01-2011 | 01-01-2012 | 
------------------------------
|2 | 22-04-2011 | 22-04-2012 | 
------------------------------
|3 | 14-12-2011 | 14-12-2012 | 
------------------------------
|4 | 25-09-2011 | 25-09-2012 | 
------------------------------
|5 | 01-06-2011 | 01-06-2012 | 
------------------------------
|6 | 21-09-2011 | 21-09-2012 | 
------------------------------


Таблица docs_duplicate

---------------------------
|id|docs_id_parent|docs_id|
---------------------------
|1 |      1       | 3     |
---------------------------
|2 |      4       | 5     |
---------------------------
|3 |      3       | 6     |
---------------------------

    

Ответы

Ответ 1

Текст запроса ничего не говорит о том, быстро или медленно он работает. Нельзя глядя
на вариант запроса X сказать что он работает быстрее, чем вариант запроста Y. 

"Скорость работы" - это минимум 3 раздельных показателя


нагрузка на CPU
нагрузка на диск (чтение)
нагрузка на диск (запись в лог)


SQL Server не выполняет запрос "как написано". Выполнение запроса всегда состоит из 


построения плана - преобразования текста SQL в конкретный механизм выполнения
выполнения запроса по плану


При этом план запроса для одного и того же текста может выглядеть по разному в зависимости
от статистики распределения значений в таблице. И от актуальности этой статистики.
И от наличия индексов. И, естественно, от количества значений. И от версии SQL Server.
И от edition.

Что делат "оптимизацию запроса по тексту" достаточно странным занятием. Потому что
текст вообще ничего не говорит о том, что будет происходить при выполнении.

Например, если данных порядка десятков тысяч строк (т.е. не 2-3 строки, а с более-менее
реальными данными) то (на моей машине, на SQL Express, без дополнительных индексов)


планы запросов с NOT EXISTS и NOT IN совпадают:

план запроса с LEFT JOIN выглядят чуть иначе:



дисковая статистика (set statistics io on) одинакова для всех 4 запросов:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads
0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads
0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'docs_duplicate'. Scan count 1, logical reads 109, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'docs'. Scan count 1, logical reads 203, physical reads 0, read-ahead reads
0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


и при этом все запросы отрабатывают примерно за 500ms (на количестве записей 40/20
тысяч, смотреть стоит с помощью set statistics time on c отключенным actual execution plan)

Что можно ускорить:

В плане видны clustered index scan-ы по таблице docs_duplicate. Это, практически,
пробег по всем записям с построением списка doc_id_parent / doc_id для последующей
фильтрации. Hash match / aggregate с последующей сортировкой (она ест CPU!) - это построение
уникального списка из здоровой выборки на 20к записей. Можно построить эти списки заранее:

CREATE NONCLUSTERED INDEX ix_docs_duplicate_doc_id ON [dbo].[docs_duplicate]
(
    [docs_id] ASC
)

-- для других вариантов
CREATE NONCLUSTERED INDEX ix_docs_duplicate_doc_parent_id ON [dbo].[docs_duplicate]
(
    [docs_id_parent] ASC
)


Это поменяет планы на примерно такие:



И статистику по io (на моих данных!) на 

Table 'docs_duplicate'. Scan count 1, logical reads 45, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'docs'. Scan count 1, logical reads 203, physical reads 0, read-ahead reads
0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Обращений к docs_duplicate стало меньше. В зависимости от данных в таблице - их может
быть намного меньше. 
И срежет время выполнения (у меня - с 450-500ms до 350-450ms)

Почему это происходит:


SQL Server-у больше не приходится сканнировать таблицу и искать уникальные значения.
Т.е. ему не надо тратить время процессора на хэширование и сортировку (в первом плане).
Он просто берет данные из индекса и льет их наверх.
Данные в новых индексах отсортированы. Два отсортированных потока данных можно сждойнить
слиянием, а не через hash join. Это тоже снимает нагрузку с CPU.
Больше не используются временные таблицы (worktable/workfile). На оригинальных планах
этого не видно, но любая hash/sort операция требует памяти. Если SQL Server не угадает
с объемом сортировки (из-за старой статистики или из-за положения луны) и выделит слишком
мало памяти - он обнаружит это уже в процессе джойна/сортировки. И сделает то, что
назывется spill to tempdb - начнет сортировать данные не в памяти, а на диске. SELECT,
пишущий на диск - это обычно довольно неприятный сюрприз - даже если у вас SSD.


TL;DR - ВСЕГДА обновляйте статистику перед попытками оптимизировать запрос. Не создавайте
индексы наугад. Смотрите планы, а не текст запроса. Оптимизируйте проблемы в плане,
а не в тексте. Не верьте в лечение по фотографии.


Ответ 2

Обратите внимание, что в этом запросе соединение осуществляется по dd.docs_id. Это
позволяет отсечь из таблицы docs документы, которые указаны как дубликаты в таблице
docs_duplicate.

SELECT * 
FROM docs d
LEFT JOIN docs_duplicate dd ON d.id = dd.docs_id 
WHERE dd.docs_id IS NULL


Неверно делать соединение по dd.docs_id_parent, так как результат будет другой. Выборка
будет содержать документы, у которых нет дубликатов и этими документами могут быть
и сами дубликаты.


Ответ 3

попробуйте это

SELECT * 
FROM docs d
LEFT JOIN docs_duplicate dd ON d.id = dd.docs_id_parent
WHERE dd.docs_id_parent IS NULL


Ответ 4

Иногда not exist дает очень неплохие результаты   

SELECT *
FROM docs d
WHERE NOT EXISTS (SELECT *
                  FROM docs_duplicate dd
                  WHERE d.id = dd.docs_id)