Автор Тема: Индексирование временных таблиц  (Прочитано 2431 раз)

0 Пользователей и 1 Гость просматривают эту тему.

U83

  • Пользователь
  • **
  • Сообщений: 14
Индексирование временных таблиц
« : Ноябрь 29, 2015, 06:41:14 pm »
Всем доброго дня!
Помогите, пожалуйста, разобраться.

Конфигурация в режиме совместимости с 8.2.16. Управляемый режим. SQL 2008 R2.

Имеется следующий запрос:

ВЫБРАТЬ
   ПродажаТабличнаяЧасть.Товар КАК Товар,
   СУММА(ПродажаТабличнаяЧасть.Количество) КАК Количество
ПОМЕСТИТЬ ТЧ
ИЗ
   Документ.Продажа.ТабличнаяЧасть КАК ПродажаТабличнаяЧасть
ГДЕ
   ПродажаТабличнаяЧасть.Ссылка = &Ссылка

СГРУППИРОВАТЬ ПО
   ПродажаТабличнаяЧасть.Товар
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
   ОстаткиТоваровОстатки.Склад,
   ОстаткиТоваровОстатки.Товар,
   ОстаткиТоваровОстатки.КоличествоОстаток
ИЗ
   РегистрНакопления.ОстаткиТоваров.Остатки(
         ,
         Склад = &Склад
            И Характеристика = &Характеристика
            И Товар В
               (ВЫБРАТЬ
                  ТЧ.Товар
               ИЗ
                  ТЧ)) КАК ОстаткиТоваровОстатки,
   ТЧ КАК ТЧ

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



Если же её проиндексировать, то план получается таким:



Почему в первом случае оптимизатор выбрал для оператора Nested Loops в качестве ведущей виртуальную таблицу, а не временную? Он разве не может определить кол-во строк во временной таблице, если она не проиндексирована?


v_mazeich

  • Пользователь
  • **
  • Сообщений: 22
  • ФИО: Виталий Александрович
Re: Индексирование временных таблиц
« Ответ #1 : Ноябрь 30, 2015, 05:42:16 am »
Приветствую!

Только на статистику могу грешить) Покажите полный план запроса из 1 случая.
« Последнее редактирование: Ноябрь 30, 2015, 05:57:39 am от v_mazeich »

blizz1205

  • Пользователь
  • **
  • Сообщений: 23
Re: Индексирование временных таблиц
« Ответ #2 : Ноябрь 30, 2015, 11:21:31 am »
Скорее всего здесь проблема в виртуальной таблице остатков. На уровне СУБД запрос к ней преобразуется в выборку из подзапросов, и поэтому оптимизатор не смог определить точное количество строк возвращаемое выборкой. И как следствие выбор оператора Nested Loops с неверной ведущей таблицей. Попробуйте переписать запрос следующим образом: вынести выборку из виртуальной таблицы в отдельную временную таблицу. И проверьте как изменится план запроса.
« Последнее редактирование: Ноябрь 30, 2015, 11:29:52 am от blizz1205 »

v_mazeich

  • Пользователь
  • **
  • Сообщений: 22
  • ФИО: Виталий Александрович
Re: Индексирование временных таблиц
« Ответ #3 : Ноябрь 30, 2015, 11:38:39 am »
Скорее всего здесь проблема в виртуальной таблице остатков. На уровне СУБД запрос к ней преобразуется в выборку из подзапросов, и поэтому оптимизатор не смог определить точное количество строк возвращаемое выборкой. И как следствие выбор оператора Nested Loops с неверной ведущей таблицей. Попробуйте переписать запрос следующим образом: вынести выборку из виртуальной таблицы в отдельную временную таблицу. И проверьте как изменится план запроса.

А разве выборка из подзапросов может негативно влиять на использование статистики или индексов?

Я так понимаю у ТС база с курсов по оптимизации, я выполнил его запрос и вообще он красиво отработал))

blizz1205

  • Пользователь
  • **
  • Сообщений: 23
