RecId index (AX 2012 R3)

[AnGor]

интересный факт (возможно извесный):
анализируя запросы на предмет нехватки индексов, заметил следующую штуку:
галочка CreateRecIdIndex и в ручную созданный индекс с полями DataAreId, Partition, RecId дают разный результат.
CreateRecIdIndex создал очень тормознутый индекс, который в десятки раз медленнее созданного в ручную.
все происходило с INVENTJOURNALTABLE
мало того, в моем случае практически всегда было полезно добавлять в индекс DataAreId и Partition

[Владимир Максимов]

А разница-то в чем? Можете выложить сюда скрипты создания обоих вариантов индекса?

[AnGor]

это после CreateRecIdIndex:

CREATE UNIQUE NONCLUSTERED INDEX [I_154RECID] ON [dbo].[INVENTJOURNALTABLE]
(
[RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 46 ms.


это ручной индекс:

CREATE UNIQUE NONCLUSTERED INDEX [I_154VOL_RECIDX] ON [dbo].[INVENTJOURNALTABLE]
(
[RECID] ASC,
[DATAAREAID] ASC,
[PARTITION] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 4 ms.

[fed]

Еще интересно - что такое "медленный индекс". Время обновления может быть разным (в зависимости от списка полей в индексе), но его не очень легко замерить; Индекс может использоваться в типичных запросах чаще или реже; Индекс может снижать время каких-то конкретных запросов сильнее или слабее. Не очень понятно что именно имелось в виду...

[fed]

А - ну вот - понятно. Время создания индекса Нагрузка на CPU при создании индекса с большим числом полей и более длинным ключем - больше. Что логично, но не делает индекс сам по себе более или менее медленным, Все зависит от того как он в дальнейшем будет использоваться.

[AnGor]

Цитата:

Сообщение от fed

... Не очень понятно что именно имелось в виду...

имелось в виду, время выполнения запроса. В моем случае select firstonly InventJournalTable where InventJournalTable.RecId == _RecId

[AnGor]

Цитата:

Сообщение от fed

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

всех запутал
это время выполнения запроса с первым и вторым индексом

[Logger]

Цитата:

Сообщение от AnGor

имелось в виду, время выполнения запроса. В моем случае select firstonly InventJournalTable where InventJournalTable.RecId == _RecId

А вы перед выполнение запроса делали ?
InventJournalTable.disableCache(true);

плюс еще не мешало бы кеш самого SQL сбросить. Была команда DBCC чего-то там по сбросу кеша сиквела.

Тогда уже можно сравнивать.

[fed]

Цитата:

Сообщение от Logger

плюс еще не мешало бы кеш самого SQL сбросить. Была команда DBCC чего-то там по сбросу кеша сиквела.

DBCC DROPCLEANBUFFERS()

[AnGor]

(sorry, I switch to english)
After run DBCC DROPCLEANBUFFERS the difference is not so huge.
This is the Timing with manual created index with dataareaid and Partition:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 30 ms.

and this is the time after applying CreateRecIdIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 40 ms.

This is the query which I used:
SELECT TOP 1 (list of all fields) FROM INVENTJOURNALTABLE T1 WHERE DATAAREAID=N'bvd' AND PARTITION=5637144576 AND RECID=5637148643

[Logger]

Еще вопрос как время меряли.
Таймер в аксапте меряет с точностью 15 миллисекунд.
Поэтому кстати, не играет роли какая настройка долгих запросов стоит, 1 миллисекунда, 5 миллисекунд, 10 или 15.

Так что может у вас и разницы то никакой нет. А просто статистическое отклонение.

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

[fed]

Цитата:

Сообщение от AnGor

(sorry, I switch to english)
After run DBCC DROPCLEANBUFFERS the difference is not so huge.
This is the Timing with manual created index with dataareaid and Partition:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 30 ms.

and this is the time after applying CreateRecIdIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 40 ms.

This is the query which I used:
SELECT TOP 1 (list of all fields) FROM INVENTJOURNALTABLE T1 WHERE DATAAREAID=N'bvd' AND PARTITION=5637144576 AND RECID=5637148643

Ну в целом - логично. У тебя в одном случае все поля условия выборки присутствуют в индексе, а во втором - только часть. Поэтому в первом случае система генерирует план запроса, который ищет по recId-индекс, а потом сразу получает всю запись целиком из кластерного индекса. Во втором случае - в получение записи целиком добавляется немножко CPUшного времени на фильтрацию по dataareaid и partition.

Если стоит задача максимально ускорить именно поиск по inventJournalTable, то попробуй искать не по recId, а по journalId. В этом случае - все будет обрабатываться в одну операцию - поиск по кластерному индексу. На вскидку - я бы ожидал что оно с 30ms до 15ms упадет.

[AnGor]

Цитата:

Сообщение от Logger

Еще вопрос как время меряли.
Таймер в аксапте меряет с точностью 15 миллисекунд.
Поэтому кстати, не играет роли какая настройка долгих запросов стоит, 1 миллисекунда, 5 миллисекунд, 10 или 15.

Так что может у вас и разницы то никакой нет. А просто статистическое отклонение.

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

I have used Timing in SQL. Before run the query I've switched on the io and time statistics:
set statistics io on
set statistics time on

[AnGor]

Цитата:

Сообщение от fed

Если стоит задача максимально ускорить именно поиск по inventJournalTable, то попробуй искать не по recId, а по journalId. В этом случае - все будет обрабатываться в одну операцию - поиск по кластерному индексу. На вскидку - я бы ожидал что оно с 30ms до 15ms упадет.

My current task is to make queries faster
I analyze the statistic which I get from DynamicsPerf. After checking the missing Indexes for lazy queries I'm trying to speed up them by adding recommended Indexes. Before create the index in AOT I'm trying to create it direct in SQL. If the index makes effect I apply it on AOT. That's way how I found out that the query run faster if I add to the index DataAreId and Partition.

[Logger]

Цитата:

Сообщение от fed

Во втором случае - в получение записи целиком добавляется немножко CPUшного времени на фильтрацию по dataareaid и partition.

Интересно. Мне казалось что во втором случае recid индекс будет больше по размеру, плюс на большой табличке поиск по составному ключу может дать деградацию производительности. Т.е. я ожидал обратного эффекта.

Нельзя назвать такой результат ожидаемым. Постфактум можно любое объяснение "привинтить".

Интересно, а сколько у них там записей всего в табличке и компаний.

[Logger]

А можете еще попробовать такой индекс создать и померять
[PARTITION] ASC,
[DATAAREAID] ASC,
[RECID] ASC

При такой конфигурации точно должно быть хуже.

[AnGor]

Цитата:

Сообщение от Logger

Интересно. Мне казалось что во втором случае recid индекс будет больше по размеру, плюс на большой табличке поиск по составному ключу с dataareaid на первом месте может дать сильную деградацию. Т.е. я ожидал обратного эффекта.

Нельзя назвать такой результат ожидаемым. Постфактум можно любое объяснение "привинтить".

Интересно, а сколько у них там записей всего в табличке и компаний.

It's the test Environment.
28 companies and the table is not big - 1547records. In prod I will expect more records

[Logger]

Надо бы еще планы запросов посмотреть.
В одном случае все условия фильтрации совпадают с полями индекса и план может быть один.
А в другом совпадают только с одним полем и план запроса может быть не таким оптимальным.
Наверно в этом собака зарыта.

[Logger]

1547records - это ни о чем.
Сгенерируйте из джоба хотя бы 100 тысяч записей.
Скорее всего картина изменится.

Что же у вас за БД если из такой игрушечной таблицы она целых 40 миллисекунд запись отбирает ?

[egorych]

для таблицы в 1500 записей вообще не рекомендуется строить индекса. Никакие! Фулскан будет быстрее, чем сначала индекс, потом в таблице...