Почему мой SQL Server ORDER BY работает медленно, несмотря на индексацию упорядоченного столбца?

У меня есть запрос SQL (сгенерированный LINQ to Entities), который примерно так:

SELECT * FROM [mydb].[dbo].[employees]
JOIN [mydb].[dbo].[industry]
  ON jobs.industryId = industry.id
JOIN [mydb].[dbo].[state]
  ON jobs.stateId = state.id
JOIN [mydb].[dbo].[positionType]
  ON jobs.positionTypeId = positionType.id
JOIN [mydb].[dbo].[payPer]
  ON jobs.salaryPerId = payPer.id
JOIN [mydb].[dbo].[country]
  ON jobs.countryId = country.id
WHERE countryName = 'US'
ORDER BY startDatetime

Запрос возвращает около 1200 строк, что я не думаю, что это огромное количество. К сожалению, это также занимает ~16 секунд. Без ORDER BY запрос занимает <1 секунду.

Я использовал SQL Server Management Studio для помещения индекса в столбец startDatetime, а также кластерного индекса для "cityId, industryId, startDatetime, positionTypeId, payPerId, stateId" (т. Е. Всех столбцов в "заданиях", которые мы используем в JOINS и в столбце мы используем ORDER BY на). У меня уже есть отдельные индексы для каждого из столбцов, которые мы используем в JOIN. К сожалению, это не сделало запрос быстрее.

Я запустил шоу-план и получил:

   |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[cityId]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[stateId]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[industryId]))
       |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[positionTypeId]))
       |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[salaryPerId]))
       |    |    |    |    |--Sort(ORDER BY:([mydb].[dbo].[jobs].[issueDatetime] ASC))
       |    |    |    |    |    |--Hash Match(Inner Join, HASH:([mydb].[dbo].[currency].[id])=([mydb].[dbo].[jobs].[salaryCurrencyId]))
       |    |    |    |    |         |--Index Scan(OBJECT:([mydb].[dbo].[currency].[IX_currency]))
       |    |    |    |    |         |--Nested Loops(Inner Join, WHERE:([mydb].[dbo].[jobs].[countryId]=[mydb].[dbo].[country].[id]))
       |    |    |    |    |              |--Index Seek(OBJECT:([mydb].[dbo].[country].[IX_country]), SEEK:([mydb].[dbo].[country].[countryName]='US') ORDERED FORWARD)
       |    |    |    |    |              |--Clustered Index Scan(OBJECT:([mydb].[dbo].[jobs].[PK_jobs]))
       |    |    |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[payPer].[PK_payPer]), SEEK:([mydb].[dbo].[payPer].[id]=[mydb].[dbo].[jobs].[salaryPerId]) ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[positionType].[PK_positionType]), SEEK:([mydb].[dbo].[positionType].[id]=[mydb].[dbo].[jobs].[positionTypeId]) ORDERED FORWARD)
       |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[industry].[PK_industry]), SEEK:([mydb].[dbo].[industry].[id]=[mydb].[dbo].[jobs].[industryId]) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[state].[PK_state]), SEEK:([mydb].[dbo].[state].[id]=[mydb].[dbo].[jobs].[stateId]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([mydb].[dbo].[city].[PK_city]), SEEK:([mydb].[dbo].[city].[id]=[mydb].[dbo].[jobs].[cityId]) ORDERED FORWARD)

Важная строка выглядит как "|--Sort(ORDER BY:([mydb].[Dbo].[Jobs].[IssueDatetime] ASC))" - без какого-либо упоминания об индексе для этого столбца.

Почему мой ORDER BY делает мой запрос намного медленнее, и как я могу ускорить мой запрос?

Источник

5 ответов

Решение

Если ваш запрос не содержит порядка, он вернет данные в том порядке, в котором он был найден. Нет никакой гарантии, что данные будут возвращены в том же порядке при повторном запуске запроса.

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

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

Попробуйте вернуть меньше столбцов (укажите нужные столбцы вместо Select *) и посмотрите, выполняется ли запрос быстрее.

Источник

Поскольку ваш запрос проецирует все столбцы (*), требуется 5 столбцов для условий соединения и имеет невыборное WHERE предложение о том, что, вероятно, является соединенным столбцом таблицы, приводит к тому, что он достигает критической точки индекса: оптимизатор решает, что дешевле отсканировать всю таблицу, отфильтровать ее и отсортировать, что это будет диапазон сканирования индекса, а затем ищите каждый ключ в таблице, чтобы получить необходимые дополнительные столбцы (5 для объединений и остальные для *).

Лучшим индексом для частичного покрытия этого запроса может быть:

CREATE INDEX ... ON .. (countryId, startDatetime);

Предложение Джеффри сделать кластеризованный индекс покрыл бы запрос на 100% и определенно улучшило бы производительность, но изменение кластеризованного индекса имеет много побочных эффектов. Я бы начал с некластеризованного индекса, как указано выше. Если они не нужны другим запросам, вы можете удалить все другие некластеризованные индексы, которые вы создали, они не помогут в этом запросе.

Источник

Вы должны попробовать ниже код также

Вставить записи во временную таблицу, не используя предложение Order by

SELECT * into #temp FROM [mydb].[dbo].[employees]
JOIN [mydb].[dbo].[industry]
  ON jobs.industryId = industry.id
JOIN [mydb].[dbo].[state]
  ON jobs.stateId = state.id
JOIN [mydb].[dbo].[positionType]
  ON jobs.positionTypeId = positionType.id
JOIN [mydb].[dbo].[payPer]
  ON jobs.salaryPerId = payPer.id
JOIN [mydb].[dbo].[country]
  ON jobs.countryId = country.id
WHERE countryName = 'US'

Теперь запустите оператор, используя Order By Clause

Select * from #temp ORDER BY startDatetime
Источник

Вспышка новостей: индексирование столбца не помогает ускорить сортировку.

Если вы хотите, чтобы ваш запрос ОЧЕНЬ быстрее, измените порядок своих таблиц. В частности, список таблиц country сначала в ваших объединенных таблицах. Причина? Предложение where может фильтровать строки из первой таблицы, вместо того чтобы выполнять все эти объединения, а затем фильтровать строки.

Источник

В каком порядке включены поля в кластерном индексе? Вы хотите поставить startDateTime поле первым для того, чтобы ORDER BY чтобы соответствовать, или в этом случае (countryId, startDateTime) в таком порядке, так как вы хотите выбрать один countryId (косвенно, через countryName), а затем заказать по startDateTime,

Источник
Другие вопросы по тегам