Использование индекса ClosedItemDimIdx

[sergeypp]

На табличке InventSum есть индекс ClosedItemDimIdx в который входят 2 поля Closed и ItemId. Поскольку поле Closed имеет всего 2 значения, то индекс не эффективный
по планам выполнения эффективнее использовать кластерный индекс ItemDimIdx, а индекс ClosedItemDimIdx вообще грохнуть.. Есть ли у кого какие комментарии по этому поводу..?

[Maxim Gorbunov]

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

А чем он Вам мешает? Пусть живет. Грохать его не стоит хотя бы потому, что значительная часть кода перестанет компилироваться (он много где в качестве index hint указан). Хотя, можно его просто отключить, не удаляя. И тем не менее, а чем он все-таки мешает?

[belugin]

>>> Поскольку поле Closed имеет всего 2 значения, то индекс не эффективный

Мне кажется, это зависит от количества значений. Например, в Оракле действовало правило 5% - если индекс отбирает 5% записей, то он эффективен. Возможно, если у вас 95% закрыто а 5% открыто то для поиска жтих 5% индекс эффективен.

[mazzy]

Цитата:

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

Поскольку поле Closed имеет всего 2 значения, то индекс не эффективный

Зависит от числа записей с каждым значением.

Цитата:

Сообщение от Maxim Gorbunov

С другой стороны, можно InventSum просто периодически чистить

Это перебор, по-моему.

Цитата:

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

Мне кажется, это зависит от количества значений. Например, в Оракле действовало правило 5% - если индекс отбирает 5% записей, то он эффективен. Возможно, если у вас 95% закрыто а 5% открыто то для поиска жтих 5% индекс эффективен.

Именно так.
Этот индекс должен будет эффективно работать через некоторое достаточно продолжительное время.

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

[Maxim Gorbunov]

Да, удалять, наверное, не стоит. Тем более, учитывая то, что проверка целостности все равно недостающие записи будет упорно восстанавливать.

[sergeypp]

пишу след запрос

select Closed, count(*) from inventsum
group by Closed

вот результат
Closed
----------- -----------
0 396231
1 923596
просто при использовании некластерного индекса в планах появляется еще и Bookmark Lookup
вот два плана. .с использованием индекса и нет

1           1             |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017]))                                                                                                                     46          2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017])                                                        [Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017]  1.0                      0.0                      1.6147925E-6             41          9.701564E-3              [Expr1002], [Expr1003]                                 NULL     PLAN_ROW                       0        1.0
1           1                  |--Stream Aggregate(DEFINE:([Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE])))                                                                                                                                     46          3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                           [Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE])                         1.0                      0.0                      1.6147925E-6             41          9.701564E-3              [Expr1015], [Expr1016], [Expr1017]                     NULL     PLAN_ROW                       0        1.0
22          1                       |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH)                                                                                                                                                                      46          4           3           Nested Loops                   Inner Join                     OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH                                                                                                                             NULL                                                                                                            16.147924                0.0                      6.7498324E-5             337         9.6999491E-3             [A].[POSTEDQTY], [A].[POSTEDVALUE]                     NULL     PLAN_ROW                       0        1.0
22          1                            |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([domoNew].[dbo].[INVENTSUM] AS [A]))                                                                                                                                                           46          6           4           Bookmark Lookup                Bookmark Lookup                BOOKMARK:([Bmk1000]), OBJECT:([domoNew].[dbo].[INVENTSUM] AS [A])                                                                                                              [A].[INVENTDIMID], [A].[POSTEDQTY], [A].[POSTEDVALUE]                                                           16.147924                7.8122527E-4             1.7762717E-5             234         4.098813E-3              [A].[INVENTDIMID], [A].[POSTEDQTY], [A].[POSTEDVALUE]  NULL     PLAN_ROW                       0        1.0
22          1                            |    |--Index Seek(OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174CLOSEDITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[CLOSED]=0 AND [A].[ITEMID]='0000005') ORDERED FORWARD)                                                            46          8           6           Index Seek                     Index Seek                     OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174CLOSEDITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[CLOSED]=0 AND [A].[ITEMID]='0000005') ORDERED FORWARD, FORCEDINDEX  [Bmk1000]                                                                                                       16.147924                3.2034011E-3             9.6424199E-5             78          3.2998251E-3             [Bmk1000]                                              NULL     PLAN_ROW                       0        1.0
22          22                           |--Clustered Index Seek(OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD)                                                                     46          9           4           Clustered Index Seek           Clustered Index Seek           OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD, FORCEDINDEX                NULL                                                                                                            1.0                      3.2034011E-3             7.9607002E-5             111         5.5336375E-3             NULL                                                   NULL     PLAN_ROW                       0        16.147924

1           1             |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017]))                                                                                                                     49          2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017])                                                        [Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017]  1.0                      0.0                      1.6147925E-6             41          1.1219452E-2             [Expr1002], [Expr1003]                                               NULL     PLAN_ROW         0        1.0
1           1                  |--Stream Aggregate(DEFINE:([Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE])))                                                                                                                                     49          3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                           [Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE])                         1.0                      0.0                      1.6147925E-6             41          1.1219452E-2             [Expr1015], [Expr1016], [Expr1017]                                   NULL     PLAN_ROW         0        1.0
22          1                       |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH)                                                                                                                                                                      49          4           3           Nested Loops                   Inner Join                     OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH                                                                                                                             NULL                                                                                                            16.147924                0.0                      6.7498324E-5             337         1.1217837E-2             [A].[POSTEDQTY], [A].[POSTEDVALUE]                                   NULL     PLAN_ROW         0        1.0
22          1                            |--Clustered Index Seek(OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174ITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[ITEMID]='0000005'),  WHERE:([A].[CLOSED]=0) ORDERED FORWARD)                                                       49          6           4           Clustered Index Seek           Clustered Index Seek           OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174ITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[ITEMID]='0000005'),  WHERE:([A].[CLOSED]=0) ORDERED FORWARD, FORCEDINDEX  [A].[INVENTDIMID], [A].[CLOSED], [A].[POSTEDQTY], [A].[POSTEDVALUE]                                             16.147924                2.7128116E-3             7.8514153E-5             234         5.5826516E-3             [A].[INVENTDIMID], [A].[CLOSED], [A].[POSTEDQTY], [A].[POSTEDVALUE]  NULL     PLAN_ROW         0        1.0
22          22                           |--Clustered Index Seek(OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD)                                                                     49          7           4           Clustered Index Seek           Clustered Index Seek           OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD, FORCEDINDEX                NULL                                                                                                            1.0                      3.2034011E-3             7.9607002E-5             111         5.5336375E-3             NULL                                                                 NULL     PLAN_ROW         0        16.147924

[sergeypp]

а если индекс закрыть конфигурационным ключем, то там где он используется в хинтах - будет игнорироваться?

[Maxim Gorbunov]

Будет.

Но зачем плодить конфигурационные ключи? Изменение свойства Enabled = No даст точно такой же эффект.

[mazzy]

Цитата:

Сообщение от Maxim Gorbunov

Но зачем плодить конфигурационные ключи?

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

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

Согласен, что это вопрос скорее эстетический.
Технически никакой разницы.

[sergeypp]

в продолжении этой темы след вопрос
имеем запрос

            while select itemId from inventSum
                // + spp DOMO 06.03.07 sppSet_001_Optimization_Inventsum
                //index hint closedItemDimIdx
                // - spp DOMO 06.03.07 sppSet_001_Optimization_Inventsum
                group by ItemId
                where inventSum.Closed              == noYes::No &&
                      inventSum.physicalInvent      != 0

Однако не смотря на закоментированный хинт аксапта генерит след запрос

SELECT A.ITEMID FROM INVENTSUM A(INDEX(I_174CLOSEDITEMDIMIDX))  WHERE ((DATAAREAID=?) AND ((CLOSED=?) AND (PHYSICALINVENT<>?))) GROUP BY A.ITEMID ORDER BY A.ITEMID OPTION(FAST 20)

Зачем ядро подставляет хинт по индексу?

[AndyD]

Кернел роллап не используете часом?

[sergeypp]

Рос верси
Solution-Axapta 3.0 CIS SP3 Build #9.2
Retail 3.030.503.000

Сист версия
Solution-Axapta 3.0 Build #1951.3730/514-193 sp3/OP023-71

нету (

[mazzy]

Цитата:

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

в продолжении этой темы след вопрос
имеем запрос

В каком слое? Есть ли нижние слои?
Например, если данный запрос в CUS, а ниже в USR есть старый код, то будет выполняться код из USR.

Цитата:

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

Зачем ядро подставляет хинт по индексу?

Если не слои, то AOS "знает" об изменениях? все AOSы знают об изменениях?
Как правил код через двухвенку, то AOS должен узнать об изменениях чуть позже.

[sergeypp]

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

[mazzy]

тогда не знаю.

знаю одно - барабашки нет.
надо разбираться.

[sergeypp]

Так и не понял в чем дело.. выяснил только одно при подстановки хинта по кластерному индексу работает правильно.. если убрать хинт, то вставляется хинт по индексу closedItemDimIdx. Если исключить поле поле Closed из запроса хинт не подставляется.. Видимо есть еще какой то внутренний оптимизатор, который начинает работать если не указан хинт.. только зачем - так и не понял, гораздо проще отдать запрос на растерзание скулю..
Кстати запрос с использованием кластерного индека отрабатывается за 4 сек, а с использованием индекса closedItemDimIdx за 14 секунд.. Если индекс и начнет когда либо работать лучше кластерного, то это случится не скоро.. Принимаю решение об его выключении (по лиц ключу, ключик уже есть и выключен)

[Wamr]

По поводу автоматического подставления индекса у mazzy

Цитата:

HINT_INDEX (1) – если параметр включен, будут работать операторы INDEX HINT из X ++ кода. Кроме того, при использовании FORCELITERALS имеет побочное действие – при использовании SELECT по одной таблице с условиями в части WHERE по индексированному полю использование этого индекса будет указано в запросе автоматически;

sergeypp, Вы уверены, что ВСЕГДА ItemDimIdx лучше чем ClosedItemIdx и что ВСЕГДА так будет... или после Вас, через полгодика, кто-то опять полезет в базу в поисках "волшебного индекса"?

[sergeypp]

2 Warm
Сегодня еще раз пробежался по коду
Если условие идет только по двум полям ItemId и Closed - вставлял хинт по индексу ClosedItemIdx, если в условие добавлялось еще одно поле, то хинт выключал или смотрел какой индекс подходит лучше и в хинте использовал его. Выключать индекс все таки не стал. Так будет правильнее..
А по поводу FORCELITERALS.. У меня на тестовом аосе действительно включено Direct execution.. Видимо поэтому