Спускаемся в 1С 8.2 на уровень Базы Данных (Часть1)

В нашей компании есть собственная разработка на 1С 8.2 платформе. Однажды мы пришли к понимаю что наша система работает не очень быстро. Оставалось понять в каком направлении двигаться, что бы оптимизировать работу системы. После долгох исследований и экспериментов, мы решили в серьез взяться за перенос некоторых операций на плечи СУБД, а именно на плечи MS SQL.

Cтруктура базы данных 1С на уровне СУБД выглядит не совсем внятно.
Постараюсь описать что же из себя представляет эта структура. Описание будет не полное. Постараюсь описать лишь самое интересное и важное, из того что нужно понимать спускаясь на уровень СУБД.

Каждый объект метаданных имеет определенный вид наименования. Например РегистрСведений начинается с _InfoRg, далее идет номер (идентификатор) регистра. А вот таблички начинающиеся с _InfoRgChng это таблицы содержащие в себе регистрацию изменений в регистре. Перечислять в данной статье все префиксы я не буду. Это можно сделать с помощью средсв 1С. По мере необходимости.

Гораздо интереснее рассказать о других особенностях.
Например о том что в каждой таблице есть внутренний уникальный идентификатор. Индексированное поле. В РегистреСведений это поле _SimpleKey. Его тип данных — binary(16), но фактически в нем хранится значение GUID, зашифрованное в binary. У документов таким полем является поле _Document#N_IDRRef. У перечисления _IDRRef. И так далее. Это помимо тех идентификаторов которые мы привыкли видеть в 1С. Хотя в самой платформе мы можем получить значение УУИД — это и будет наш GUID.

Ещё интереснее у нас хранятся данные полей типа «ПланВидовХарактеристик». Точнее те поля, которые могут примнимать разнотипные значения.
Допустим у нас есть Значение. И оно может хранить в себе Строку, Дату, Число, ссылку на справочник клиентов, и ссылку на справочник сотрудников. В 1С мы видим одно единственное поле. На деле же такое поле в базе данных будет иметь ряд полей:
_Fld8818_TYPE(binary(1)) — В данном поле хранится тип значения, который хранится в текущей записи;
_Fld8818_N(Numeric(x)) — Здесь будет храниться тип числа. Тип числа (разрядность и длинна) будет зависеть от настроек в самом конфигураторе 1С;
_Fld8818_T(datetime) — В данном поле будет храниться значение типа ДатаВремя;
_Fld8818_S(nvarchar(1024)) — В этом поле значение в виде строки;
_Fld8818_RTRef(binary(4)) — В данном поле, при условии что в записи хранится ссылка, будет указан тип ссылки. То есть, на какую таблицу ссылается, справочник это или документ, что за документ или справочник;
_Fld8818_RRRef(binary(16)) — А это уже будет сама ссылка на конкретную запись, в конкретной таблице.
Вот такой занимательный формат имеют составные поля.

Все хорошо, но как же узнать какая таблица для чего нужна. Что в ней? И какое поле — что содержит в себе?
В этом нам поможет встроенная функция поставляемая вместе с платформой:

ПолучитьСтруктуруХраненияБазыДанных()

Данная функция возвращает структуру в которой мы можем по имени объекта в МетаДанных, получить имя объекта в базе данных. Точно так же в структуре содержаться и все поля объектов, и их наименования в базе данных. Но здесь уже начинаются подводные грабли. Которых вроде как и нет, и в тоже время они есть.

Важный момент. При вызове метода, обязательно нужно передать во второй параметр значение «Истина». Что это означает? Этот параметр означает будет ли структура отображать данные в формате 1С: Предприятие, либо в формате СУБД. В чем же разница?

Допустим мы отображаем данные в формате 1С: Предприятие.
Например, если мы попытаемся с помощью этой структуры узнать как называется в базе данных поле «Клиент», то получим к примеру такое имя «Fld1234». Вроде бы все хорошо. Но если мы попытаемся написать запрос к MS SQL:

