Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
ДД, коллеги!

Подскажите пожалуйста, у меня есть простейший запрос с группировкой и MAX по полю:
SELECT
MAX(UploadDateTime) As MxDt
,FY
,Owner
,Version
,MU
FROM dOPEX
GROUP BY
,FY
,Owner
,Version
,MU


Почему простейший запрос вызывает Index Scan и переборку всех строк в таблице?
По-моему, должен быть Index Seek и возврат одной строки?

Таблица и сканируемый индекс:
CREATE TABLE dOPEX(
	[UploadDateTime] [smalldatetime] NOT NULL,
	[FY] [int] NOT NULL,
	[Owner] [int] NOT NULL,
	[Version] [varchar](4) NOT NULL,
	[MU] [nvarchar](15) NOT NULL,
	[Account] [varchar](10) NOT NULL,
	[Amount] [float](53) NOT NULL,
 CONSTRAINT [PK_dOPEX] PRIMARY KEY CLUSTERED 
(
	[UploadDateTime] ASC,
	[FY] ASC,
	[Owner] ASC,
	[Version] ASC,
	[CostCentre] ASC,
	[Item] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dOPEX_Desc] ON [dbo].[dOPEX]
(
	[UploadDateTime] DESC,
	[FY] ASC,
	[Owner] ASC,
	[Version] ASC,
	[MU] ASC
)
WHERE ([UploadDateTime] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)


Подскажите пожалуйста!

Строк там мизер, но интересна именно причина!
23 сен 15, 15:10    [18185609]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Уточню: возвращается одна строка, но в плане первое действие Index Scan, Estimated Rows = Actual Rows = 447
23 сен 15, 15:11    [18185611]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
SomewhereSomehow
Member

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

Index Seek - это поиск по индексу. Какой у вас в запросе критерий поиска, т.е. что ищем?
23 сен 15, 15:18    [18185636]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Glory
Member

Откуда:
Сообщений: 104760
Eugene_p1
Почему простейший запрос вызывает Index Scan и переборку всех строк в таблице?
По-моему, должен быть Index Seek и возврат одной строки?

А как поиск по индексу поможет распределить все _записи_ согласно вашему условию группировки ?
23 сен 15, 15:23    [18185657]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Glory
Eugene_p1
Почему простейший запрос вызывает Index Scan и переборку всех строк в таблице?
По-моему, должен быть Index Seek и возврат одной строки?

А как поиск по индексу поможет распределить все _записи_ согласно вашему условию группировки ?

Логично. ;)
Я подумал, что заданный критерий (MAX(UploadDateTime)) уже сужает поиск - нужно просканировать только часть индекса, а сканируется вся таблица.
23 сен 15, 15:30    [18185676]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
o-o
Guest
индекс под этот запрос строили?
надо ж было по
FY
Owner
Version
MU
и только потом UploadDateTime.
первые 4 можно менять местами между ними самими (четырьмя),
но не UploadDateTime же
23 сен 15, 15:30    [18185677]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
SomewhereSomehow
Eugene_p1,

Index Seek - это поиск по индексу. Какой у вас в запросе критерий поиска, т.е. что ищем?

Понял.

Но.

У меня несколько аналогичных запросов, и в некоторых появляется Index Seek и Actual Rows = 1.
23 сен 15, 15:31    [18185679]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
o-o
индекс под этот запрос строили?
надо ж было по
FY
Owner
Version
MU
и только потом UploadDateTime.
первые 4 можно менять местами между ними самими (четырьмя),
но не UploadDateTime же

Попробовал - результат такой же.

Да, под этот запрос построил.
23 сен 15, 15:32    [18185687]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Разовью тему.

Эта задумка нужна для того, чтобы получить из таблицы последнюю загруженную в неё порцию строк.
Поэтому она join'ится на себя:
WITH
Mx(Mxt, FY,  [Owner], [Version], [MU])
AS 
(
SELECT MAX(UploadDateTime) As Mxt
	, FY
	, [Owner]
	, [Version]
	, [MU] 
FROM dOPEX 
GROUP BY 
	  FY
	, [Owner]
	, [Version]
	, [MU] 
)

SELECT 
       Account, Amount
FROM dOPEX o
INNER JOIN Mx ON
    o.UploadDateTime = Mx.Mxt
AND o.[Owner] = Mx.[Owner]
AND o.FY = Mx.FY
AND o.MU = Mx.MU
23 сен 15, 15:40    [18185719]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
SELECT TOP(1) WITH TIES *
FROM dOPEX
ORDER BY ROW_NUMBER()OVER(PARTITION BY FY,[Owner],[Version],[MU] ORDER BY UploadDateTime DESC);
23 сен 15, 15:43    [18185734]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
o-o
Guest
Eugene_p1
o-o
индекс под этот запрос строили?
надо ж было по
FY
Owner
Version
MU
и только потом UploadDateTime.
первые 4 можно менять местами между ними самими (четырьмя),
но не UploadDateTime же

