Почему добавление TOP 1 резко ухудшает производительность?

У меня довольно простой запрос

SELECT TOP 1 dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

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

 введите описание изображения здесь>> </a> </p>

<p> Однако, если я удалю <code>TOP 1</code>, я получаю план, который выглядит так и работает через 1-2 секунды: </p>

<p> <a href= введите описание изображения здесь>> </a> </p>

<p> Исправить PK & индексирование ниже. </p>

<p> Тот факт, что <code>TOP 1</code> изменил план запроса, не удивляет меня, я просто немного удивлен, что это делает его намного хуже. </p>

<p> Примечание. Я прочитал результаты этого <a href = post и понять концепцию TOP 1 и т. д. Мне интересно, как я могу изменить запрос, чтобы он использовал лучший план. В настоящее время я сбрасываю данные в таблицу temp, а затем вытаскиваю из нее первую строку. Мне интересно, есть ли лучший способ.

Изменить . Для людей, читающих это после факта, есть несколько дополнительных фрагментов информации.

  • Document_Queue - PK /CI является D_ID и имеет строки ~ 5k.
  • Correspondence_Journal - PK /CI - FILE_NUMBER, CORRESPONDENCE_ID и имеет ~ 1,4 мил строк.

Когда я начал, других индексов не было. Я закончил с одним в Корреспондентском журнале (Document_Id, File_Number)

39 голосов | спросил Kenneth Fisher 28 Jpm1000000pmThu, 28 Jan 2016 21:40:44 +030016 2016, 21:40:44

6 ответов


28

Попробуйте принудительно хеш присоединиться *

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

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


* Будьте осторожны с объединяйте подсказки , потому что они заставляют план таблицу, чтобы соответствовать записанному порядку таблиц в запросе (точно так же, как если бы был указан OPTION (FORCE ORDER)). Из ссылки документации:

 BOL extract

Это не может привести к нежелательным эффектам в этом примере, но в целом это очень хорошо. FORCE ORDER (подразумеваемый или явный) является мощным советом very , который выходит за рамки принудительного исполнения; он предотвращает применение широкого спектра методов оптимизации, включая частичные агрегации и переупорядочение.

Подсказка OPTION (HASH JOIN) может быть менее навязкой в ​​подходящих случаях, так как это не означает FORCE ORDER. Однако он применяется ко всем объединениям в запросе. Другие решения доступны.

ответил paparazzo 28 Jpm1000000pmThu, 28 Jan 2016 22:18:50 +030016 2016, 22:18:50
30

Поскольку вы получаете правильный план с помощью ORDER BY, возможно, вы можете просто свернуть свой собственный оператор TOP?

SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER
FROM (
    SELECT dc.DOCUMENT_ID,
           dc.COPIES,
           dc.REQUESTOR,
           dc.D_ID,
           cj.FILE_NUMBER,
           ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum
    FROM DOCUMENT_QUEUE dc
    INNER JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
    WHERE dc.QUEUE_DATE <= GETDATE()
      AND dc.PRINT_LOCATION = 2
) AS sub
WHERE _rownum=1;

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

ответил Daniel Hutmacher 28 Jpm1000000pmThu, 28 Jan 2016 22:27:04 +030016 2016, 22:27:04
29

Редактировать: +1 работает в этой ситуации, потому что получается, что FILE_NUMBER представляет собой строчную версию с нулевым дополнением целого числа. Лучшим решением для строк является добавление '' (пустая строка), поскольку добавление значения может повлиять на порядок, или для чисел, чтобы добавить что-то, что является константой, но содержит недетерминированную функцию, такой как знак sign(rand()+1). Идея «разбить сортировку» по-прежнему действительна здесь, просто мой метод не идеален.

+ 1

Нет, я не имею в виду, что я согласен ни с чем, я имею в виду это как решение. Если вы измените свой запрос на ORDER BY cj.FILE_NUMBER + 1, тогда TOP 1 будет вести себя по-другому.

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

Толщина этих стрелок предполагает, что ваша таблица DOCUMENT_QUEUE (DQ) намного меньше, чем ваша таблица CORRESPONDENCE_JOURNAL (CJ). И лучший план - проверить строки DQ до тех пор, пока не найдет строку CJ. В самом деле, это то, что сделал бы Оптимизатор запросов (QO), если бы у него не было этого pesky ORDER BY, это хорошо поддерживается индексом покрытия на CJ.