Select Fld1234 From _InfoReg

Мы в 80% случаев — получим ошибку. Почему? А потому что это лишь общий вид наименования поля. Но стоит знать о том что во первых любое имя поля начинается с нижнего подчеркивания. Казалось бы прибавим к наименованию поля символ "_" и делов то! Но нет. Далее ещё интересней. В зависимости от содержимого поля и его типа, поле имеет определенный постфикс в наименовании. Например RRef — это значит что в поле содержится ссылка. А если просто значение то этого постфикса нет. А помните составные типы данных? Там вообще может быть куча различных постфиксов, при этом полей начинающихся на "_Fld1234" будет гораздо больше чем 1. И как же нам обойти это?
Легко. Те кто знает MS SQL, сразу догадались что на помощь придет системное представление INFORMATION_SCHEMA.COLUMNS
С помощью этого представления мы можем отобрать информацию по наименованию таблицы, и по тому ключевому наименованию поля. Пример запроса:

Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like 'ИмяОбъекта(Таблицы)' and COLUMN_NAME like 'ПервичноеИмяПоля%'

Данный запрос выдаст нам ряд полей, имена которых начинаются на "_Fld1234". Нам же останется эти данные обработать в нашей программе для использования в запросах к базе.

Но какие минусы у этого метода? Во первых для того что бы обратиться к базе, нам необходимо настроенное подключение к БД, через 1С. То есть дополнительные настройки. Но они нам в любом случае пригодятся, но представьте, у вас большой запрос. Нужно получить имена 20 полей. И каждый раз при этом обращаться к базе и искать там имена полей? Получать и использовать подключение? Это не очень оптимально. Плюс к тому полученные из базы данные, придется ещё как-то обрабатывать. Дополнительные действия.

Вот тут то нам и приходит на помощь функция

ПолучитьСтруктуруХраненияБазыДанных(,Истина)

Когда значение параметра ИменаБазыДанных = Истина, то функция в результирующую структуру сразу передает всю необходимую информацию по объектам. Включая все физические поля Базы данных. Если поле составное, то в структуре будут видны все физические поля составного поля. Это значительно облегчает нашу работу.

Как же нам использовать отбор в прямых запросах? Как отобрать данные по конкретному документу? Или по конкретному значению?

Все довольно просто, но снова есть нюансы.

Поля формата Дата. По умолчанию при использовании MS SQL сервера, дата 1С в базу помещается с прибавлением к году 2000. То есть дата в системе 1С «01.01.2013» будет выглядеть как «01.01.4013». Но и это ещё не все. Для того что бы в запросе произвести сравнение даты и оно прошло корректно, нам необходимо дату конвертировать в определенный формат.
По умолчанию в базе данных MSSQL используется формат ymd. Это означает что в дате сперва указан год, месяц и затем дата. А выглядит дата следующим образом: 4013-01-01. Для использования в условиях сравнения или для прочих манипуляций нам эту дату нужно обрамлять в опострофы, так же как и строки.

Для преобразования даты в формат SQL я написал для себя такую простенькую функцию:

Функция ДатаВSQL(ЗнДата) Экспорт
	Год = Год(ЗнДата);
	Год = Год + 2000;
	Год = Строка(Год);
	Год = СтрЗаменить(Год,Символ(160),"");
	Месяц = Строка(Месяц(ЗнДата));
	День = Строка(День(ЗнДата));
	Если СтрДлина(Месяц) = 1 Тогда
		Месяц = "0"+Месяц;
	КонецЕсли;
	Если СтрДлина(День) = 1 Тогда
		День = "0"+День;
	КонецЕсли;
	Возврат "'"+Год+"-"+Месяц+"-"+День+"'";
КонецФункции

Данная функция возвращает готовую дату, в нужном формате в виде строки, остается только подставить в текст запроса. Если у вас в MS SQL по каким то причинам установлен иной формат даты, можно на момент исполнения запроса его поменять. Делается это так:

