Вопрос к знатокам SQL! Долго идет реструктуризация бух.регистра в 8.2 #696631

Проблема: оооочень долго идет реструктуризация регистра бухгалтерии в 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( 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 > 0 ORDER BY _AccRgED10346._RecorderTRef, _AccRgED10346._RecorderRRef, _AccRgED10346._LineNo, _AccRgED10346._Correspond',N' numeric,@P2 varbinary,@P3 varbinary,@P4 numeric,@P5 numeric,@P6 varbinary,@P7 varbinary,@P8 varbinary,@P9 varbinary,0 varbinary',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 > OR _AccntRegED10346._RecorderTRef = AND _AccntRegED10346._RecorderRRef > @P2 OR _AccntRegED10346._RecorderTRef = AND _AccntRegED10346._RecorderRRef = @P2 AND _AccntRegED10346._LineNo >= CAST(@P3 AS NUMERIC(1,0)) ORDER BY _AccntRegED10346._RecorderTRef, _AccntRegED10346._RecorderRRef, _AccntRegED10346._LineNo, _AccntRegED10346._Correspond', N' varbinary,@P2 varbinary,@P3 numeric(1,0)', 0x000000C3, 0xAC70001517A2820811E29516EF65FB2B, 4 Что можно сделать, кроме как сказать большое спасибо программистам фирмы 1С ? p.s. заметил, что если убрать "exec sp_executesql...", то запрос выполняется мгновенно, а с "exec sp_executesql..." - долго.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

проверь, есть ли в индексе "_AccRgED...._ByRecorder_RNN" поле _Correspond. Если нет - добавь. Если нет индекса, то запили индекс по ([_RecorderTRef] ASC,[_RecorderRRef] ASC,[_LineNo] ASC,[_Correspond] ASC) Если такой индекс есть, то перестрой его чтобы он дефрагментировался и статистика обновилась. Если не поможет, то стоит проверить, а не распараллеливается ли план запроса. Если распараллеливается, то запретить распараллеливаение.

Проверил - всё есть. Перестраивал все индексы в этой таблице, поставил maxdop=1 Результаты те же. Для эксперимента отключал использование этого индекса - всё стало только в 100 раз медленнее :) Создавал новую базу в 8.2 путем загрузки cf - индексы такие же. Сравнивал с индексами типовой БП 2.0 - такие же. Делал выгрузку/загрузку через dt (более мелкой аналогичной базы). Те же грабли, явно заметно замедление счетчика при реструктуризации. У кого-нибудь есть возможность проверить время реструктуризации бух.регистра хотя бы на 1 млн. записей в 8.2 (добавить субконто в конфигураторе к любому счету)? Заметно замедление в ходе реструктуризации (счетчик внизу)?

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

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

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

Ну тогда надо шаманский бубен включать: 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 - позволяет не строить план выполнения запроса каждый раз.

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

"1с судя по планам часто хватает не тот индекс. " Я с таким сталкивался только в ситуации вида ГДЕ Номенклатура В (Выбрать Таблица.Номенклатура ИЗ Таблица) лечиться заменой на внутреннеесоединение.

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

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

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

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

Где-то 5 млн. записей реструктурируются около 1.5 часов. Насчет замедления не знаю, не наблюдал. 8.2, БП 2.0 Как вариант, попробовать разные релизы 8.2 -- 18, 17, 16, 15 ... Может в каком-то из них регистр бухгалтерии быстрее пересчитывается => на этот релиз и перелезть.

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

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

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

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

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

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

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

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

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

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

И еще результат такого запроса:

Ну или вот такую штуку можно выполнить, а то может там сервер уже оборался про отсутствующие индексы: SELECT  TOP 10 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;

Не глупите. Все уже описано в

утром и вчера было так сейчас так (я сегодня по-всякому пробовал индексы перестраивать)    пишет про таблицы другой БД

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

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

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

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

8.2.19.68 у меня

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

"План запроса бы." Все в 48

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

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

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

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

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

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

Уже делал с fullscan.

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

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

То, что проблема с неселективным значением первой колонки и большим выражением и так понятно. Вопрос в том, что подставить серверу, чтобы он правильный план сгенерил Попробуй создать такой индекс: CREATE unique NONCLUSTERED INDEX [_AccRgED10346_ByRecorder_RNN_2] ON [dbo].[_AccRgED10346] ( DESC - чтобы попробовать обмануть оптимизатор unique - чтобы попробовать обмануть оптимизатор INCLUDE - чтобы не лезть в кластеризованный

Да. См. .

Ребята! Счастье пришло от релиза 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, _AccRgED10346._RecorderRRef, _AccRgED10346._LineNo, _AccRgED10346._Correspond', N'@P1 numeric(1,0),@P2 varbinary,@P3 varbinary', 2, 0xAE240015178262C811DE177710214CB3, 0x000000F0

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

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

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

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

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

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

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

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

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

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

Да.

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