Сравнение 200.000 строк

22 марта 2013

Файлы для загрузки:

Информационная база с примером

Файл SQL-базы данных (*.mdf)

Вместо предисловия

На самом деле в статье будет идти речь об обмене/синхронизации данных между информационной базой 1С:Предприятие и некоторой SQL-базой данных. Название статьи лишь отражает способ сопоставления записей таблиц информационных систем, а именно по полям со строковым типом данных (в MS SQL тип "nvarchar"). Если в предыдущей статье "Получение данных из "не 1С"" мы рассматривали лишь получение данных из другой базы для просмотра и формирования отчета, то сегодня поговорим о синхронизации записей двух таблиц.

В примере используется СУБД Microsoft® SQL Server® 2008 Express with Advanced Services.

Синхронизируемые таблицы в SQL-базе и в информационной базе 1С:Предприятия

И так, приступим к рассмотрению примера. Для начала опишем теоретическую часть.

В теории

У нас есть задача выгрузить все записи из справочника "Данные" в некоторую SQL-таблицу в сторонней базе данных. После этого необходимо производить обновление измененных записей уже в таблице справочника. Сопоставление записей выполняются по коду записи (поле "Код" в 1С:Предприятии и "Code" в SQL-таблице "Info"). Справочник "Данные" содержит 200.000 элементов.

Сопоставление таблиц

При первой выгрузке из 1С:Предприятия в SQL-базу "DataDb" (см. скриншот выше) нам необходимо сначала выполнить очистку таблицы "Info" и после добавить записи для всех элементов справочника "Данные".

Уже после этого необходимо обновлять все данные в справочнике на стороне 1С:Предприятия, которые были изменены в SQL-базе данных. При этом, если были добавлены новые записи, то для них должны быть созданы соответствующие элементы в справочнике "Данные". Элементы справочника "Данные" для удаленных записей в SQL-таблице "Info" также должны быть удалены из информационной базы.

Условие измененных записейИзмененными будут считаться записи, у которых дата модификации больше, чем у исходной записи (то есть значение "Modifier" больше значения в реквизите справочника "ДатаМодификации").

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

Практический пример

Добавим обработку "Обмен с SQL базой" в конфигурацию. На форме обработки поместим две команды: "Обновить все записи в SQL-таблице" и "Загрузить элементы из SQL-таблицы". 

Обработка обмена с SQL-базой

Первая команда выполняет очистку таблицы "Info" и последующее добавление записей для каждого элемента справочника. Программный код серверной части этой команды выглядит следующим образом:

Полная выгрузка данных в SQL-таблицу

После выполнения команды обновления всех записей в SQL-таблице состав обеих таблиц будет идентичным. Далее нам нужно реализовать алгоритм обновления элементов справочника "Данные" по измененным записям в SQL-таблице. Как было сказано выше, соответствие между записями в таблицах устанавливается по коду (поле "Код" в справочнике и "Code" в таблице "Info"). В соответствии с этим, серверный код команды "Обновить все записи в SQL-таблице" выглядит следующим образом:

Программный код получения изменений в SQL-таблице "Info" по сравнению с таблицей справочника "Данные"

Основные моменты отмечены в комментариях к коду, поясню некоторые нюансы. Первое, на что стоит обратить внимание - это явная типизация колонок таблицы значений "ТаблицаРезультат". Это необходимо для того, чтобы в дальнейшем таблица значений могла быть передана в запрос и там обработана. Если мы не установим типы значений  в колонке, то запрос сообщит об ошибке, что данный тип нельзя выбрать в запросе или о невозможности сравнения строк неограниченной длины.

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

Текст запроса сравнения двух таблиц

Использование полного соединения позволяет сформировать результирующую таблицу со всеми записями из двух таблиц. Если соответствующей записи одной таблицы не найдена запись для другой таблицы, то будет выведено значение NULL.

Выражение для поля "Modifier" позволяет вывести дату модификации записи. Если в строке нет значения "Modifier", то есть в SQL-таблице нет записи для текущего элемента справочника, то в поле "Modifier" подставляется дата "31 декабря 3000 года". Иначе берется значения из SQL-таблицы. 

Условие на результат выборки запроса позволяет отобрать те записи, с которыми необходимо выполнять какие-либо действия в 1С:Предприятии (создание, обновление или удаление). 

В условии поле "ДатаМодификации" проверяется на тип NULL для того, чтобы в случаях отсутствия элемента в справочнике "Данные" эта запись попала в результат запроса. В дальнейшем будет необходимо создать соответствующий элемент справочника. 

Проверим работу созданной команды. Выполню следующие изменения в SQL-таблице Info:

  1. Удалю элементы с кодами: "000000002", "000000003" и  "000000004".  
  2. Создам новый элемент "Новый элемент из SQL" с кодом "999999999".
  3. Изменю наименование элемента с кодом "000000001".

После выполнения команды "Загрузить элементы из SQL-таблицы" мы увидим следующий результат:

Результат выполнения команды

Алгоритм работает, но насколько быстро?

Заключение о производительности

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

То есть, за 46 секунд платформа получает записи из SQL-таблицы, создает промежуточную таблицу значений, затем выполняет запрос на сравнение двух таблиц (промежуточной и таблицы справочника "Данные") и после обрабатывает результат запроса.

Но что, если обрабатывать результат запроса к SQL-таблице без использования класса "Запрос". То есть используя объектную модель работы с данными.

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

Обработка SQL-запроса к таблице "Info" без использования класса "Запрос"

В этом случае время выполнения команды ставит 4800 секунд! Время выполнения увеличилось в более чем 100 раз!

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

Наиболее востребован такой подход в задачах по интеграции информационных систем. В нашем примере те данные, которые хранятся в SQL-таблице "Info", мы не могли получить в виде объектов информационной базы сразу же   (в виде элементов справочника), приходилось использовать поиск элементов по коду. При использовании запроса для сравнения двух таблиц по строковым полям в конечном счете мы могли получить ссылки на объекты информационной базы достаточно быстро. 


comments powered by Disqus