Проблема: оооочень долго идет реструктуризация регистра бухгалтерии в 8.2.

База на SQL 2008.
Конфигурация - БП 1.6 допиленная (регистр бухгалтерии не трогался).
Платформа 8.2.18.109
Железо - с запасом (более того, проверял на 3х разных серверах, в т.ч. виртуальном: от железа время реструктуризации не очень зависит).

Основная таблица регистра бухгалтерии - 14 млн. записей.
Таблица ЗначенияСубконто - 46 млн.

В версии 8.1.12.101 (с которой недавно перешли на 8.2) реструктуризация регистра бухгалтерии этой базы занимает чуть больше часа.
В 8.2 - в 20 раз дольше !!!

Профайлер показал, что львиную долю времени занимают запросы вида:

exec sp_executesql N'SELECT TOP 1000
_AccRgED10346._Period AS f_1,
_AccRgED10346._RecorderTRef AS f_2,
_AccRgED10346._RecorderRRef AS f_3,
_AccRgED10346._LineNo AS f_4,
_AccRgED10346._Correspond AS f_5,
_AccRgED10346._KindRRef AS f_6,
_AccRgED10346._Value_TYPE AS f_7,
_AccRgED10346._Value_RTRef AS f_8,
_AccRgED10346._Value_RRRef AS f_9
FROM
_AccRgED10346 WITH(NOLOCK)
WHERE
_AccRgED10346._LineNo = CAST(P1 AS NUMERIC(2,0)) AND _AccRgED10346._RecorderRRef = @P2 AND _AccRgED10346._RecorderTRef = @P3 AND _AccRgED10346._Correspond >= CAST(@P4 AS NUMERIC(1,0)) OR
_AccRgED10346._LineNo > CAST(@P5 AS NUMERIC(2,0)) AND _AccRgED10346._RecorderRRef = @P6 AND _AccRgED10346._RecorderTRef = @P7 OR
_AccRgED10346._RecorderRRef > @P8 AND _AccRgED10346._RecorderTRef = @P9 OR
_AccRgED10346._RecorderTRef > P10
ORDER BY
_AccRgED10346._RecorderTRef,
_AccRgED10346._RecorderRRef,
_AccRgED10346._LineNo,
_AccRgED10346._Correspond',N'P1 numeric(10),@P2 varbinary(16),@P3 varbinary(4),@P4 numeric(10),@P5 numeric(10),@P6 varbinary(16),@P7 varbinary(4),@P8 varbinary(16),@P9 varbinary(4),P10 varbinary(4)',16,0xB7CC001517A2820811E3325C82A8515A,0x000000C3,0,16,0xB7CC001517A2820811E3325C82A8515A,0x000000C3,0xB7CC001517A2820811E3325C82A8515A,0x000000C3,0x000000C3


duration начинается с 500 и после обработки 2 млн. записей достигает 3000 !


В 8.1 этот запрос выглядел так (duration крутится около 80):

exec sp_executesql N'SELECT TOP 1000
_AccntRegED10346._Period AS f_1,
_AccntRegED10346._RecorderTRef AS f_2,
_AccntRegED10346._RecorderRRef AS f_3,
_AccntRegED10346._LineNo AS f_4,
_AccntRegED10346._Correspond AS f_5,
_AccntRegED10346._KindRRef AS f_6,
_AccntRegED10346._Value_TYPE AS f_7,
_AccntRegED10346._Value_RTRef AS f_8,
_AccntRegED10346._Value_RRRef AS f_9
FROM
_AccntRegED10346 WITH(NOLOCK)
WHERE
_AccntRegED10346._RecorderTRef > P1 OR
_AccntRegED10346._RecorderTRef = P1 AND _AccntRegED10346._RecorderRRef > @P2 OR
_AccntRegED10346._RecorderTRef = P1 AND _AccntRegED10346._RecorderRRef = @P2 AND _AccntRegED10346._LineNo >= CAST(@P3 AS NUMERIC(1,0))
ORDER BY
_AccntRegED10346._RecorderTRef,
_AccntRegED10346._RecorderRRef,
_AccntRegED10346._LineNo,
_AccntRegED10346._Correspond', N'P1 varbinary(4),@P2 varbinary(16),@P3 numeric(1,0)', 0x000000C3, 0xAC70001517A2820811E29516EF65FB2B, 4


Что можно сделать, кроме как сказать большое спасибо программистам фирмы 1С ?

p.s. заметил, что если убрать "exec sp_executesql...", то запрос выполняется мгновенно, а с "exec sp_executesql..." - долго.

>>если убрать "exec sp_executesql...", то запрос выполняется мгновенно, а с "exec sp_executesql..." - долго.

найди отличия

(1) Но как мне это может помочь?

тьфу, читать разучился...

Места сколько на журнал транзакций выделил? Журнал транзакции сделай сразу больше чем сам файл БД в 2 раза, предварительно его почистив. Это просто рекомендация

1 Вопрос на кой фиг вы переходили если сидите с древней бп на древней платформе?
Обычно меняют и конфу и платформу, а вы решили извратиться - результат на лицо.
Почему столько доп параметров - Видимо в 2.0 структура таблицы другая.
Предлагать откатиться обратно не предлагать?

(3) ldf сильно не растет. На базе с моделью simple всё то же самое.

(4) БП 2.0 нет и не будет.
Базу недавно свернули, поэтому там всего лишь 14 млн записей в основной таблице :)
Просто конвертация в режиме совместимости. Речь про одну и ту же конфу, только под разными платформами.

Транзакшн лог отключи

(7) Отключал (ставил simple)

SELECT TOP 1000

такой запрос по индексу должен выполняться за менее 1 сек

(9)

duration начинается с 500 и после обработки 2 млн. записей достигает 3000 !

Запрос не по индексу отрабатывает

А для 8.1 - по индексу, судя по "duration крутится около 80"

(10) Как ткнуть SQL мордой в индекс?

(12)

1. Переписать запрос
или
2. Добавить свой, годный индекс.

Использовать hint

Но он вам не поможет.

Счаст я гляну в своей базе.

Может, при конвертации из 8.1 какой-нибудь индекс не создался, нужный для 8.2?
Надо глянуть последствия реструктуризации на мелкой базе.

(0) Может режимом совместимости поиграться? При разных режимах в некоторых случаях запрос к SQL - разный.

(17) Сожрут :)
Ладно, посмотрю завтра насчет (16)

Давай подключусь, гляну.

(0)проверь, есть ли в индексе "_AccRgED...._ByRecorder_RNN" поле _Correspond. Если нет - добавь. Если нет индекса, то запили индекс по ([_RecorderTRef] ASC,[_RecorderRRef] ASC,[_LineNo] ASC,[_Correspond] ASC)

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

(20)
Проверил - всё есть.
Перестраивал все индексы в этой таблице, поставил maxdop=1
Результаты те же.
Для эксперимента отключал использование этого индекса - всё стало только в 100 раз медленнее :)

Создавал новую базу в 8.2 путем загрузки cf - индексы такие же.
Сравнивал с индексами типовой БП 2.0 - такие же.

Делал выгрузку/загрузку через dt (более мелкой аналогичной базы). Те же грабли, явно заметно замедление счетчика при реструктуризации.

У кого-нибудь есть возможность проверить время реструктуризации бух.регистра хотя бы на 1 млн. записей в 8.2 (добавить субконто в конфигураторе к любому счету)? Заметно замедление в ходе реструктуризации (счетчик внизу)?

(21) А нет ли там огромных документов с большим количеством проводок (в смысле - хотя бы сотни тысяч в одном документе)? Типа каких-нибудь начальных остатков? Если есть, то скорее всего из-за них статистика нормально не подхватывается.
Тогда надо брать, смотреть план, подставлять какие-нибудь нетипичные индексы.

