Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
Привет,

Ниже привожу порезанный и упрощённый запрос, являющийся частью вьюхи.
В запросе из таблицы [dbo].[fact_lastoperations], которая находится в другой БД выбираются данные по самой последней цене товара на филиале (там что-то вроде лога последних изменений цены). У таблицы [dbo].[fact_lastoperations] есть некластерный индес:
CREATE NONCLUSTERED INDEX [ix_filid_lagerId] ON [dbo].[fact_lastoperations]
(
[filID] ASC,
[lagerID] ASC,
[modified] DESC
)
INCLUDE ([operationPrice],[kolvo])


Проблема в том, что оптимизатор почему то выбирает план запроса с INDEX SCAN вместо INDEX SEEK из-за чего время выполнения резко возрастает. Если я пробую для теста заменить в JOIN'e сравнение на константу (как указано в закомментированной строке), то выбирается INDEX SEEK, как и требуется. Как заставить оптимизатор использовать INDEX SEEK - непонятно...
-- результат запроса - 1 строка
SELECT distinct top 10 
  cp.CompetitorPrice_id, -- код строки документа
  cp.PriceForm_id, -- родительский документ
  cp.Lager_id, -- код товара
  pf.Filial_id, -- код филиала
  pinc.Price -- последняя цена для товара на филиале
FROM  dbo.CompetitorPrice AS cp (nolock) -- строки документов
JOIN dbo.PriceForm AS pf (nolock) ON cp.PriceForm_id = pf.PriceForm_id -- документы
JOIN ( -- здесь выбираем цены для товара на филиале в порядке убывания даты обновления для каждого товара
	select
		f.filid 'fil', 
		f.lagerid 'LagerID', 
		f.operationPrice 'Price', 
		row_number() over (partition by f.filid, f.lagerid order by f.modified desc) as rn
	from [CB].[dbo].[fact_lastoperations] f -- WITH (FORCESEEK) -- пробовал ставить, но валится ошибка
) pinc on 
-- pinc.fil = 585 AND pinc.LagerID = cp.Lager_id -- тут выполняет INDEX SEEK по [CB].[dbo].[fact_lastoperations] SubTreeCost = 0,163
pinc.fil = pf.Filial_id AND pinc.LagerID = cp.Lager_id -- тут выполняет INDEX SCAN по [CB].[dbo].[fact_lastoperations] SubTreeCost = 76,789
WHERE 1=1
AND CompetitorPrice_id = 3858237 -- ID строки документа, в которой указан ID товара и ID родительского документа
AND pinc.rn = 1 -- выбираем строки с самыми "свежими" датами

Пробовал указывать хинт FORCESEEK, но тогда валится ошибка:
Msg 8622, Level 16, State 1, Line 10
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.


План выполнения с INDEX SCAN прилагаю.

К сообщению приложен файл (index_scan.sqlplan - 52Kb) cкачать
9 фев 14, 12:40    [15542111]     Ответить | Цитировать Сообщить модератору
 Re: оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Какой может быть INDEX SEEK, когда вы там всю таблицу нумеруете?
Перепишите через cross apply.
9 фев 14, 13:37    [15542245]     Ответить | Цитировать Сообщить модератору
 Re: оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Leax,

Перепишите через cross apply.
9 фев 14, 13:41    [15542260]     Ответить | Цитировать Сообщить модератору
 Re: оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
Но ведь при указании константы (там где закомменчено)- INDEX SEEK работает!
9 фев 14, 13:48    [15542273]     Ответить | Цитировать Сообщить модератору
 Re: оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
С CROSS APPLY отработало как надо! Спасибо за подсказку!
Вот такой вариант оптимальный, имхо:
-- результат запроса - 1 строка
SELECT distinct top 10 
  cp.CompetitorPrice_id, -- код строки документа
  cp.PriceForm_id, -- родительский документ
  cp.Lager_id, -- код товара
  pf.Filial_id -- код филиала
 -- ,pinc.Price -- последняя цена для товара на филиале
FROM  dbo.CompetitorPrice AS cp (nolock)
JOIN dbo.PriceForm AS pf (nolock) ON cp.PriceForm_id = pf.PriceForm_id -- связь 1 к 1
cross apply
 ( -- здесь выбираем цены для товара на филиале в порядке убывания даты обновления для каждого товара
	select top 1
		f.filid 'fil', 
		f.lagerid 'LagerID', 
		f.operationPrice 'Price'
	from [CB].[dbo].[fact_lastoperations] f
	where f.filID = pf.Filial_id AND f.lagerID = cp.Lager_id
	order by modified desc
) pinc 
where CompetitorPrice_id = 3858237 -- ID строки документа, в которой указан ID товара и ID родительского документа
9 фев 14, 13:59    [15542295]     Ответить | Цитировать Сообщить модератору
 Re: оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Leax,

Вас удивляет то что разные запросы имеют разный план? Ведь это разные по смыслу запросы.

Для того, чтобы оптимизатор рассматривал план с поиском по индексу запрос должен удовлетворять некоторому "паттерну", иначе оптимизатор просто не будет применять правило преобразования для генерации оператора поиска. Даже указав хинт forcessek принудительно, в дереве логических операторов просто не будет подходящего логического оператора (LogOp_Select если конкретно), чтобы рассматривать поиск.

С константой этот оператор появляется т.к. вы явно задаете условие поиска. Также поиск будет возможен, если будет соединение вложенными циклами, когда на внешней стороне будет pf.Filial_id, а на внутренней f.filid. Скорее всего, этого можно добиться переписав запрос с использованием cross apply, перенеся условие из partition by f.filid в условие apply, типа cross apply (... where pf.Filial_id = by f.filid).
9 фев 14, 14:21    [15542332]     Ответить | Цитировать Сообщить модератору
 Re: оптимизатор выбирает INDEX SCAN вместо INDEX SEEK  [new]
Leax
Member

Откуда: Киев
Сообщений: 438
Ещё раз благодарю!
9 фев 14, 14:55    [15542372]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить