Почему пункт "Верх" может привести к длительной стоимости

Ответ 1

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

Моя первая мысль заключается в том, что вы делаете select top serviceid из (выберите *....), и оптимизатору может быть сложно отвести запрос к внутренним запросам и сделать использование индекса.

Подумайте, переписывая его как

select top 10 ServiceRequestID  
from  big_table_1
inner join big_table_2 cap2
on cap1.servicerequestid = cap2.customerreferencenumber
and big_table_1.statusid = 2

В вашем запросе база данных, вероятно, пытается объединить результаты и вернуть их, а THEN ограничивает ее до 10 лучших во внешнем запросе. В приведенном выше запросе базе данных нужно будет собрать только первые 10 результатов по мере объединения результатов, экономя массу времени. И если servicerequestID проиндексирован, он обязательно будет использовать его. В вашем примере запрос ищет столбец servicerequestid в результирующем наборе, который уже был возвращен в виртуальном, неиндексированном формате.

Надеюсь, что это имеет смысл. Хотя гипотетически, оптимизатор должен принимать любой формат, в который мы помещаем SQL, и определять оптимальный способ возврата значений каждый раз, правда состоит в том, что способ, которым мы объединяем наш SQL, может действительно повлиять на порядок, в котором определенные шаги выполняются на БД.

SELECT TOP работает медленно, независимо от ORDER BY

Почему верхний индекс (1) в индексированном столбце SQL Server медленный?

Ответ 2

Это также может зависеть от того, что вы подразумеваете под "завершенным". Если "закончено" означает, что вы начинаете видеть некоторый дисплей на gui, это не обязательно означает, что запрос завершил выполнение. Это может означать, что результаты начинают поступать, а не потоковая передача. Когда вы завертываете это в подзапрос, внешний запрос не может действительно обрабатывать его до тех пор, пока не будут доступны все результаты внутреннего запроса:

• внешний запрос зависит от времени, которое требуется для возврата строки last внутреннего запроса, прежде чем он сможет "закончить"
• Выполнение внутреннего запроса независимо может потребоваться только до тех пор, пока строка first не будет возвращена до просмотра каких-либо результатов

В Oracle появились подсказки "first_rows" и "all_rows", которые были несколько связаны с манипулированием этим видом поведения. AskTom обсуждение.

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

Немного отвлекая тему, было бы интересно попробовать имитировать что-то подобное в Oracle: создать функцию Pipelined для потоковой передачи чисел; потоки обратно несколько (скажем, 15), а затем немного свернуть, прежде чем потоковое обратно.

Используется клиент jdbc для выполненияQuery для конвейерной функции. По умолчанию в Oracle Statement fetchSize по умолчанию 10. Loop и распечатать результаты с меткой времени. Посмотрите, не попали ли результаты. Я не мог проверить это с помощью Postgresql (RETURN NEXT), так как Postgres не передает результаты из функции.

Oracle Pipelined Function

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

Postgresql RETURN NEXT

Примечание. Текущая реализация RETURN NEXT и RETURN QUERY сохраняет весь набор результатов перед возвратом из функции, поскольку обсуждалось выше. Это означает, что если функция PL/pgSQL создает очень большой набор результатов, производительность может быть низкой: данные будут записаны на диск, чтобы избежать исчерпания памяти, но сама функция не будет пока не будет сформирован весь результирующий набор. Будущее версия PL/pgSQL может позволить пользователям определять возвращаемый набор функции, которые не имеют этого ограничения.

Размеры выборки по умолчанию JDBC

statement.setFetchSize(100);

Ответ 3

Я не могу объяснить, почему, но я могу дать представление:

попробуйте добавить SET ROWCOUNT 10 перед запросом. Это помогло мне в некоторых случаях. Имейте в виду, что это параметр области, поэтому после выполнения запроса вы должны вернуть его обратно к исходному.

Объяснение: SET ROWCOUNT: приводит к тому, что SQL Server перестает обрабатывать запрос после возврата указанного количества строк.

Ответ 4

При отладке таких вещей я считаю, что самый быстрый способ выяснить, как SQL Server "видит" два запроса, - это посмотреть на их планы запросов. Нажмите CTRL-L в SSMS в представлении запроса, и результаты покажут, какую логику он будет использовать для построения ваших результатов, когда запрос действительно выполняется.