(21) maxdop при переиндексации влияет только на саму переиндексацию :)

(22) Самые большие документы (число проводок):
11 569
7 196
6 365
5 219
4 711
(23) я в целом для сервера поставил = 1

(24) Ну тогда надо шаманский бубен включать:
1. Если умеешь, то выковырять из трассы план запроса в XML-виде. В нем есть "missing indexes", если их создать, то можно локально проблему обойти (понятно, что при первой же реструктуризации в новой таблице их не будет). Если не умеешь - подскажу, это не сложно
2. Попробовать индекс ([_RecorderTRef] ASC,[_RecorderRRef] ASC,[_LineNo] ASC,[_Correspond] ASC) сделать кластеризованным (и уникальным, если позволяют данные), а по периоду - некластеризованным. Минус тот же, что и в п. 1 - слетит на реструктуризации.

Ну и понятно: эксперименты на копии.

так вроде Маня писал что 8.2 тормознутая и скорость ей вернули только на 8.3. где то он приводил тесты

Можно указывать индекс командой with index().
1с судя по планам часто хватает не тот индекс.
Был запрос который выполнялся в 1с 30 секунд, при указании индекса и запуски через скуль, стал меньше секунды.

exec sp_executesql - позволяет не строить план выполнения запроса каждый раз.

(27) А где посмотреть про with index()?
Я думала, что уже не используется

(27) "1с судя по планам часто хватает не тот индекс. "

Я с таким сталкивался только в ситуации вида

ГДЕ Номенклатура В (Выбрать Таблица.Номенклатура ИЗ Таблица)

лечиться заменой на внутреннеесоединение.

(29) Бгг. Я сталкивался и прямо с симметричной ситуацией :) Здесь часто проблема чуть глубже, чем просто В или СОЕДИНЕНИЕ. Обычно рядом с такой ситуацией какой-то другой косяк структуры.

(29) Но, кстати, "В (ВЫБРАТЬ...)" у 1С издавна работает через пень-колоду. В 8.0 конструктор условия терял. В 8.2-8.3 полный п с составными типами:
Некорректная работа конструкции МоментВремени В (ВЫБРАТЬ ...)
  
Способ воспроизведения:
В любой конфигурации выполнить запрос:
ВЫБРАТЬ
    Док.Номер,
    Док.Дата
ИЗ
    Документ.ПриходныйКассовыйОрдер КАК Док
ГДЕ
    Док.МоментВремени В
            (ВЫБРАТЬ
                ВложенныйЗапрос.МоментВремени
            ИЗ
                Документ.ПриходныйКассовыйОрдер КАК ВложенныйЗапрос)
(Если в конфигурации нет документа ПриходныйКассовыйОрдер, то подставить любой другой в основной и вложенный запрос)
  
Ожидаемое поведение:
Запрос может быть проверен конструктором, у него допустимый синтаксис, ожидается, что запрос выполнится без ошибок
  
Фактический результат:
Ошибка времени выполнения и завершение сеанса работы
Невосстановимая ошибка
Ошибка при выполнении запроса POST к ресурсу /e1cib/logForm:
по причине:
Ошибка SDBL:
Пропущена точка с запятой (pos=105)
  
Некорректная работа конструкции ПолеСоставногоТипа В (ВЫБРАТЬ ПЕРВЫЕ … УПОРЯДОЧИТЬ ПО …)
  
Способ воспроизведения:
В любой конфигурации выполнить запросы:
Запрос 1.
  
ВЫБРАТЬ
    1 КАК Поле1
ПОМЕСТИТЬ ВТ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 2
;
ВЫБРАТЬ
    ВТ.Поле1
ИЗ
    ВТ КАК ВТ
ГДЕ
    ВТ.Поле1 В
            (ВЫБРАТЬ ПЕРВЫЕ 1
                ВложенныйЗапрос.Поле1
            ИЗ
                ВТ КАК ВложенныйЗапрос
            УПОРЯДОЧИТЬ ПО
                ВложенныйЗапрос.Поле1)