Попробовал - результат такой же.

Да, под этот запрос построил.

нехорошо обманывать.
вот аналогичный вашему запрос без индекс (слева),
и вот с индексом (справа)
картинки разные видим?
Stream Aggregate от Hash Aggregate отличаем?
индекс только что построенный видим (ix_test)?
---
а вообще, если у вас там 447 строк, какие еще индексы нужны?

К сообщению приложен файл. Размер - 96Kb
23 сен 15, 15:52    [18185774]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Eugene_p1
Эта задумка нужна для того, чтобы получить из таблицы последнюю загруженную в неё порцию строк.
Во-первых, индекс у вас фильтрованный, и в показанном запросе он использоваться не может. Во-вторых, он просто неверен.

Индекс переделать:
CREATE NONCLUSTERED INDEX [IX_dOPEX_Desc] ON [dbo].[dOPEX]
(
	[FY] ASC,
	[Owner] ASC,
	[Version] ASC,
	[MU] ASC,
	[UploadDateTime] DESC
)
INCLUDE (Account, Amount)
WHERE ([UploadDateTime] IS NOT NULL)

Запрос переписать:
with t as
(
 SELECT 
  Account, Amount, rank() over (partition by FY, Owner, Version, MU order by UploadDateTime desc) as r
 FROM dOPEX o
 WHERE
  UploadDateTime is not null
)
select
 Account, Amount
from
 t
where
 r = 1;
23 сен 15, 15:58    [18185806]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
iap
SELECT TOP(1) WITH TIES *
FROM dOPEX
ORDER BY ROW_NUMBER()OVER(PARTITION BY FY,[Owner],[Version],[MU] ORDER BY UploadDateTime DESC);

Actual Number of Rows не поменялся, а вот план усложнился.



o-o
Eugene_p1
пропущено...

Попробовал - результат такой же.

Да, под этот запрос построил.

нехорошо обманывать.
вот аналогичный вашему запрос без индекс (слева),
и вот с индексом (справа)
картинки разные видим?
Stream Aggregate от Hash Aggregate отличаем?
индекс только что построенный видим (ix_test)?
---
а вообще, если у вас там 447 строк, какие еще индексы нужны?

o-o, не обманываю, чесслово! :)
Перемещение UploadDateTime в конец списка не изменило Actual Number of Rows - так же 447.
Как я написал в начале, вопрос а) спортивный, и б) - есть перспектива роста таблицы.


invm ,
переделал. Добавление условия в подзапрос разумно, согласен. Но Ваша версия не решает ключевой проблемы: сканирования всего индекса.
У меня вопрос - этого можно избежать, или нет?
Кстати, есть аналогичный индекс Asc, где все поля по возрастанию - почему-то оптимизатору он больше нравится.
23 сен 15, 16:10    [18185879]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Eugene_p1
Кстати, есть аналогичный индекс Asc, где все поля по возрастанию - почему-то оптимизатору он больше нравится.

Соврал. В индексе IX_dOPEX_Asc поле UploadDateTime перенесено в INCLUDE.
23 сен 15, 16:21    [18185944]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
o-o
Guest
Eugene_p1
o-o, не обманываю, чесслово! :)
Перемещение UploadDateTime в конец списка не изменило Actual Number of Rows - так же 447.
Как я написал в начале, вопрос а) спортивный, и б) - есть перспектива роста таблицы.

вот на подросшей таблице и узнаете разницу между Stream и Hash.
и не надо заливать про одинаковые планы.
и при чем тут скан, куда ж без него в вашей постановке задачи-то?
и откуда 1 строка в результате,
кроме случая, когда у вас вообще всего 1 уникальная комбинация FY, Owner, Version, MU в таблице?
---
читайте, какой стал Aggregate,
и полез ли он в мой индекс или в ваш.
и если так уж прямо все одно, покажите картинкой как у меня, 2 плана рядышком
---
а если внимательно посмотрите вариант для вашей полной хотелки в исполнении invm,
должны заметить, порядок полей ключа там мой или ваш, применительно к UploadDateTime.
[у меня только include-полей нет, тк в вашем первоначальном запросе их нет, всего-то]
23 сен 15, 16:22    [18185948]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Eugene_p1
Но Ваша версия не решает ключевой проблемы: сканирования всего индекса.
У меня вопрос - этого можно избежать, или нет?
Нельзя.
Подумайте, что необходимо знать, чтобы выполнить поиск для каждой уникальной группы.
23 сен 15, 16:31    [18186001]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
o-o
и откуда 1 строка в результате,
кроме случая, когда у вас вообще всего 1 уникальная комбинация FY, Owner, Version, MU в таблице?