SQL Server поддерживает статистику о ваших таблицах, например. гистограммы числа строк с данными в определенных диапазонах. Он собирает и использует эту статистику, чтобы попытаться предсказать "лучший" способ запуска запросов к этим таблицам. Например, у него могут быть данные, которые предполагают, что для некоторых входов можно ожидать, что конкретный подзапрос должен возвращать 1M строк, тогда как для других входов один и тот же подзапрос может возвращать 1000 строк. Это может привести к выбору различных стратегий построения результатов, скажем, используя сканирование таблицы (исчерпывающий поиск в таблице) вместо поиска индекса (перейдите прямо к желаемым данным). Если статистика не отражает адекватно данные, может быть выбрана "неправильная" стратегия с результатами, аналогичными тем, что вы испытываете. Я не знаю, была ли эта проблема здесь, но я хотел бы найти то, что я хотел бы найти.

Ответ 5

У меня была аналогичная проблема с запросом вроде твоего. Запрос, заказанный, но без верхнего предложения занял 1 секунду, тот же запрос с верхним 3 занял 1 минуту.

Я видел, что с использованием переменной для вершины она работала, как и ожидалось.

Код для вашего случая:

declare @top int = 10;

select top (@top) ServiceRequestID
from 
(
    (select * 
     from  big_table_1
     where big_table_1.StatusId=2
    ) cap1
    inner join
      big_table_2 cap2
    on cap1.ServiceRequestID = cap2.CustomerReferenceNumber
    )

Ответ 6

TOP не сортирует результаты, насколько мне известно, если вы не используете порядок.

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

Попробуйте использовать запрос @sql_mommy, но убедитесь, что у вас есть следующее:

Чтобы ваш запрос работал быстрее, вы можете создать индекс в servicerequestid и statusid в big_table_1 и индекс для customerreferencenumber в big_table_2. Если вы создаете некластеризованные индексы, вы должны получить только индексный план с очень быстрыми результатами.

Если я правильно помню, результаты TOP будут в том же порядке, что и указатель, который вы нам на big_table_1, но я не уверен.

Гис

Ответ 7

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

Запустите эту партию для каждого запроса, чтобы сравнить время выполнения и результаты статистики (Не запускать его в рабочей среде):

DBCC FREEPROCCACHE
GO

CHECKPOINT 
GO

DBCC DROPCLEANBUFFERS 
GO

SET STATISTICS IO ON
GO

SET STATISTICS TIME ON
GO

-- your query here
GO

SET STATISTICS TIME OFF
GO

SET STATISTICS IO OFF
GO

Ответ 8

Мне просто пришлось исследовать очень похожую проблему.

SELECT TOP 5 *
FROM t1 JOIN t2 ON t2.t1id = t1.id 
WHERE t1.Code = 'MyCode' 
ORDER BY t2.id DESC

t1 имеет 100K строк, t2 20M строк. Среднее количество строк из объединенных таблиц для t1.Code составляет около 35K. Фактический набор результатов - всего 3 строки, потому что t1.Code = 'MyCode' соответствует только двум строкам, которые имеют только 3 соответствующие строки в t2. Статистика обновлена.

В TOP 5, как и выше, запрос занимает несколько минут, при этом TOP 5 удаляет запрос немедленно.

Планы с и без TOP полностью отличаются.

• План без TOP использует поиск индекса на t1.Code, находит 2 строки, затем вложенный цикл объединяет 3 строки через индексный поиск по t2. Очень быстро.
• План с TOP использует сканирование индекса на t2, дающее 20M строк, затем вложенный цикл объединяет 2 строки через индексный поиск на t1.Code, затем применяет верхний оператор.

Я думаю, что мой план TOP настолько плох, что строки, выбранные из t1 и t2, являются одними из самых новых строк (наибольшие значения для t1.id и t2.id). Оптимизатор запросов предположил, что выбор первых 5 строк из равномерно распределенного среднего набора результатов будет быстрее, чем не-TOP-подход. Я протестировал эту теорию, используя t1.code из самых ранних строк, и ответ является второстепенным, используя тот же план.

Таким образом, заключение, по крайней мере, в моем случае состоит в том, что проблема связана с неравномерным распределением данных, которое не отражается в статистике.

Ответ 9

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

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

Повторите попытку после пересчета статистики (и пока вы на ней, перестройте индексы)

Также проверьте, помогает ли это вынуть where big_table_1.StatusId=2 и вместо этого перейдите к

select top 10 ServiceRequestID
from  big_table_1 as cap1 INNER JOIN
big_table_2 as cap2
ON cap1.ServiceRequestID = cap2.CustomerReferenceNumber
WHERE cap1.StatusId=2

Я считаю этот формат более удобочитаемым, хотя он должен (хотя и возможно, он не работает) оптимизировать один и тот же план выполнения. Возвращенный endresult будет идентичным, несмотря на