;
УНИЧТОЖИТЬ ВТ;
  
Запрос 2.
  
ВЫБРАТЬ
    1 КАК Поле1
ПОМЕСТИТЬ ВТ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "2"
;
ВЫБРАТЬ
    ВТ.Поле1
ИЗ
    ВТ КАК ВТ
ГДЕ
    ВТ.Поле1 В
            (ВЫБРАТЬ ПЕРВЫЕ 1
                ВложенныйЗапрос.Поле1
            ИЗ
                ВТ КАК ВложенныйЗапрос
            УПОРЯДОЧИТЬ ПО
                ВложенныйЗапрос.Поле1)
;
УНИЧТОЖИТЬ ВТ;
  
Ожидаемое поведение:
Запросы могут быть проверены конструктором, у них допустимый синтаксис, ожидается, что запросы выполнятся без ошибок, разница в запросах только в построенном типе поля временной таблицы.
  
Фактический результат:
Первый запрос выполняется, второй завершается с ошибкой времени выполнения и завершением сеанса работы:
Невосстановимая ошибка
Ошибка при выполнении запроса POST к ресурсу /e1cib/logForm:
по причине:
Ошибка SDBL:
ORDER BY недопустим внутри IN с множественным сравнением
  
Способ обхода:
Изменить запрос на эквивалентный:
ВЫБРАТЬ
    ВТ.Поле1
ИЗ
    ВТ КАК ВТ
ГДЕ
    ВТ.Поле1 В
            (ВЫБРАТЬ
                ВложенныйЗапрос.Поле1
            ИЗ
                (ВЫБРАТЬ ПЕРВЫЕ 1
                    ВложенныйЗапрос.Поле1
                ИЗ
                    ВТ КАК ВложенныйЗапрос
                УПОРЯДОЧИТЬ ПО
                    ВложенныйЗапрос.Поле1
                ) КАК ВложенныйЗапрос)
  
  
Некорректные результаты запроса, содержащего конструкцию ПолеСоставногоТипа В (ВЫБРАТЬ… )
  
Способ воспроизведения:
В любой конфигурации выполнить запросы:
Запрос 1.
  
ВЫБРАТЬ
    1 КАК Поле1
ПОМЕСТИТЬ ВТ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 2
;
ВЫБРАТЬ
    ВТ.Поле1
ИЗ
    ВТ КАК ВТ
ГДЕ
    ВТ.Поле1 В
            (ВЫБРАТЬ ПЕРВЫЕ 1
                ВложенныйЗапрос.Поле1
            ИЗ
                ВТ КАК ВложенныйЗапрос);
УНИЧТОЖИТЬ ВТ;
  
Запрос 2.
  
ВЫБРАТЬ
    1 КАК Поле1
ПОМЕСТИТЬ ВТ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "2"
;
ВЫБРАТЬ
    ВТ.Поле1
ИЗ
    ВТ КАК ВТ
ГДЕ
    ВТ.Поле1 В
            (ВЫБРАТЬ ПЕРВЫЕ 1
                ВложенныйЗапрос.Поле1
            ИЗ
                ВТ КАК ВложенныйЗапрос);
УНИЧТОЖИТЬ ВТ;
  
Ожидаемое поведение:
Одинаковые результаты для первого и второго запроса (результат – 1 строка)
Фактический результат:
В первом запросе возвращаемый результат – 1 строка, во втором – 2 строки.
Замечание:
Причина некорректного результата видна из технологического журнала: коррелированный запрос строится без учета выражения «Первые 1».

Проблема в условии

_AccRgED10346._RecorderTRef >@P10

У отдельного _RecorderTRef - низкая селективность и оптимизатор SQL решает сделать IndexScan по всему индексу.

Как это решить - не могу предложить.

Кроме как спросить 1С -  зачем этот запрос и попросить переписать.

(21) Где-то 5 млн. записей реструктурируются около 1.5 часов.
Насчет замедления не знаю, не наблюдал.
8.2, БП 2.0