Re: Индексирование временных таблиц
« Ответ #4 : Ноябрь 30, 2015, 11:53:06 am »
А разве выборка из подзапросов может негативно влиять на использование статистики или индексов?

Нет, ни в коем случае выборка из подзапросов не влияет ни на статистику, ни на индексы. Я даже такого представить не могу себе:)
Я имел ввиду, что выборка из виртуальной таблицы при трансляции на СУБД разворачивается до подзапросов. И оптимизатор не может точно сказать, сколько вернется строк, и считает, что будет всего 1. Поэтому и подставил эту виртуальную таблицу в качестве ведущей для Nested Loops.

Я так понимаю у ТС база с курсов по оптимизации, я выполнил его запрос и вообще он красиво отработал))

Надо бы еще точно знать на какой СУБД выполнялся запрос у ТС.:) План может различаться кардинально)

v_mazeich

  • Пользователь
  • **
  • Сообщений: 22
  • ФИО: Виталий Александрович
Re: Индексирование временных таблиц
« Ответ #5 : Ноябрь 30, 2015, 11:56:36 am »
А разве выборка из подзапросов может негативно влиять на использование статистики или индексов?

Нет, ни в коем случае выборка из подзапросов не влияет ни на статистику, ни на индексы. Я даже такого представить не могу себе:)
Я имел ввиду, что выборка из виртуальной таблицы при трансляции на СУБД разворачивается до подзапросов. И оптимизатор не может точно сказать, сколько вернется строк, и считает, что будет всего 1. Поэтому и подставил эту виртуальную таблицу в качестве ведущей для Nested Loops.

Я так понимаю у ТС база с курсов по оптимизации, я выполнил его запрос и вообще он красиво отработал))

Надо бы еще точно знать на какой СУБД выполнялся запрос у ТС.:) План может различаться кардинально)

Думаю с СУБД мало кто извращается при подготовке))

Если при получении данных из регистра остатков не указывать период, то запрос на СУБД не становится особо сложным, потому что он делает выборку фактически только из таблицы итогов, что в принципе и видно на плане в виде суффикса Т в названии регистра) я все таки думаю на статистику))

blizz1205

  • Пользователь
  • **
  • Сообщений: 23
Re: Индексирование временных таблиц
« Ответ #6 : Ноябрь 30, 2015, 12:49:16 pm »
Не хватает колонки EstimateRows для полноты картины:)

U83

  • Пользователь
  • **
  • Сообщений: 14
Re: Индексирование временных таблиц
« Ответ #7 : Декабрь 01, 2015, 07:34:15 pm »
Всем спасибо! Выполнил тестирование и исправление, всё заработало как надо...

Обратил внимание еще на такую вещь:

