Определение имен таблиц MSSQL
Структура базы данных 1С весьма запутана и состоит из малозначимых для человека названий. 1С содержит функцию определения структуры хранения по имени объекта. В основу разработки положена эта функция ПолучитьСтруктуруХраненияБазыДанных, которая согласно русскому названию возвращает описание структуры. В этой структуре важны 2 поля Назначение, которое должно быть равно «Основная», и название таблицы ИмяТаблицыХранения.
Определение смещения дат
Таблица _YearOffset содержит число, обозначающее смещение года дат. Оно принимает значение 0 или 2000. Так со смещением 2000 дата 01.01.2014 будет храниться в базе данных как 01.01.4014. Соответственно при отборе по датам (удаление происходит за период времени) нужно учитывать смещение. Смещение можно получить следующим кодом 1С:
командаСмещение = sqlConnection.CreateCommand();
командаСмещение.CommandText = "select top 1 Offset from _YearOffset";
командаСмещение.CommandTimeout = timeout;
чтениеСмещение = командаСмещение.ExecuteReader();
Если чтениеСмещение.Read() Тогда
Смещение = чтениеСмещение.GetInt32(0);
КонецЕсли;
чтениеСмещение.Dispose();
Установка пометки на удаление документов
Имея названия таблиц документов и зная, что поля _Date_Time, _Marked и _Posted отвечают за дату, отметку об удалении и отметку о проведении соответственно, можно одним SQL-запросом пометить их все на удаление. Делается это так:
командаДокумента = sqlConnection.CreateCommand();
командаДокумента.CommandText = "UPDATE " + строкаТаблицы.ИмяТаблицыХранения + " SET _Marked = 0x01, _Posted = 0x00 WHERE _Date_Time BETWEEN @StartDate AND @EndDate";
командаДокумента.Parameters.AddWithValue("@StartDate", StartDate);
командаДокумента.Parameters.AddWithValue("@EndDate", EndDate);
командаДокумента.ExecuteNonQuery();
командаДокумента.Dispose();
Установка пометки на удаление в журналах документов
Не смотря на установку отметки на удаление у документов, в журналах документов хранятся дубли отметок об удалении на каждый документ. Список журналов, где участвует документ можно получить из метаданных документа так: Метаданные.ЖурналыДокументов
Отметка на удаление через поля _Marked и _Posted происходит аналогично через команду:
командаЖурналов.CommandText = командаЖурналов.CommandText + "UPDATE " + ОсновнаяТаблицаЖурнала + " SET _Marked = 0x01, _Posted = 0x00 WHERE _DocumentRRef IN (SELECT _IDRRef FROM " + строкаТаблицы.ИмяТаблицыХранения + " WHERE _Date_Time BETWEEN @StartDate AND @EndDate);"
Удаление движений регистров
При удалении документов 1С удаляет движения документа по регистрам. В случае прямого доступа эти движения нужно удалить самостоятельно. Список регистров можно получить через метаданные ДокументМетаданные.Движения.
Команда, которой выполняется удаление движений следующая:
командаРегистров.CommandText = командаРегистров.CommandText + "DELETE FROM " + ОсновнаяТаблицаРегистра + " WHERE _RecorderRRef IN (SELECT _IDRRef FROM " + строкаТаблицы.ИмяТаблицыХранения + " WHERE _Date_Time BETWEEN @StartDate AND @EndDate);";
Заключение
Как оказалось, добиться убыстрения работы 1С примерно на 2 порядка не так сложно, достаточно выполнить 3 вида команд. В конечной обработке логика расширена за счет выбора документов по видам, добавлением таймаута, добавлением транзакции, пакетным выполнением команд.
PS. Список возникающих проблем и пути устранения:
1. Обработка игнорирует документы, где запрещено проведение, например, корректировка записей регистров. В корректировке записей регистров удаление документа связано со снятием активности записей регистров.
2. Результат удаления не отражается в планах обмена. Решается одновременным запуском обработки в связанных базах.
3. Не затрагивает таблицы итогов. Решается пересчетом итогов через Конфигуратор-Тестирование и Исправление-Пересчет итогов.
Саму обработку можно скачать здесь:
ПометкаУдаленияПрямымЗапросом.epf (13,77 kb)
Комментарии (33)
alexpp
14.08.2015 14:07+5командаСмещение = sqlConnection.CreateCommand();
чтениеСмещение = командаСмещение.ExecuteReader();
командаДокумента.ExecuteNonQuery();
Глаза начинают кровоточить от такого… Уж если есть sqlConnection.CreateCommand(), ExecuteReader() — то почему и переменным не давать названия латиницей? Или это у 1С так принято?
Elisy
15.08.2015 06:35+7Объяснение простое. В статью попал только код, передающий суть метода. Остальной код, связанный с обработкой событий интерфейса, обращениям к метаданным не попал, чтобы не запутать читателей. Получается, что из общего объема кода код обращений к .Net занимает процентов 10-20. Целесообразно, чтобы общий стиль кода был русский с английскими вкраплениями. Да и 1С-никам, которые будут доделывать под себя метод будет привычнее.
В связи с тем, что ваше негодование разделяют многие в разных статьях про 1С, у меня появляются вопросы:
1. Почему русский код считается плохим стилем в русскоговорящей стране?
2. В проектах .Net/Asp.Net тесной интеграции с 1С вы будете каждый объект/поле схожих с 1С данных переводить на английский? Как следствие держать в голове словарь соответствий?
3. Попробуйте перевести быстро термины из кода статьи: документ, журнал, регистр. Сколько уйдет времени на правильный перевод с тем смыслом, что закладывает 1С в эти типы? Когда я занимался в свое время переводом конфигураций, я брал эти термины из помощи 1С, благо переводы там были. Это занимало время. А есть случаи посложнее: перевод Контрагент, ДоговорКонтрагента, ПриходныйКассовыйОрдер, Номенклатура.
foxkeys
14.08.2015 14:25+6Вообще-то, вопрос прямого доступа к БД в 1С является критически важным для сложных проектов.
В частности, в компании где я работал, управление структурой изделия и ряд других функций производственного блока реализованы в виде хранимых процедур и функций. Т.к. «штатный» 1С код на два порядка медленнее, что делает операции, по сути, невозможными.
На уровне SQL операция выполняется за 20-30 сек, в 1С, соответственно, 30-50мин.
И дело даже не в комфорте, просто нужные дневные операции при этом невозможно ввести даже за полные сутки. А не то, что за рабочий день…
К слову, триггеры исполняются в рамках транзакции запущенной 1С — что и позволяет исполнять произвольный код в нужный момент и соблюдать целостность выполняемого как 1С так и MSSQL кода.
Самое сложное в этой схеме — при изменении любой задействованной таблицы в конфигураторе необходима перекомпиляция ХП (т.к. меняются имена таблиц/полей.) Однако, вопрос решается запуском специальной обработки после сохранения конфы.
В целом — работоспособность схемы проверена более чем надежно. Система работает уже порядка 6 лет (хотя я и уволился — но был там буквально пару месяцев назад, поддерживаю сотрудничество).
Кстати, предприятие входит в топ-10 налогоплательщиков региона. Т.е. совсем даже и не мелкое. А структура изделия может включать в себя до 20000 ДСЕ (детале-сборочных единиц). И 20к это не лимит, это имеющиеся реальные изделия. Вообще, формального, лимита на размер структуры нет.
P.S. версия 8.1/8.2 поверх MSSQL 2005
P.P.S. — убей не пойму, что мешает 1С сделать подобный инструментарий штатным, не требующим плясок с бубном и прочей фигни, расширив таким образом, области применимости 1С в разы…khim
15.08.2015 11:05+4Мдаа… В своё время, будучи студентом, я помогал перевести систему с Искры (кажется 226й) на клон XT (да-да, 4.77Mhz, 640KiB памяти, EGA монитор). И вот там тоже было порядка 20к «имеющихся реальных изделий». Мы изрядно повозились, но вписались. И вот теперь, имея в 1000 раз более быструю систему и в 1000 раз больше памяти люди снова решают ту же задачу и снова не не вписываются «в полные сутки, а не то, что в рабочий день».
Всё-таки удивительная это штука: прогресс.
m08pvv
16.08.2015 17:20Налицо действие закона Вирта
khim
17.08.2015 07:40Который, в свою очередь, прямое следствие Закон Мура. Части программ, которые упираются в аппаратуру вылизываются так, как только можно, но большая часть ресурсов уходит на 100500 слоёв абстракции, всевозможные аббревиатуры типа ORM или SOLID тоже небесплатны. Так что любая программа ускоряется только до того состояния, чтобы её можно было использовать — и не более того.
Aclz
14.08.2015 14:47+1В конечной обработке логика расширена за счет выбора документов по видам, добавлением таймаута, добавлением транзакции, пакетным выполнением команд.
Поскольку в 1С нет ссылочной целостности на уровне СУБД (она переложена на плечи ORM), как не реализована она у вас — такой подход сопряжен с рисками побить базу.
P.P.S. — убей не пойму, что мешает 1С сделать подобный инструментарий штатным, не требующим плясок с бубном и прочей фигни, расширив таким образом, области применимости 1С в разы…
Во-первых, потому, что ORM и прямые запросы — вещи взаимоисключающие.
Во-вторых, платформой вам дадены все методы для определения структуры БД, а ОС — штатные классы для работы с SQL-сервером. Для работы с прямыми запросами этого более чем достаточно.
iliabvf
14.08.2015 17:13+4«Как оказалось, добиться убыстрения работы 1С примерно на 2 порядка не так сложно, достаточно выполнить 3 вида команд» — не знаю плакать или смеяться, никакого ускорения 1С тут нет, вижу только SQL запросы.
Elisy
17.08.2015 06:15Формально 1С убыстряет свою работу, потому что форма со скриптом 1С запускается внутри 1С с обращением к .Net framework.
Infactum
14.08.2015 17:33+5Думаю для тех, кто хотя бы немного знаком с 1С известно, что прямая работа с БД — это нарушение лицензионного соглашения, но оставим этот вопрос.
Если автор так уверен, что его действия содержат полный список операций, которые делает система при установке пометки удаления документов непосредственно средствами платформы, и сами операции настолько неоптимальны, что на их выполнение средставами 1С уходит аж на 2 порядка больше времени, то почему бы не написать, что именно платформа 1С делает не так? Собрать трассировку профайлером — простейшая операция, зато статья получилась бы куда интереснее.
Вот интересно, будь это не 1С, а другая ORM, тоже бы полезли менять БД напрямую, без понимания работы системы?sen77
14.08.2015 18:111с очень любит делать вложенные запросы.
Скорее всего, вместо того, чтоб приджоинить таблицу 1с в цикле запускает запрос на удаление каждого документаAclz
14.08.2015 18:25+2Начать можно с того, что в 1С удаление документа это не просто удаление записи из таблиц, но и проверка прав пользователя на удаляемые объекты (или даже отдельные записи), поиск ссылок на связанные объекты, блокировка всех полученных участвующих в удалении таблиц, выполнение программных обработчиков, связанных с удалением, регистрация производимых изменений в журналах и т. д. и т. п.
sen77
14.08.2015 19:51Никто не говорит, что этого делать не надо.
Почему при удалении 100млн строк нужно столько же раз проверить права, выполнить процедуры вместо того, что бы сделать это 1 раз?
Infactum
14.08.2015 20:48+2Если бы вы удаляли 100 млн. строк из регистра (ближайщие аналог таблицы СУБД), то права проверились только 1 раз.
А удаление каждого документа это отдельная операция.
Я не спорю, в системе могла бы присутствовать операция пакетного удаления, которая формировала бы единый запрос. Но зачем? В реальных рабочих базах операция массового удаления данных вообще возникать не должна.
И не стоит забывать, что мы сейчас говорим только о пометке на удаление. А потом еще нужен будет контроль ссылочной целостности, на которой уйдет не меньше времени.DrPass
17.08.2015 10:41В реальных рабочих базах операции массового удаления или массового изменения документов возникают регулярно. Можно сколько угодно рассуждать о том, что это, дескать, неправильная архитектура, кривые руки или плохие бухгалтеры. Но в живой организации всегда происходят какие-то изменения, которые затрагивают учёт глобально. Например, ввели отдельный склад для учёта несуществующих товаров, которые купили официально, продали «в чёрную», и потом надо их отмыть. Учтенные документы по таким товарам, соответственно, надо массово перепровести. Сбойнул синхронизатор документов между ERP и 1C, надо грохнуть залитые им документы и перезагрузить их, и т.д.
В принципе, ничего крамольного в прямых SQL-запросах нет, это действительно позволяет значительно сократить время обработки больших объемов данных… если, конечно, чётко понимаешь, что делаешь. Мы в свое время даже вставляли учтённые документы в Microsoft Dynamics NAV, воспроизводя все необходимые записи в журналах.
Elisy
17.08.2015 07:09-1Думаю для тех, кто хотя бы немного знаком с 1С известно, что прямая работа с БД — это нарушение лицензионного соглашения, но оставим этот вопрос.
Официальная позиция 1С по прямому доступу многим известна, не стоит так явно выдавать свою ангажированность. Тем не менее мыслящим людям рекомендую ознакомиться со статьей 1334 п.1 ГК РФ часть 4 «Исключительное право изготовителя базы данных», статьей 25 п.1 и п.3 «Свободное воспроизведение программ для ЭВМ и баз данных. Декомпилирование программ для ЭВМ» Закона об авторском праве и смежных правах. А также подумать, почему в 1С появилась родная функция ПолучитьСтруктуруХраненияБазыДанных и почему до сих пор не создано ни одного прецедента преследования по этому пункту лицензии. Комментарий выше: «Вообще-то, вопрос прямого доступа к БД в 1С является критически важным для сложных проектов» как бы намекает на ответ.
Если автор так уверен, что его действия содержат полный список операций, которые делает система при установке пометки удаления документов непосредственно средствами платформы, и сами операции настолько неоптимальны, что на их выполнение средставами 1С уходит аж на 2 порядка больше времени, то почему бы не написать, что именно платформа 1С делает не так?
Давайте разбираться спокойно без фанатизма. Где конкретно автор критикует 1С и говорит, что она делает что-то не так? Если бы я дублировал запросы 1С по каждому документу индивидуально, то получил бы схожее время работы.
Собрать трассировку профайлером — простейшая операция, зато статья получилась бы куда интереснее.
Нравится тратить впустую время, изучайте профайлером результат работы 1С и публикуйте статью. Мне не интересны темы, не приводящие к результату. А конкретно данная тема приводит к значительной экономии моего времени и времени пользователей.
Вот интересно, будь это не 1С, а другая ORM, тоже бы полезли менять БД напрямую, без понимания работы системы?
Не нужно делать из 1С священную корову. 1С не лучше и не хуже других программ. Не пойму, что может остановить полезть менять напрямую «другую ORM», если скорость от этой операции возрастет в 100 раз.movsb
17.08.2015 18:46Нравится тратить впустую время, изучайте профайлером результат работы 1С и публикуйте статью. Мне не интересны темы, не приводящие к результату.
Результат будет, точнее, нужно сначала выяснить причину. В статье, к сожалению, не сказано, сколько в базе документов отправляются в небытие.
Если у вас в базе несколько тысяч документов помечаются на удаление больше часа, нужно сделать замер. Выполняющийся дополнительный код на языке 1С можно и нужно оптимизировать.
А вот если у вас в месяц проходит более миллиона документов, тормоза конечно же от их количества. Но здесь такой вопрос: а как вы вообще в такой базе работаете? Не только пометка на удаление, а любая групповая операция будет выполняться днями.
ComodoHacker
15.08.2015 09:42+3Покритикую немного.
1. Что с итогами регистров? Таблицы итогов вы, похоже, не трогаете. Если итоги не пересчитать, база останется в несогласованном состоянии. А если пересчитать по-честному, разница в скорости станет не такой уж впечатляющей. Пересчитывать итоги прямыми запросами, думаю, даже вы не решитесь.
2. При таком удалении не исполняется логика отмены проведения, которая может быть не эквивалентна простому удалению движений. Возможный результат — опять же несогласованная база и ошибки при дальнейшей работе. Значит, данная обработка применима не для всех конфигураций и не для всех документов. Об этом нужно упомянуть в статье.
3. Обработка требует наличия вашей платной внешней компоненты. Об этом тоже нужно предупредить. Хотя в данном случае достаточно обычного ADODB.Connection.
P.S. Вы вообще эту обработку в продакшене использовали? Или она написана исключительно для пиара вашей ВК?
Fragster
15.08.2015 14:05+1Еще и регистрация в РИБ для документов их движений/других данных не согласовывается, не обновляются последовательности документов и прочее и прочее…
Elisy
17.08.2015 07:30Одной из причин выбора метода прямого доступа стал как раз РИБ. В случае с РИБ время на удаление обычными средствами будет больше в разы. Время тратится не только на удаление в одной базе. Время дополнительно тратится на:
1. Формирование пакета обмена. В этот момент пользователям практически не реально работать в базе — обмен занимает основные таблицы.
2. Загрузку пакета обмена в каждую базу с занятием основных таблиц. Пользователям не реально работать в базе. Время на удаление будет сравнимо со временем пометки на удаление в первой базе.
В случае прямого доступа обработка и последующий пересчет итогов запускаются параллельно на всех связанных базах.
Elisy
17.08.2015 07:18Хорошая конструктивная критика. Совсем упустил из виду негативные стороны подхода. Дописал их в конце статьи.
1. Что с итогами регистров? Таблицы итогов вы, похоже, не трогаете. Если итоги не пересчитать, база останется в несогласованном состоянии. А если пересчитать по-честному, разница в скорости станет не такой уж впечатляющей. Пересчитывать итоги прямыми запросами, думаю, даже вы не решитесь.
Пересчет делается через Конфигуратор-Тестирование и Исправление-Пересчет итогов
2. При таком удалении не исполняется логика отмены проведения, которая может быть не эквивалентна простому удалению движений. Возможный результат — опять же несогласованная база и ошибки при дальнейшей работе. Значит, данная обработка применима не для всех конфигураций и не для всех документов. Об этом нужно упомянуть в статье.
В обработке можно исключить документы с «особенной» логикой отмены проведения
3. Обработка требует наличия вашей платной внешней компоненты. Об этом тоже нужно предупредить. Хотя в данном случае достаточно обычного ADODB.Connection.
Обработка без проблем работает на ознакомительной версии. Код открыт. Специальные методы защиты алгоритма не предпринимались. Имея открытый код обработки можно переписать алгоритм под разные методы доступа к СУБД.
P.S. Вы вообще эту обработку в продакшене использовали? Или она написана исключительно для пиара вашей ВК?
Алгоритм отработал в реальной базе. Сама база отработала уже неделю с реальными пользователями.ComodoHacker
17.08.2015 09:56+1Пересчет делается через Конфигуратор-Тестирование и Исправление-Пересчет итогов
Это может съесть всю экономию времени :)
boblenin
16.08.2015 17:26> (SELECT _IDRRef FROM " + строкаТаблицы.ИмяТаблицыХранения + "
Здравствуй SQL Injection.Infactum
16.08.2015 17:51+1Значение строкаТаблицы.ИмяТаблицыХранения это фактически данные, возвращенные системным методом ПолучитьСтруктуруХраненияБазыДанных. Откуда взяться инъекции, если пользователь не может влиять на значение переменной?
Elisy
18.08.2015 06:12Разумное замечание. Тема SQL-инъекций в 1С совсем не изучена. Я пока не знаю, как применить эту технологию конкретно к моему случаю.
Elisy
17.08.2015 06:46В связи со справедливыми комментариями о негативных эффектах прямого доступа дополнил статью:
PS. Список возникающих проблем и пути устранения:
1. Обработка игнорирует документы, где запрещено проведение, например, корректировка записей регистров. В корректировке записей регистров удаление документа связано со снятием активности записей регистров.
2. Результат удаления не отражается в планах обмена. Решается одновременным запуском обработки в связанных базах.
3. Не затрагивает таблицы итогов. Решается пересчетом итогов через Конфигуратор-Тестирование и Исправление-Пересчет итогов.
AlexandrDP
Лучше скрыть/удалить данный данный пост с хабра.
Juggernaut
Вот просто интересно — а почему, собственно? Кому «лучше» от того, что этот пост удалят?
Что движет самозваными цензорами на Хабре?
AlexandrDP
При чем тут цензура.
Карма у автора была, кажется, "+", сейчас 0.
Учитывая, что посты с более ценной информацией получали "-" — дал совет.
Все на усмотрение автора.
saboteur_kiev
По-моему вы слишком торопитесь осудить.
Ну и не забывайте, что к плохому посту могут быть шикарные каменты. Я вот уже чуть ниже полезное прочитал.
Elisy
Все, вроде, впорядке с кармой и рейтингом статьи. Подобных статей в Интернете не нашел почему-то.