Итак, если вы полностью отбросили ORDER BY, я ожидаю, что вы получите план, в который входит вложенный цикл, итерация по строкам в DQ, поиск в CJ, чтобы убедиться, что строка существует. И с TOP 1, это остановится после выталкивания одной строки.

Но если вам действительно нужна первая строка в порядке FILE_NUMBER, тогда вы можете обмануть систему, игнорируя этот индекс, который кажется (неправильно) настолько полезным, делая ORDER BY CJ.FILE_NUMBER+1), который, как мы знаем, сохранит тот же порядок, что и раньше, но, тем не менее, QO этого не делает. QO сосредоточится на получении всего набора, так что оператор Top N Sort может быть удовлетворен. Этот метод должен составить план, содержащий оператор Compute Scalar для вычисления значения для упорядочения, и оператор Top N Sort для получения первой строки. Но справа от них вы должны увидеть хороший Nested Loop, делающий много Seeks на CJ. И лучше, чем работать с большой таблицей строк, которые ничего не соответствуют в DQ.

Hash Match не обязательно ужасен, но если набор строк, которые вы возвращаете из DQ, меньше, чем CJ (как я ожидал, это будет), тогда Hash Match будет много сканировать больше CJ, чем нужно.

Примечание. Я использовал +1 вместо +0, потому что оптимизатор запросов может распознать, что +0 ничего не меняет. Конечно, то же самое можно применить к +1, если не сейчас, то в какой-то момент в будущем.

ответил Rob Farley 29 Jam1000000amFri, 29 Jan 2016 05:41:23 +030016 2016, 05:41:23
7
  

Я прочитал результаты этого сообщения и понимаю концепцию   Row Goal и т. Д. Мне интересно, как я могу изменить   запрос, чтобы он использовал лучший план

Добавление OPTION (QUERYTRACEON 4138) отключает эффект целых строк только для этого запроса, не будучи чрезмерно предписывающим окончательный план и, вероятно, будет самым простым /самым прямым способом.

Если добавление этого подсказки дает вам ошибку разрешений (требуется для DBCC TRACEON), вы можете применить ее с помощью руководства по плану:

Использование QUERYTRACEON в план , спагеттидба

... или просто используйте хранимую процедуру:

Какие разрешения выполняются QUERYTRACEON Нужна? Kendra Little

ответил Martin Smith 30 Jpm1000000pmSat, 30 Jan 2016 18:03:07 +030016 2016, 18:03:07
2

Поскольку вы выполняете TOP(1), я рекомендую сделать детекцию для ORDER BY для начала. По крайней мере, это обеспечит функциональность предсказуемости результатов (всегда полезно для регрессионного тестирования). Похоже, вам нужно добавить DC.D_ID и CJ.CORRESPONDENCE_ID.

При взгляде на планы запросов я иногда считаю целесообразным упростить запрос: возможно, заранее выберите все соответствующие строки dc в таблице temp, чтобы устранить проблемы с оценкой мощности в QUEUE_DATE и PRINT_LOCATION. Это должно быть быстро, учитывая низкий ряд строк. Затем вы можете добавить индексы в эту временную таблицу без изменения постоянной таблицы.

ответил Simon Birch 31 Jpm1000000pmSun, 31 Jan 2016 13:54:13 +030016 2016, 13:54:13
2

Более новые версии SQL Server предлагают разные (и, возможно, лучшие) варианты для обработки запросов, которые получают субоптимальную производительность, когда оптимизатор может применять оптимизацию целей строки. SQL Server 2016 SP1 представил DISABLE_OPTIMIZER_ROWGOAL USE HINT, который имеет тот же эффект, что и флаг трассировки 4138. Если вы не в этой версии, вы также можете рассмотреть запрос OPTIMIZE FOR подскажите, чтобы получить план запроса, предназначенный для возврата всех строк, а не только 1. Запрос ниже вернет те же результаты, что и в вопросе, но он не будет создан с целью получить только 1 строку.

DECLARE @top INT = 1;

SELECT TOP (@top) dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER
OPTION (OPTIMIZE FOR (@top = 987654321));
ответил Joe Obbish 9 AMpMon, 09 Apr 2018 06:41:16 +030041Monday 2018, 06:41:16

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132