Согласен, я пропустил этот момент: там действительно только одна комбинация FY, Owner, Version, MU на данный момент.
o-o,
в полной картине запроса до правок (да и после них) - Index Scan по IX_dOPEX_Asc (Mxt), и Clustered Index Seek по внешней таблице, из которой достаю данные (физически - одна и та же таблица).
Index Scan - 64%, Index Seek - 32%.

Оптимальной производительности добился путем добавления в индекс в INCLUDE всех полей таблицы, кроме 5 обсужденных.
Запрос:
WITH
Mx(Mxt, FY,  [Owner], [Version], [MU])
AS 
(
SELECT MAX(UploadDateTime) As Mxt
	, FY
	, [Owner]
	, [Version]
	, [MU] 
FROM dOPEX
GROUP BY 
	  FY
	, [Owner]
	, [Version]
	, [MU] 
)

SELECT 
		  o.[Owner]
		, o.[Version]
		, o.FY
		, o.MU
		, cast(FSI as varchar(10)) As FSI
		, Account
		, CostCentre
		, Category
		, Item
FROM dOPEX o
INNER JOIN Mx ON 
		o.UploadDateTime = Mx.Mxt 
	AND o.FY = Mx.FY 
	AND o.[Owner] = Mx.[Owner]
	AND o.[Version] = Mx.[Version] 
	AND o.MU = Mx.MU

стал выполняться:
SELECT <== Top <== Segment <==Index Scan (IX_dOPEX_Asc) (100%)


Если не трудно, ткните, где почитать о принципах создания индексов?
23 сен 15, 17:06    [18186157]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
invm
Eugene_p1
Но Ваша версия не решает ключевой проблемы: сканирования всего индекса.
У меня вопрос - этого можно избежать, или нет?
Нельзя.
Подумайте, что необходимо знать, чтобы выполнить поиск для каждой уникальной группы.

Подумал. Не знаю, но предполагаю, что нужен внешний ключ по этим полям (за исключением UploadDateTime). Тогда будет Index Scan, но по Range, а не всему индексу.

Эти поля включены в PK (т.к. по ним нужно обеспечивать уникальность).
23 сен 15, 17:15    [18186210]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
o-o
Guest
какой еще ключ?
и сканить придется все равно.
только одно дело, когда в каждой группе искомое отсортировано и идет первым/последним в строках этой группы,
и другое дело, когда и групп никаких нет, а навалено все как у вас, когда вы первым отсортировали то, что ищем (зачем???)

вот вы ищите самого высокого -- мах(рост) -- в группе, ок?
в группах у нас пол (м/ж), возраст.
таблица (как ее упорядочили вы):
152, м, 20
162, ж, 40
173, м, 40
173, ж, 50
174, м, 40
182, ж, 40
183, м, 40

убьетесь же с ходу дать ответ, группы не видно, крайнее значение (м, 40, 183), и что, а остальные?

вот вам код, отсортируйте 3мя способами
+
declare @t table (h int, s char, a int);
insert into @t values
(152, 'м', 20),
(162, 'ж', 40),
(173, 'м', 40),
(173, 'ж', 50),
(174, 'м', 40),
(182, 'ж', 40),
(183, 'м', 40)

select *
from @t
order by h, s, a;

select s, a, h
from @t
order by s, a, h;

select a, s, h
from @t
order by a, s, h;

видно, что в последних 2-ух группы готовы, подходи, забирай мин/макс значение в группе?
вот вы серверу дали вариант 1 для поиска, а он бы себе пожелал 2 или 3

К сообщению приложен файл. Размер - 8Kb
23 сен 15, 18:07    [18186522]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Спасибо большое!
Сейчас попробую и буду разбираться.
23 сен 15, 18:23    [18186619]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
o-o,