set dateformat ymd

Либо надо будет переделать представление даты в своем запросе.

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

Функция ПолучитьВнутрСсылкуПоНомеру(Номер,Объект) Экспорт
	БуфЗапрос = "Select master.dbo.fn_varbintohexstr([_IDRRef]) From _"+ПолучитьНаименованиеОбъектаБД(Объект)+" Where _Number like '"+Строка(Номер)+"'";
	Возврат ПолучитьЗначениеИзБазы(БуфЗапрос);
КонецФункции

Функция ПолучитьВнутрСсылкуПоКоду(Код,Объект) Экспорт
	БуфЗапрос = "Select master.dbo.fn_varbintohexstr([_IDRRef]) From _"+ПолучитьНаименованиеОбъектаБД(Объект)+" Where _Code like '"+Строка(Код)+"'";
	Возврат ПолучитьЗначениеИзБазы(БуфЗапрос);
КонецФункции

Как видно в коде, мы строим простой запрос, и получаем из базы значение ID, которое храниться в базе данных. Объект — это у нас наименование справочника либо документа, а код — код элемента справочника или документа.
Функция master.dbo.fn_varbintohexstr() — позволяет преобразовать значение формата binary в строку. Но использовать эту функцию — не обазательно.

Полученный ID имеет примерно такой вид: 0xa8ed00221591466911e17da9fd549878
В запросе мы его можем сравнивать как строку

where master.dbo.fn_varbintohexstr(_fld1234RRef) = '0xa8ed00221591466911e17da9fd549878'

Но в таком случае запрос будет отрабатывать дольше. Так как на преобразование в строку тоже нужно время. Поэтому лучше сравнение делать таким образом:

where _fld1234RRef = 0xa8ed00221591466911e17da9fd549878

А давайте представим что нам нужно в запросе сделать левое соединение. И сравнение должно происходить с полем через точку? В 1С это будет выглядеть примерно так

...
Левое соединение Документ.НашДокумент КАК Док
По Регистр.Регистратор.Дата = Док.Дата
...

Как же описать это с помощью MS SQL? В том месте запроса, где описываются соединения, компилятор запросов ещё не знает о том что в таблице регистра есть ссылка на регистратор, и что это в свою очередь есть документ, а у этого документа есть дата. Описать ещё одно соединение? Не поможет. Словом я пытался это сделать всяко. Но в итоге решение свелось к вложенному запросу. (если кто-то найдет реальную альтернативу, буду рад узнать ваш способ).

Выше приведенный фрагмент на чистом SQL будет выглядить так:

...
Left join РегистрСведений.КДМ_ПланированиеБюджета Register
ON ((select _Date_Time from НашДокумент DocPlan where Register.Источник = DocPlan._IDRRef) = НашаТаблица.DocDate)
...

В запросе мы видим, что во вложенном запросе делаем выборку из таблицы документа, где ID документа равен ID который записан в поле нашей таблицы «Источник», и далее полученное значение _Date_Time сравниваем с датой из нашей таблицы. Все логично и просто. Думаю теперь мы понимаем, во что превращаются наши обращения к полям и объектам через точку, в запросах 1С, когда они транслируются на SQL запрос. И теперь становится понятно почему такие обращения затормаживают работу запросов.

Данная тема весьма обширна и в одной статье описать все нюансы не просто. Поэтому на сегодня я думаю ограничимся этим. А в следующих постах я подробнее расскажу о том каким образом в 1С, можно подключиться к базе данных через ADO, для того что бы выполнять прямые запросы. Вместе с Вами мы попытаемся создать универсальную обработку для ускоренного перевода запросов в формате 1С, в формат T-SQL, а так же поговорим об оптимизации запросов. В конечном итоге мы увидем что при выполнении определенных задач, прямые запросы позволяют сократить общее время операций с трех часов работы, до трех минут.

Автор: ixilimuse

Источник