ADODB: Прямая запись и другие операции с SQL - таблицами (MS SQL server 2000)
Интеграция - Внешние источники данных
В каком случае имеет смысл прямая запись в SQL-таблицу?
Когда может понадобиться прямая запись в SQL-таблицу на платформе 1С 8.1?
В нашем случае таблица регистра 1С8.1 использовалась как источник для перекачки данных в другую учетную базу. Некоторое время все работало средствами 1С. Но настал момент, когда процесс записи регистра стал занимать неприлично много времени. При этом все данные в регистре «остро необходимы».
Как ускорить процесс записи в SQL-таблицу
После некоторых мытарств (превзойти 1С в плане оптимизации работы с SQL мне не удалось), встал вопрос об индексации таблицы. В итоге было решено скопировать таблицу регистра в отдельную таблицу и убить все индексы, что в разы ускорило процесс прямой записи.
Далее описана реализация процесса записи в SQL-таблицу без индексов.
Пример ТЗ
Разберем на конкретном примере.
Допустим, что перед нами стоят задачи:
- Собирать по определенным счетам проводки за период в разрезе организаций (Орг) и сценариев (Сцен), обрабатывать эти данные по известным правилам и записывать данные в таблицу отдельной базы
- Записи за период по организациям и сценариям должны перезаписываться
- Необходимо архивировать таблицу в виде копий на дату
- Using Connection Pooling with SQL server
- Understanding ADO Transactions with MS SQL Server
- Использование библиотеки ADO
- Руководство по производительности загрузки данных (SQL server 2005 и SQL server 2008)
- Справочник по Transact-SQL (компонент Database Engine)
- ADO CreateParameter Metod
Реализация
Создание базы
Для создания базы нам потребуется "Enterprise Manager" (рис.1)

Рис 1.
Открыв "Enterprise Manager". позиционируемся на папке "Databases" и щелчком правой кнопки мыши создаем новую базу (рис.2).

Рис 2.
Создаем базу (рис.3).

Рис 3.
Настройка базы изображена на рис.4 и 5

Рис 4.

Рис 5.
(T-SQL:
Create DataBase MyBase
Alter DataBase MyBase SET Recovery Simple
Alter DataBase MyBase SET Auto_Shrink ON)
Создание таблицы
Откроем нашу базу, спозициониремся на "Tables" и правой кнопкой мыши - создать новую таблицу (рис. 6)

Рис 6.
Следуя ТЗ, мы имеем "ключевые" поля:
- Период
- Организация (Орг)
- Сценарий (Сцен)
Остальные поля представим в виде основных типов данных (рис. 7). Типы значений и их длина взяты на примере регистра 1С.

Рис 7.
Таким образом, мы имеем таблицу "MyTable" (рис. 8):

Рис 8.
Для корректной работы пишем Организацию и Сценарий в виде ссылок (рис. 8). Это нам понадобиться в дальнейшем.
(T-SQL:
Create Table MyBase.dbo.MyTable (ПолеПериод DateTime, ПолеОргСсылка binary(16), ПолеСценСсылка binary(16), ПолеСтрока nvarchar(100), ПолеБулево binary(1), ПолеСумма decimal(15,2))
)
Форма обработки

Рис 9.
Не претендую на то, что обработка должна выглядеть именно так. Хотелось бы отметить, что процессы обработки данных и их запись в регистр разделены.
Сбор и обработка данных
В данном случае этот процесс не представляет интереса. В конечном итоге имеем таблицу значений "таблицаДанных"
Очистка таблицы, запись в регистр
Будем использовать следующую схему:
--> НачатьТранзакцию
----> ОчиститьРегистр
----> ЗаписатьРегистр
--> ЗафиксироватьТранзакцию
Не буду приводить весь код обработки, остановлюсь лишь на основных моментах.
СтрокаКоннекта = "Provider=SQLOLEDB;Password=sdR$543Qr;Persist Security Info=True;User ID= userSQL;Initial Catalog=MyBase;Data Source=serverSQL";
//Источник получения GUID: Mista.ru

// конец источника
Далее собственные изыскания:

Фрагмент кода очистки и записи в регистр:



Необходимо отметить, что в приведенном выше фрагменте кода "FIRM" - Организация, "BASE" - Сценарий, "Entity_Name" - пример строкового значения, "InRep" - булево и "SUMM" - сумма.
Архивирование таблиц
Сразу хочу оговориться, что архивирование происходит путем копирования таблицы на определенную дату.

Вступайте в нашу телеграмм-группу Инфостарт
SQL ADODB прямая запись insert
Вы можете заказать платную адаптацию этой статьи под ваши задачи на «Бирже заказов».
- 0% комиссии — оплата напрямую исполнителю;
- Исполнители любого масштаба — от отдельных специалистов до команд под проект;
- Прямой обмен контактами между заказчиком и исполнителем;
- Безопасная сделка — при необходимости;
- Рейтинги, кейсы и прозрачная система откликов.
См. также
Внешние источники данных Разработчик Аналитик Пользователь 1С:Предприятие 8 1C:Бухгалтерия Узбекистан Беларусь Кыргызстан Молдова Россия Казахстан Платные (руб)
Готовое решение для автоматической выгрузки данных из 1С 8.3 в базу данных ClickHouse, PostgreSQL или Microsoft SQL для работы с данными 1С в BI-системах. «Экстрактор данных 1С в BI» работает со всеми типовыми и нестандартными конфигурациями 1С 8.3 и упрощает работу бизнес-аналитиков. Благодаря этому решению, специалистам не требуется быть программистами, чтобы легко получать данные из 1С в вашей BI-системе.
35000 руб.
15.11.2022 33991 53 49
Внешние источники данных Кадровый учет Файловый обмен (TXT, XML, DBF), FTP Перенос данных 1C Разработчик 1С:Предприятие 8 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)
Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактическим удержаниям, НДФЛ, вычетам, страховым взносам из базы Парус 10 учреждений (далее Парус) в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (далее 1С) и начать с ней работать с любого месяца года.
85400 руб.
05.10.2022 14736 17 8
Розничная торговля Внешние источники данных Разработчик Бухгалтер 1С:Предприятие 8 1С:Розница 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Розничная и сетевая торговля (FMCG) Бухгалтерский учет Управленческий учет Платные (руб)
Загрузка данных продаж и инкассаций из сервисов Kit Vending/Kit Shop в 1С. Реализовано в виде Расширения. Мониторинг состояний торговых автоматов.
12200 руб.
10.06.2019 34783 49 25
Зарплата Внешние источники данных Бюджетный учет Перенос данных 1C Системный администратор Разработчик 1С:Предприятие 8 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)
Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактическим удержаниям, НДФЛ, вычетам, страховым взносам из базы Парус 8 учреждений (далее Парус) в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (далее 1С) и начать с ней работать с любого месяца года.
122000 руб.
19.08.2020 30814 28 3
Внешние источники данных Зарплата Бюджетный учет Разработчик Бухгалтер 1С:Предприятие 8 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)
Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактическим удержаниям, НДФЛ, вычетам, страховым взносам из базы Парус 7.хх учреждений (далее Парус) в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (далее 1С) и начать с ней работать с любого месяца года.
85400 руб.
24.04.2017 57759 99 165
Производство готовой продукции (работ, услуг) Внешние источники данных 1С:Предприятие 8 1С:Управление нашей фирмой 1.6 1С:Управление нашей фирмой 3.0 Лесное и деревообрабатывающее хозяйство Россия Управленческий учет Платные (руб)
Обработка предназначена для загрузки файлов, выгруженных из системы Базис-мебельщик, в справочник 1С "Спецификации" для последующих процессов учета и диспетчирования полуфабрикатов и изделий.
10370 руб.
24.06.2021 26935 66 55
Внешние источники данных Розничная торговля Разработчик Бухгалтер 1С:Предприятие 8 1С:Бухгалтерия 2.0 1С:Бухгалтерия 3.0 Здравоохранение, медицина, стоматология Розничная и сетевая торговля (FMCG) Россия Бухгалтерский учет Налоговый учет Платные (руб)
Внешняя обработка обмена данными между системами ЮНИКО и 1С:Бухгалтерия 3.0.
6100 руб.
01.11.2011 55565 129 81
Файловый обмен (TXT, XML, DBF), FTP Внешние источники данных Бухгалтер Пользователь 1С:Предприятие 8 1С:Бухгалтерия 3.0 Здравоохранение, медицина, стоматология Фармацевтика, аптеки Россия Платные (руб)
Внешняя обработка «Загрузка документов из eФарма» создана для программы 1С:Бухгалтерия предприятия, редакция 3.0 (версии Базовая, Проф и Корп). Обработка позволяет загружать документы в базу 1С из файлов выгрузки, имеющих формат .DBF, которые создаются в программе eФарма или eФарма2, а также из любых других программ, формирующих файлы-выгрузки с таким же расширением.
9760 руб.
15.03.2024 2928 5 0
ЗЫ КАК МОДЕРАТОР предупреждаю - говорим только по теме этой статьи, считаю разговоры о лицензии для сабжа оффтопиком и буду удалять посты о лицензии.
Сейчас тема не открывается в IE8.
Подход , описанный в статье :
Откажемся от регистра для хранения информации из-за медленной записи
И будем использовать SQl-таблицы.
- представляется глубоко ошибочным.
Для решения таких задач нужно использовать справочник с табличной частью.
Табличная часть справочника - это обычная таблица SQL без индексов.
На всякий случай (про запись в табличную часть справочника) :
"Если менялись только отдельные строки табличной части или добавлялись новые строки, то будут записаны только измененные или добавленные строки." (с) Радченко с.42. "Профессиональная разработка в системе 1сПредприятие8"
Другими словами запись в табличную часть уже оптимизирована.
Цитата из почти официальной статьи на kb.1c.ru
Индекс Условие
Ссылка + Ключ (Кластерный) Всегда.
Реквизит Объект конфигурации включен в критерий отбора через реквизит "Реквизит" табличной части или для реквизита табличной части установлено свойство "Индексировать".
Я же подразумеваю прежде всего , что таблиная часть не содержит тех обязательных индексов на измерения в регистре и запись в табличную часть осуществляется значительно быстрее , чем в регистр.
Вот начало статьи (думаю, что этого достаточно)
«« Рубрикатор / Просмотр статьи (ID: 20)
Разделы рубрикатора:
/ Техническая информация / 1С:Предприятие 8.1
Индексы таблиц базы данных
Автор: Рупасов Константин (1С, Москва)
ЗЫ вроде что-то такое еще на ИТС есть.
И в "гроссбухе" на 1000 страниц (название не помню) также эта инфа должна быть
Такой подход даёт максимально быструю запись , не требует ADO-соединения к внешней базе.
Что по само по себе является большим выигрышем , по сранению с Вашим подходом.
И цель моего выступления была именно в этом.
Конкретный же сценарий обработки таких данных - это отдельный разговор и требует отдельной статьи и со сравнительным анализом. В планах у меня нет такой статьи.
ок, что есть "оптимизация" по-вашему и что, соответственно вашему определению "оптимизации", оптимизировано в 8 для работы с запросами?
что касается моего ИМХО, то 1ска просто делает запросы к скл как может :)
в скльном "диалекте" 1С есть некоторые "примочки" типа "через" точку, но назвать это "оптимизацией для скл" я бы не риснул
PS Что в черном ящике?"(игра что?где?когда?)
Лучше думать своей головой.
Первая задача 1с-ника изучить встроенные возможности платформы и эффективно их использовать.
Если кто-то крикнул , что ему не хватает встроенных возможностей платформы в типовых учетных задачах -
в 99.99% случаях он просто не умеет решать задачи.
ЗЫ А первая задача 1С-ника - это руководствоватся первой задачей программиста - быстрый, качественный результат за минимум необходимых средств и действий. Вряд ли 1С-ник, если потребуется один раз сравнить таблицы из базы 1С и из другой базы(тоже на SQL) будет использовать 1С.
Для новичков всё-таки это круто очень. Не отквоченный кусок в смысле, а вообще прямой доступ к sql. Можно ведь сломать базу с концами..
Если б я увидел новичка, ковыряющего в 1С-ной sql таблице, отправил бы его на галеры :).
По статье - не нашел как новичку определить, что поле "V8_InfoReg1152_1.V8_Fld6853" это именно нужный ему реквизит из нужного ему регистра. Кроме того, если планируется частое обращение к sql, лучше сразу создать view с названиями таблиц и полей по образу и подобию 1С метаданных и не попадать в ситуацию, когда становится мучительно больно от одной перепутанной цифры во всех этих "reg", "fld" и пр.
Наверное, есть уже удобные и простые обработки автоматически создающие view по метаданным 1С?
ЗЫ Обработок создающих view куча
ЗЫ на работе у меня точно такая штука есть
2. CONVERT(DateTime,'"+Формат(НачалоДня(ДатаНач), "ДФ = ""YYYY.MM.dd HH:mm:ss""")+"' ) не совсем правильно. В соответствии с лучше исльзовать формат { ts '1998-05-02 01:23:56.123' } или { ts '1998-05-02 01:23:56' }
3. Много мелких неаккуратностей: использование "Conn.Execute("BEGIN TRANSACTION");" (а вдруг используется connection pooling?), нигде явно не указаны блокировки (то ли есть уверенность, что режимы по умолчнию подходят, то ли нет понимания темы), не указываются режимы исполнения ADO (т.е. зачем-то получаются рекордсеты при операциях DML), не используются параметры запросов (возможно, запросы бы ускорились) и многое другое
Тут момент такой, что управлять транзакциями с клиента - суть зло. Иногда необходимое зло, но всё равно возникает лишний момент, который надо постоянно учитывать. В данном случае - какая разница где начата транзакция в 1С или в АДО, если внутри транзакции работа сервера приостанавливается чтобы спросить у пользователя: "уверен?". Пользователь может несколько секунд думать, а если выполнение транзакции предполагает какую-то блокировку, то все будут ждать. Плюс автоматом манипулирование транзакциями на клиенте означает, что транзакцию нужно обеспечить в 2 системах (иначе прощай целостность данных). Плюс есть интересная специфика, если используется уровень изоляции readcommited snapshot. В данном-то случае всё относительно гладко - по умолчанию используется уровень изоляции readcommited и блокировка снимается сразу после завершения чтения запроса (тем более, что sysobjects в MS SQL 2005 и выше лишь представление над системными таблицами, и его использование, кстати, желательно исключать, заменяя на INFORMATION_SCHEMA.TABLES).
Еще сразу можно добить, что вообще говоря есть случаи (connection pooling), когда каждая из последовательных команд не гарантирует, что выполнится в одном и том же соединении, но опять же в данном случае, это к счастью не так.
Завтра допишу про остальное.
PS: ой, не заметил, что там в цикле с транзакцией и вопросом "drop table". Тогда вопросу() нет оправдания - блокировки будут накладываться Sch-M и до конца транзакции.
|;
|SET ANSI_WARNINGS ON
|;
|SET NOCOUNT ON
|;
|SET ANSI_NULLS ON
|;
|if exists (select * from sysobjects where Чего там надо ) drop table какую надо
И здесь же сразу можно создать table.
Рекордсеты в этом случае излишне. Серверу, чтобы выполнять операции dml действительно не нужны.
locktype же - ну все зависит от ситуации. Если применительно к данной статье, то не думаю что стоит заморачиватся с connection pooling.
Итак. Про мелочи.
Про моё (не только моё) отношение к управлению транзакциями с клиента я основное сказал. Но есть еще момент. Посылая "BEGIN TRANSACTION" впрямую на сервер мы не учитываем, что некоторые провайдеры (ну и MS SQL тоже, но не в ADO, а в ADO.NET) могут работать в режиме пула соединений. Это прекрасно, что клиент быстро поднимает соединение из пула, вместо "долгого" ожидания создания соединения, но этот механизм сложнее. И глючнее. Т.е. "BEGIN TRANSACTION" гипотетически может уйти не в том соединении, что остальные запросы и "COMMIT", хоть и не в таком тривиальном случае. Нет, конечно, это обычно фиксится (разарботчиком драйвера), но зачем искать грабли, когда у соединения есть методы BeginTrans, CommitTrans и RollbackTrans?
Совсем не понятна конструкция
Conn.Open();
Conn.Execute("BEGIN TRANSACTION");
Conn.Execute(СтрокаЗапроса);
CONN.EXECUTE("COMMIT TRANSACTION");Странное замечание "Следует упомянуть, что для записи в таблицу SQL должны быть права владельца таблицы.". Я могу догадываться, что имелось ввиду (например то, что данному логину предлагается сопоставить пользователя БД со схемой dbo по умолчанию), но право же - неочевидно. Лучше приведите скрипты создания таблиц и логинов - это будет однозначно.
Неаккуратная работа со скобками в куске:
|CONVERT(DateTime,'"+Формат(стр.Период), "ДФ = ""YYYY.MM.dd HH:mm:ss""")+"' ), // и кошерно и работает
|"+ ПолучитьGUIDПоУникальномуИдентификатору( стр.Орг.УникальныйИдентификатор()+",
|"+ ПолучитьGUIDПоУникальномуИдентификатору( стр.Сцен.УникальныйИдентификатор()+",За что вы СокрЛП на строку натравили? Он же её пообкусает. Ладно справа покусает (там многие кусают и не видно), но ведь и слева покусает!
Странно, что транзакции используются, но при перезаписи (как бы единая операция) удаление происходит в одной транзакции, а вставка в другой. Нипанятна.
Drop table в транзакции в цикле с вопросом, как я писал выше - это ваще жесть. По сути - совершенно однопользовательское решение.
Нужность "архивирования" на этих объёмах тоже вызывает большие сомнения. На таблицах объёмом до 100 ГБ в год кластерного индекса по дате зачастую хватает по уши. А дальше можно использовать секционирование.
Запрос к sysobjects идёт без ограничения типов объектов. А вдруг будет процедура с таким именем?
Если уж коннекты закрываете в конце, то почему рекордсеты не закрывать после использования?
Дальше. У метода Execute соединения и команды вообще-то есть параметры, которые являются битовой маской CommandTypeEnum и ExecuteOptionEnum. В частности есть параметр "adExecuteNoRecords", который рекомендуется для выполнения запросов от которых не ожидается результат. Кстати, для читабельности рекомендую использовать конструкции типа
ExecuteOptionEnum = Новый Структура ("adAsyncExecute, adAsyncFetch, adAsyncFetchNonBlocking, adExecuteNoRecords, adExecuteStream, adOptionUnspecified", 16, 32, 64, 128, 1024, -1);Пример работы с параметрами можно найти . Вкратце, плюсы: принципиальная невозможность SQL injection, не нужно думать о представлении констант (чисел, дат), однократная компиляция запроса сервером, экономия сетевого трафика. Минус мне известен один: для некоторых типов для очень простых запросов (в частности инсерт узкую в таблицу без индексов) из-за медленной работы на стыке 1С и COM быстрее в 1С собрать нужный скрипт (но тоже надо уметь) и отправить его на сервер.
Блокировочные хинты. По умолчанию MS SQL использует уровень изоляции READ COMMITTED и блокировку по записям (по возможности) с дальнейшей эскалацией по количеству блокировок. Это в целом удобно, но не в случае массовой загрузки. В случае массовой загрузки для экономии памяти лучше заблокировать таблицу целиком до конца транзакции.
Ну а вообще, если интересует производительная загрузка данных, то лучше чутка подтянуть , чтоб воплотить её в практике. А если хочется сделать пример для новичков, а не суперперегрузки то этот пример должен быть безукоризненно красив в своей простоте: он должен быть в едином стиле написания (а не то Conn, то CONN), он должен быть целостным и выполняемым, он должен быть правильным не только с точки зрения новичка, но и с точки зрения нормального программера.
Респект автору!
Рейтинг: 736
Вы можете заказать платную консультацию или разработку у автора. Будет создан приватный заказ на «Бирже заказов» для автора.
№ 81209
Создание 04.02.11 13:04
Обновление 31.10.13 10:35
Просмотры 53070
Загрузки 0
Рейтинг
61
Комментарии 65
Код открыт Не указано
Рубрики Внешние источники данных
Кому Разработчик
Тип файла Нет файла
Платформа 1С:Предприятие 8
Конфигурация 1C:Бухгалтерия
Операционная система Windows
Страна Россия
Отрасль Не имеет значения
Налоги Не имеет значения
Вид учета Не имеет значения
Доступ к файлу Бесплатно (free)