CREATE NONCLUSTERED INDEX [IX_dOPEX_Asc] ON [dbo].[dOPEX]
(
	[FY] ASC,
	[Version] ASC,
	[MU] ASC,
	[Owner] ASC,
	[UploadDateTime] ASC
)
INCLUDE ( 	[FSI],
	[Account],
	[CostCentre],
	[Category],
	[Item],
	[FC],
	[FY+1],
	[FY+2],
	[FY+3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Как раз в порядке возрастания детализации (FY - год, объединяет большое кол-во записей, UploadDateTime - условно, версии файлов)

SELECT MAX(UploadDateTime) As Mxt
	, FY
	, [Version]
	, [MU] 
	, [Owner]
FROM dOPEX
GROUP BY 
	  FY
	, [Version]
	, [MU] 
	, [Owner]


Я написал, что в плане ничего не поменялось.
Это так. Там как был Index Scan на все строки таблицы, так и остался. Это значит, что всё равно я неправильно сконфигурировал индекс, и он не отрабатывает, как положено?

В полном запросе, тем не менее, картина изменилась.
Если индекс такой же, то происходит так:
RowsExecutesStmtText
1121SELECT ...
1121|--Top(TOP EXPRESSION:((1)))
4481 |--Segment
4481 |--Index Scan(OBJECT:([Budgeting].[dbo].[dOPEX].[IX_dOPEX_Asc] AS [o]) ORDERED BACKWARD)

Но если в индексе поле UploadDateTime сделать сортированным DESC, то картина меняется:
RowsExecutesStmtText
1121SELECT
1121 |--Nested Loops(Inner Join; OUTER REFERENCES:([Budgeting].[dbo].[dOPEX].[FY]; [Budgeting].[dbo].[dOPEX].[Owner]; [Budgeting].[dbo].[dOPEX].[Version]; [Budgeting].[dbo].[dOPEX].[MU]; [Expr1003]))
21 |--Stream Aggregate(GROUP BY:([Budgeting].[dbo].[dOPEX].[FY]; [Budgeting].[dbo].[dOPEX].[Version]; [Budgeting].[dbo].[dOPEX].[MU]; [Budgeting].[dbo].[dOPEX].[Owner]) DEFINE:([Expr1003]=MAX([Budgeting].[dbo].[dOPEX].[UploadDateTime])))
4481 | |--Index Scan(OBJECT:([Budgeting].[dbo].[dOPEX].[IX_dOPEX_Asc]); ORDERED FORWARD)
1122 |--Index Seek(OBJECT:([Budgeting].[dbo].[dOPEX].[IX_dOPEX_Asc] AS [o]); SEEK:([o].[FY]=[Budgeting].[dbo].[dOPEX].[FY] AND [o].[Version]=[Budgeting].[dbo].[dOPEX].[Version] AND [o].[MU]=[Budgeting].[dbo].[dOPEX].[MU] AND [o].[Owner]=[Budgeting].[dbo].[dOPEX].[Owner] AND [o].[UploadDateTime]=[Expr1003]) ORDERED FORWARD)
23 сен 15, 18:51    [18186805]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
o-o
Guest
Eugene_p1,

Вы сейчас про какой запрос, от invm? Оконным функциям в каких-то случаях не все равно, uploadDateTime desc или asc. Это тот план, где segment.
А я про первоначальный запрос с group by, вот ему без разницы, и мой первый ответ про это.
И именно для него из вашего и моего индекса выберет мой индекс, будь он с desc, будь он с asc по upload.
А вам надо выкинуть фиксу из головы, что если в плане скан, то надо убиться. Ну надо ему перелопатить всю таблицу, смиритесь.
В select * from table тоже нужно все, просто это очевиднее, тк результат нагладен (там всё) .
23 сен 15, 19:12    [18186929]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Любопытный гость
Guest
Не могу понять почему DESC в UploadDateTime так влияет на план.
Может уважаемый SomewhereSomehow или какой другой гуру объяснит?
24 сен 15, 15:07    [18190233]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
о-о,
спасибо за разъяснения, почерпнул много нового. :)


В моем понимании, при сканировании индекса с расположенными в правильном порядке полями, предпочтительнее иметь индекс с сортировкой поля UploadDateTime DESC, т.к. после получения каждой комбинации других полей, достаточно будет взять первую запись.
Однако, из двух одинаковых индексов, отличающихся только сортировкой последнего поля, оптимизатор выбирает тот, у которого ASC.
Интерес уже спортивный, для формирования понимания работы.

И еще,
в оптимизаторе описание действия Index Scan такое: Scans Non-Clustered Index, entirely, or only a range.
Вот именно это меня смутило - мне не понятно, почему в моем случае всё же целиком сканируется индекс, а не диапазон (т.е., не берется первое или последнее значение UploadDateTime).
24 сен 15, 16:07    [18190648]     Ответить | Цитировать Сообщить модератору
 Re: Index Scan вместо Index Seek  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Eugene_p1
мне не понятно, почему в моем случае всё же целиком сканируется индекс, а не диапазон (т.е., не берется первое или последнее значение UploadDateTime).
Предположим есть индекс (a, b, c desc):
abc
11220
21210
3125
422200
522100

Начали сканирование со строки 1. Каким образом, дойдя до строки 2, мы должны сразу перейти к строке 4?
24 сен 15, 16:38    [18190897]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить