Для чего в 1С: Предприятие 7.7 используют прямые запросы

Идеальным, с точки зрения 1С: Предприятие 7.7, является такой запрос — который построен на одной [виртуальной] таблице, без соединений, с отбором по индексированному полю и минимальным набором реквизитов в результирующей таблице. Такой запрос будет достаточно быстро выполнен платформой и поэтому, в качестве методических рекомендаций, фирма 1С предлагает, в частности, под каждый аналитический отчет, создавать отдельный регистр оперативного учета. Среди прочих рекомендаций, предлагается не делать вызов запроса в цикле и не выгружать результаты запроса в таблицу значений, для дальнейшей работы с данными.

В этой статье, я приведу пример расчетной задачи, для решения которой, придется нарушить, как минимум, две из вышеуказанных рекомендаций. По-другому, эту задачу не решить, просто. Потом приведу пример решения этой задачи через прямые запросы, с вызовом vfpoledb-драйвера dbf-баз данных от Microsoft, используя навигационный подход работы с таблицами базы данных.

Постановка задачи:

  1. Фирма реализует товар оптовым покупателям;
  2. По реализации, клиентам предоставляется отсрочка платежа, некоторое количество дней, указано в карточке клиента;
  3. Для отдельных клиентов, отсрочка платежа рассчитывается по календарю задолженности, также указанному в карточке клиента;
  4. Требуется найти всех клиентов, имеющих просроченную задолженность.

Перед тем, как решать эту задачу, хотел бы отметить некоторые ее особенности. Во-первых, период отсрочки платежа, на всей совокупности клиентов — разный, а, следовательно, коль скоро период отбора движений является параметром виртуальной таблицы итогов регистра, нам потребуется столько раз выполнить запрос, сколько у нас будет разных периодов отсрочки платежа, с учетом корректировки сроков по календарям. Во-вторых, этих календарей может быть несколько, соответственно, множество клиентов по периодам «дробится» еще больше. И, наконец, следует заметить, что расчитанный ранее результат устаревает не только с вводом новых документов движения взаиморасчетов, а просто, с течением времени, что не позволяет использовать таблицы остатков, для извлечения этих данных.

//*******************************************
Функция ОтборКлиентовСДолгамиНаТекущуюДату(тзКлиенты)
// стандартный объект 'запрос'
Запрос = СоздатьОбъект("Запрос");
// текст запроса на выборку клиентов, имеющих актуальный долг
ТекстЗапроса = "//{{ЗАПРОС(Сформировать)
|Без итогов;
|Клиент = Регистр.ВзаиморасчетыПокупателей.Клиент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг;
|Функция ДолгКонОст = КонОст(Долг);
|Группировка Клиент без упорядочивания без групп;
|"//}}ЗАПРОС
;
// замер времени выполнения запроса
время = _getPerformanceCounter();
// процедура выполнения запроса
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
Возврат 0;
КонецЕсли;
// вывод времени, потраченного системой на исполнения запроса
Сообщить("Время запроса: "
+ (_getPerformanceCounter() - время));
// замер времени на выгрузку результатов
время = _getPerformanceCounter();
Запрос.Выгрузить(тзКлиенты);
// вывод времени, потраченного на выгрузку результатов
Сообщить("Время выгрузки результатов:"
+ (_getPerformanceCounter() - время));
// вывод количества результирующих строк запроса
Сообщить("Строк запроса: " + тзКлиенты.КоличествоСтрок());
Возврат 1;
КонецФункции
//*******************************************
Процедура ВычислитьДатуНачалаОтсрочкиДолга(тзКлиенты)
// новый реквизит таблицы для хранения даты начала периода отсрочки
тзКлиенты.НоваяКолонка("ДатаНачала");
тзКлиенты.ВыбратьСтроки();
Пока тзКлиенты.ПолучитьСтроку() = 1 Цикл
Календарь = тзКлиенты.Клиент.КалендарьЗадолженности;
Если Календарь.Выбран() = 1 Тогда
// расчет даты начала периода отсрочки по календарю задолженности
тзКлиенты.ДатаНачала = Календарь.ПолучитьДату(ТекущаяДата(),
- тзКлиенты.Клиент.Глубина.Получить(ТекущаяДата()) - 1);
Иначе
// расчет даты начала периода отсрочки от текущей даты
тзКлиенты.ДатаНачала = ТекущаяДата()
- тзКлиенты.Клиент.Глубина.Получить(ТекущаяДата()) - 1;
КонецЕсли;
КонецЦикла;
КонецПроцедуры
//*******************************************
Функция ВыполнитьЗапросПоПросрДолгу(тзКлиенты, ДатаНачала, сзКлиенты)
Если сзКлиенты.РазмерСписка() = 0 Тогда
// передано пустое подмножество таблицы клиентов
Возврат 1;
КонецЕсли;
// стандартный объект 'запрос'
Запрос = СоздатьОбъект("Запрос");
// текст запроса на вычисление накопленного за период отсрочки долга
ТекстЗапроса = "//{{ЗАПРОС(Сформировать2)
|Период с ДатаНачала;
|Без итогов;
|Клиент = Регистр.ВзаиморасчетыПокупателей.Клиент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг;
|Функция ДолгПриход = Приход(Долг);
|Группировка Клиент без упорядочивания без групп;// все;
|Условие(Клиент в сзКлиенты);
|"//}}ЗАПРОС
;
// замер времени выполнения запроса
время = _getPerformanceCounter();
// процедура выполнения запроса
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
Возврат 0;
КонецЕсли;
// вывод времени, потраченного системой на исполнения запроса
Сообщить("Время запроса: "
+ (_getPerformanceCounter() - время));
Пока Запрос.Группировка() = 1 Цикл
НС = 0;
Если тзКлиенты.НайтиЗначение(Запрос.Клиент, НС, "Клиент") = 1 Тогда
// для каждого из результатов запроса корректируем строку в таблице клиентов
тзКлиенты.УстановитьЗначение(НС, "ПросрДолг",
Макс(0, тзКлиенты.ПолучитьЗначение(НС, "ДолгКонОст")
- Запрос.ДолгПриход));
КонецЕсли;
КонецЦикла;
Возврат 1;
КонецФункции
//*******************************************
Функция ВычислитьПросроченныйДолг(тзКлиенты)
Перем ДатаНачала;
// текущая "порция" клиентов для запроса по просроченному долгу
сзКлиенты = СоздатьОбъект("СписокЗначений");
тзКлиенты.НоваяКолонка("ПросрДолг");
//
тзКлиенты.ВыбратьСтроки();
Пока тзКлиенты.ПолучитьСтроку() = 1 Цикл
тзКлиенты.ПросрДолг = Макс(0, тзКлиенты.ДолгКонОст);
КонецЦикла;
//
тзКлиенты.Сортировать("ДатаНачала");
НС = 1;
Пока 1=1 Цикл
Если НС > тзКлиенты.КоличествоСтрок() Тогда
// запуск процедуры расчета просроченного долга для последней порции клиентов
Если ВыполнитьЗапросПоПросрДолгу(тзКлиенты,
ДатаНачала, сзКлиенты) = 0 Тогда
Возврат 0;
КонецЕсли;
// выход из цикла и процедуры
Возврат 1;
Иначе
тзКлиенты.ПолучитьСтрокуПоНомеру(НС);
Если ДатаНачала <> тзКлиенты.ДатаНачала Тогда
// нашли начало новой порции данных
Если ПустоеЗначение(ДатаНачала) = 1 Тогда
// это - первая порция данных
Иначе
// данные для расчетов по предыдущей порции - собраны
Если ВыполнитьЗапросПоПросрДолгу(тзКлиенты,
ДатаНачала, сзКлиенты) = 0 Тогда
Возврат 0;
КонецЕсли;
сзКлиенты.УдалитьВсе();
КонецЕсли;
ДатаНачала = тзКлиенты.ДатаНачала;
Иначе
// продолжаем собирать данные...
КонецЕсли;
сзКлиенты.ДобавитьЗначение(тзКлиенты.Клиент);
КонецЕсли;
// итератор по строкам таблицы
НС = НС + 1;
КонецЦикла;
КонецФункции
//*******************************************
Процедура Сформировать()
Перем тзКлиенты;
// замер времени, необходимого для вычислений
время = _getPerformanceCounter();
// выборка клиентов, имеющих долг на текущую дату
Если ОтборКлиентовСДолгамиНаТекущуюДату(тзКлиенты) = 1 Тогда
ВычислитьДатуНачалаОтсрочкиДолга(тзКлиенты);
Если ВычислитьПросроченныйДолг(тзКлиенты) = 0 Тогда
Сообщить("Не удалось сформировать отчет", "!");
КонецЕсли;
КонецЕсли;
// оценка временных затрат
Сообщить("Всего затрачено времени: "
+ (_getPerformanceCounter() - время));
тзКлиенты.Сортировать("Клиент");
тзКлиенты.ВыбратьСтроку();
КонецПроцедуры

Согласно статистике выполнения данного запроса на рабочей базе, при порядке 2-3 тыс. активных клиентов, данный расчет выполняется в течении 9-10 секунд. При этом, максимальная продолжительность выполнения единичного запроса расчета просроченной задолженности по группе клиентов с одинаковым кредитным периодом, примерно равна 4-5 секунд.

глмOLEDBQuery.ВыполнитьИнструкцию("_test(CTOD('11/14/2019'),CTOD('12/26/2019'))", "", 1, 0, 0, 0);
тзРезультат = глмOLEDBQuery.ВыполнитьИнструкцию("SELECT * FROM credits", "", 1, 0, 0, 0);
&& параметры процедуры:
&& - текущая (системная) дата
&& - дата среза таблицы актуальных итогов
PARAMETER dCurrentDate, dSliceDate
&& результирующая таблица сведений по долгам и просроченным долгам покупателей
CREATE TABLE credits(customer CHAR(9), debt NUMERIC(15,2), overdue_d NUMERIC(15,2))
&& временная таблица с расчетами параметров кредитного периода
CREATE TABLE calendar_c(ID CHAR(4), grace_p NUMERIC(3,0), start_d DATETIME)
INDEX ON ID TAG ID OF calendar_c
&& таблица итогов остатков взаиморасчетов
USE rg169 AGAIN ALIAS totals IN 0 ORDER TAG prop DESC SHARED
&& таблица констант для вычисления текущего значения периода отсрочки
USE 1sconst AGAIN ALIAS constants IN 0 ORDER TAG idd DESC SHARED
&& таблица клиентов для поиска сведений о календаре исчисления кредитного периода
USE sc46 AGAIN ALIAS customers IN 0 ORDER TAG idd SHARED
&& открытие таблицы календарей для вычисления граниз периода кредита
USE cl AGAIN ALIAS cl IN 0 ORDER TAG 'id+date' DESC SHARED
&& открытие таблицы движений регистра взаиморасчетов
USE ra169 AGAIN ALIAS entries IN 0 ORDER TAG via170 DESC SHARED
&& анализ среза таблицы итогов
SELECT totals
IF SEEK(DTOS(dSliceDate), 'totals', 'prop')
SCAN WHILE totals.period = dSliceDate
IF totals.sp171 > 0
&& рассматриваем только дебиторскую задолженность
cCurrentCustomer = totals.sp170
nCurrentDebt = totals.sp171
&& отсрочку платежа еще требуется вычислить
nCurrentGracePeriod = 0
SELECT constants
IF SEEK(' P3' + cCurrentCustomer, 'constants', 'idd')
SCAN WHILE constants.ID + constants.Objid = '' + cCurrentCustomer
&& перебор периодических значений реквизита в обратном хронологическом порядке
nCurrentGracePeriod = VAL(constants.Value)
EXIT
ENDSCAN
ENDIF
&& расчет параметров кредитного периода
dGracePeriodStartDate = dCurrentDate - nCurrentGracePeriod
SELECT customers
IF SEEK(cCurrentCustomer, 'customers', 'idd')
SCAN WHILE customers.ID = cCurrentCustomer
&& определение заданного в карточке клиента календаря
cCurrentCalendar = SUBSTR(customers.sp6588, 7, 4)
EXIT
ENDSCAN
ENDIF
IF cCurrentCalendar <> ' 0'
&& задан непустой календарь
SELECT calendar_c
IF SEEK(cCurrentCalendar, 'calendar_c', 'id')
SCAN WHILE calendar_c.ID = cCurrentCalendar
IF calendar_c.grace_p = nCurrentGracePeriod
&& нашли ранее рассчитанный результат по выбранному календарю
dGracePeriodStartDate = calendar_c.start_d
EXIT
ENDIF
ENDSCAN
ELSE
SELECT cl
&& общее количество рабочих дней
nWorkingDays = 0
IF SEEK(cCurrentCalendar, 'cl', 'id+date')
SCAN WHILE cl.ID = cCurrentCalendar
IF cl.daylen > 0
nWorkingDays = nWorkingDays + 1
ENDIF
IF nCurrentGracePeriod = nWorkingDays
dGracePeriodStartDate = cl.date
EXIT
ENDIF
&& если расчет дат по календарю по разным причинам не будет закончен - используется разность дат
ENDSCAN
&& сохранить результат расчетов в таблицу
INSERT INTO calendar_c(ID, grace_p, start_d) _
VALUES(cCurrentCalendar, nCurrentGracePeriod, dGracePeriodStartDate)
ENDIF
ENDIF
ENDIF
&& расчет прихода долга за кредитный период
nReceiptDebt = 0
SELECT entries
IF SEEK(cCurrentCustomer, 'entries', 'via170')
SCAN WHILE entries.sp170 = cCurrentCustomer _
AND entries.date >= dGracePeriodStartDate
IF entries.sp171 > 0
nReceiptDebt = nReceiptDebt + entries.sp171
ENDIF
&& дальнейший расчет можно не продолжать, просроченного долга нет
IF nReceiptDebt >= nCurrentDebt
EXIT
ENDIF
ENDSCAN
ENDIF
&& расчет просроченной задолженности
IF nCurrentDebt > nReceiptDebt
nOverdueDebt = nCurrentDebt - nReceiptDebt
ELSE
nOverdueDebt = 0
ENDIF
&& запись результатов расчета в таблицу
INSERT INTO credits(customer, debt, overdue_d) _
VALUES(cCurrentCustomer, nCurrentDebt, nOverdueDebt)
ENDIF
ENDSCAN
ELSE
&& не нашли таблицу итогов регистра, соответствующую выбранной дате
RETURN 0
ENDIF
&& процедура расчета завершена успешно
RETURN 1

Скорость выполнения этого запроса на том же массиве данных уже существенно выше. Он выполняется, примерно, за 950 миллисекунд, то есть, мы имеем десятикратное увеличение производительности запроса. Оно происходит, во-первых, от того, что навигационный метод с dbf-таблицами, сам по себе, работает существенно быстрее. Во-вторых, все вычисления у нас укладываются в один запрос. В-третьих, используя навигационный метод, мы можем заранее пресечь дальнейшее выполнение расчетов по определенной аналитике, когда «видим» явные признаки того, что в них нет необходимости. Как это было, когда мы прерывали цикл расчета накопления задолженности, по достижению суммы текущего долга.

В заключение, хочется отметить, что выполнение прямых запросов посредством vfpoledb-драйвера от Microsoft — не единственное решение. Также можно применять vfpodbc-драйвер, от той-же Microsoft или 1sqlite-драйвер от разработчиков внешних компонент для 1С: Предприятие 7.7. В своем решении, следует выбирать более подходящий вариант, в зависимости от текущей задачи, формата базы данных, текущего системного окружения и других, существенных по мнению разработчика, моментов.

Вам понравиться

Добавить комментарий