|--Clustered Index Seek(OBJECT:([Opt].[dbo].[_AccumRgT22].[_AccumRgT22_ByDims_TRRR] AS [T2]), SEEK:([T2].[_Period]=[@P1] AND [T2].[_Fld19RRef]=[@P2] AND [T2].[_Fld404RRef]=[@P3] AND [T2].[_Fld20RRef]=[tempdb].[dbo].[#tt1].[_Q_000_F_000RRef] as [T3].[_Q_000_F_000RRef]),  WHERE:([Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]<>CONVERT_IMPLICIT(numeric(16,0),[@P4],0) AND [Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]<>CONVERT_IMPLICIT(numeric(16,0),[@P5],0) AND CONVERT(numeric(30,8),CONVERT_IMPLICIT(numeric(38,0),[Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21],0),0)<>CONVERT_IMPLICIT(numeric(30,8),[@P6],0)) ORDERED FORWARD)

Что в данном случае делает условие WHERE?

v_mazeich

  • Пользователь
  • **
  • Сообщений: 22
  • ФИО: Виталий Александрович
Re: Индексирование временных таблиц
« Ответ #8 : Декабрь 02, 2015, 06:16:50 am »
Всем спасибо! Выполнил тестирование и исправление, всё заработало как надо...

Обратил внимание еще на такую вещь:

|--Clustered Index Seek(OBJECT:([Opt].[dbo].[_AccumRgT22].[_AccumRgT22_ByDims_TRRR] AS [T2]), SEEK:([T2].[_Period]=[@P1] AND [T2].[_Fld19RRef]=[@P2] AND [T2].[_Fld404RRef]=[@P3] AND [T2].[_Fld20RRef]=[tempdb].[dbo].[#tt1].[_Q_000_F_000RRef] as [T3].[_Q_000_F_000RRef]),  WHERE:([Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]<>CONVERT_IMPLICIT(numeric(16,0),[@P4],0) AND [Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]<>CONVERT_IMPLICIT(numeric(16,0),[@P5],0) AND CONVERT(numeric(30,8),CONVERT_IMPLICIT(numeric(38,0),[Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21],0),0)<>CONVERT_IMPLICIT(numeric(30,8),[@P6],0)) ORDERED FORWARD)

Что в данном случае делает условие WHERE?

Частичное сканирование кластерного индекса. Поле Количество (Fld21) не включено в состав ключевых полей индекса таблицы итогов а значит поиск по нему не может быть использован. А с чем оно сравнивается я понять не могу)

U83

  • Пользователь
  • **
  • Сообщений: 14
Re: Индексирование временных таблиц
« Ответ #9 : Декабрь 02, 2015, 10:10:02 am »

Что в данном случае делает условие WHERE?

Частичное сканирование кластерного индекса. Поле Количество (Fld21) не включено в состав ключевых полей индекса таблицы итогов а значит поиск по нему не может быть использован. А с чем оно сравнивается я понять не могу)

Но ведь поле Количество это ресурс, а индекс по измерениям в данном случае подходящий. Как говорится, ничего не понимаю )

blizz1205

  • Пользователь
  • **
  • Сообщений: 23
Re: Индексирование временных таблиц
« Ответ #10 : Декабрь 02, 2015, 01:30:57 pm »
Похоже что идет неявное преобразование параметра, которое мешает поиску по индексу. Текст запроса бы увидеть.

U83

  • Пользователь
  • **
  • Сообщений: 14
Re: Индексирование временных таблиц
« Ответ #11 : Декабрь 02, 2015, 02:00:58 pm »
Похоже что идет неявное преобразование параметра, которое мешает поиску по индексу. Текст запроса бы увидеть.

Измерения регистра: Склад, Товар.

ВЫБРАТЬ
   ОстаткиТоваровОстатки.Товар,
   ОстаткиТоваровОстатки.КоличествоОстаток
ИЗ
   РегистрНакопления.ОстаткиТоваров.Остатки(
         ,
         Склад = &Склад
            И Товар = &Товар) КАК ОстаткиТоваровОстатки

 |--Clustered Index Seek(OBJECT:([Opt].[dbo].[_AccumRgT22].[_AccumRgT22_ByDims_TRR] AS [T2]), SEEK:([T2].[_Period]=[@P1] AND [T2].[_Fld19RRef]=[@P2] AND [T2].[_Fld20RRef]=[@P3]),  WHERE:(([Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]<CONVERT_IMPLICIT(numeric(16,0),[@P4],0) OR [Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]>CONVERT_IMPLICIT(numeric(16,0),[@P4],0)) AND ([Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]<CONVERT_IMPLICIT(numeric(16,0),[@P5],0) OR [Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]>CONVERT_IMPLICIT(numeric(16,0),[@P5],0)) AND (CONVERT(numeric(30,8),CONVERT_IMPLICIT(numeric(38,0),[Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21],0),0)<CONVERT_IMPLICIT(numeric(30,8),[@P6],0) OR CONVERT(numeric(30,8),CONVERT_IMPLICIT(numeric(38,0),[Opt].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21],0),0)>CONVERT_IMPLICIT(numeric(30,8),[@P6],0))) ORDERED FORWARD)