Как вариант, попробовать разные релизы 8.2 -- 18, 17, 16, 15 ...
Может в каком-то из них регистр бухгалтерии быстрее пересчитывается => на этот релиз и перелезть.

(21)
> 5 млн. записей собственно реструктурируются минут 15, потом
пересчет итогов ночь (или ее часть, точно не знаю, с утра прихожу)
Кстати в понятие сабжа реструктуризация входит пересчет итогов ?

(35)
нет . если не менялся состав измерений

(35) Входит, но это - потом, автор до этого не дошел.
(36) Плохо, МихаилМ, плохо. Субконто остаточное - это и есть измерение.

(34) Это быстро.
У меня 5 млн. сутки лопатились, потом просто снял процесс, бессмысленно ждать 14 млн. Дальше замедление катастрофическое начинается.

(38) Все-таки у тебя какие-то особенности с сервером, может быть с дисками проблема, попробуй позамерять. Или с SQL - может Service pack не последний?
Не так давно вытаскивал УПП из дремучего релиза, еще редакции 1.2, сейчас 1.3. Рестуктуризация Хозрасчетного регистра заняла 180 минут, при количестве записей в основной таблице регистра - 38,5 млн.
Платформа 8.2.13
PS Замерял сейчас 1 млн записей - 7 минут рестуктуризации, замедления не наблюдал. Duration запросов вида (0) 10-20.

(39) У меня duration был на уровне 200-300.
Но это, возможно, зависит от счета, на который субконто добавляется (а, точнее, количества движений по этому счету)

(39) Твои цифры примерно соответствует скорости, с которой реструктуризация шла у нас в 8.1

(41) У тебя какой счет?

55.04 - ручные операции и платежки. Очень мало проводок по нему.

(43) Покажи уже план выполнения (желательно XML). Может там всё элементарно.

(43) И еще результат такого запроса:
DBCC SHOW_STATISTICS (N'[dbo].[_AccRgED666]', N'_AccRgED666_ByRecorder_RNN')

(43) Ну или вот такую штуку можно выполнить, а то может там сервер уже оборался про отсутствующие индексы:

SELECT  TOP 10
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

(45) (46) Не глупите. Все уже описано в (32)

(44)

утром и вчера было так
https://dl.dropboxusercontent.com/u/67681686/index.png

сейчас так (я сегодня по-всякому пробовал индексы перестраивать)
https://dl.dropboxusercontent.com/u/67681686/index2.png
https://dl.dropboxusercontent.com/u/67681686/test.SQLPlan.xml


(45)  https://dl.dropboxusercontent.com/u/67681686/showstatistics.png

(46) пишет про таблицы другой БД

тьфу... коряво вставляется

(31) выберите во временную таблицу вложенный запрос, взлетит - уверен.

(50) А теперь иди и скажи это серверу 1С.

в 19 релизе говорят что-то прооптимизировали

(52) 8.2.19.68 у меня

(47) Проверить не на чем. План запроса бы.
(50) Что-то я вложенный запрос в упор не вижу в сабже.

(54) "План запроса бы."

Все в 48

(0) А все таки - в каком режиме совместимости работает платформа? Уж не в 8.1 ли?

Очевидно же!

Сколько там, в многомиллионной табличке полей с документов НЕтекущего типа ? Почти все. Все миллионы записей. Из которых потом береться 1000 верхних.

на партнерке уже спрашивал?

с документов НЕтекущего типа ?

->

с документов больше текущего типа ?

(58) Есть тема. Пока по нулям.
Ну ты то хоть понял проблему?

(0) Все читать лень, выполни пересчет статистики(по большой таблице) только обязательно с фулсканом.(без фулскана могут быть дырки в статистике которые очень дорого обходятся) Уверен что поможет. Если нет - то просто проблему не решить.(может диски убитые и т.п.)

(61) Уже делал с fullscan.

(60) Все, все посмотрел, сорри. Мда... Может, соорудить ему временно индекс с полным покрытием под запрос?

(63) Что ты имеешь ввиду под полным покрытием?
Все поля запроса - в индексе?

(47) То, что проблема с неселективным значением первой колонки и большим выражением и так понятно. Вопрос в том, что подставить серверу, чтобы он правильный план сгенерил
(48) Попробуй создать такой индекс:
CREATE unique NONCLUSTERED INDEX [_AccRgED10346_ByRecorder_RNN_2] ON [dbo].[_AccRgED10346]
(
    [_RecorderTRef] DESC,
    [_RecorderRRef] DESC,
    [_LineNo] DESC,
    [_Correspond] DESC,
    [_KindRRef]
)
INCLUDE (_Value_TYPE, _Value_RTRef, _Value_RRRef)
WITH (SORT_IN_TEMPDB = ON) ON [PRIMARY]

DESC - чтобы попробовать обмануть оптимизатор
unique - чтобы попробовать обмануть оптимизатор
INCLUDE - чтобы не лезть в кластеризованный

(64) Да. См. (65).

Ребята! Счастье пришло от релиза 8.2.15: там запрос реструктуризации аналогичен 8.1:

exec sp_executesql N'SELECT TOP 1000
_AccRgED10346._Period AS f_1,
_AccRgED10346._RecorderTRef AS f_2,
_AccRgED10346._RecorderRRef AS f_3,
_AccRgED10346._LineNo AS f_4,
_AccRgED10346._Correspond AS f_5,
_AccRgED10346._KindRRef AS f_6,
_AccRgED10346._Value_TYPE AS f_7,
_AccRgED10346._Value_RTRef AS f_8,
_AccRgED10346._Value_RRRef AS f_9
FROM
_AccRgED10346 WITH(NOLOCK)
WHERE
_AccRgED10346._LineNo > CAST(@P1 AS NUMERIC(1,0)) AND _AccRgED10346._RecorderRRef = @P2 AND _AccRgED10346._RecorderTRef = @P3 OR
_AccRgED10346._RecorderRRef > @P2 AND _AccRgED10346._RecorderTRef = @P3 OR
_AccRgED10346._RecorderTRef > @P3
ORDER BY
_AccRgED10346._RecorderTRef,
_AccRgED10346._RecorderRRef,
_AccRgED10346._LineNo,
_AccRgED10346._Correspond', N'@P1 numeric(1,0),@P2 varbinary(16),@P3 varbinary(4)', 2, 0xAE240015178262C811DE177710214CB3, 0x000000F0

(67) вот же ж изврат :)

(65) Сделал (пришлось вырубить _AccRgED10346_ByRecorder_RNN, иначе к нему цеплялся)
По _AccRgED10346_ByRecorder_RNN_2 делает Index Scan :(

(67) А потом 1С выпустит новую платформу..
Ответь про режим совместимости!

(70) Исключительно в режиме совместимости 8.1. И другого не предвидится.
Думаешь, 1С будет отдавать другой запрос серверу?

(71) КОНЕЧНО! Включай хотя бы режим 8.2.13

(71) Читал инфу от 1С, где правда не помню. Режим совместимости 8.1 - это тестовый, переходный режим, только для того, чтобы подготовить конфу к режиму 8.2

(73) Ты запросы смотрел в режиме совместимости и без него? :)
8.2.16 - тоже ОК
В общем, когда понадобится реструктуризация, буду делать это на 8.2.16.
Уф....

(74) Конкретно твои запросы, не смотрел. Но то, что платформа генерит разные запросы к SQL в разных режимах совместимости - это факт, натыкался.

(67) Ну вот, как я тебе и советовал.
Просто помниться, подобное уже у кого-то было и лечилось именно откатом платформы до какой-то версии.
Ну что ж, отлично.

(74)
Ну а почему мы на 8.2.18 без проблем делаем ?
Режим совместимости ?

(77) Да.

Я ж тоже без проблем делаю без режима совместимости.