Один из наиболее часто встречающихся видов запросов 1С - это запрос балансового типа, в котором в разрезе необходимых нам измерений за выбранный период получаются начальные остатки, приход, расход и конечные остатки. Часто возникает необходимость ускорить данный запрос с помощью перевода его в прямой запрос к SQL -серверу. Но при рассмотрении выясняется, что, фактически мы имеем дело не с одним запросом, а с четырьмя, результаты которых должны быть объединены в один. Можно по-разному подходить к этой задаче – сводить данные в таблице значений, использовать временные таблицы SQL -сервера и т.д.
Здесь я опишу наиболее легкий вариант построения такого запроса, который не требует ни временных таблиц, ни сведения в один на «клиенте», ни каких бы то ни было дополнительных действий с базой данных. Все очень просто.
Итак. Представим такой простой запрос 1С :
Запрос =СоздатьОбъект("Запрос" );
ТекстЗапроса ="//{{ЗАПРОС(ОдинС)
|Период с НачДата по КонДата;
|Товар = Регистр.Партии.Товар;
|Количество = Регистр.Партии.Количество;
|Функция Нач = НачОст(Количество);
|Функция Прих = Приход(Количество);
|Функция Расх = Расход(Количество);
|Функция Кон = КонОст(Количество);
|Группировка Товар без групп;
|" //}}ЗАПРОС ;
Как мы видим, имеет место обращение к регистру «Партии» с учетом выбранного в диалоге периода, одна группировка по измерению «Товар », 4 численных колонки (начальный остаток, приход, расход, конечный остаток). Никаких условий для простоты демонстрации. Как видно, в запросе будут участвовать три таблицы: таблицы остатков и движений регистра «Партии » и таблица «журнал документов ». В тестовом запросе будет анализироваться период с 05.02.2002 года, по 20.01.2004 для усложнения задачи.
Казалось бы ничего сложного – мы строим 4 запроса в каждом из которых имеется столбец товара и числовой столбец с соответствующим результатом суммирования (начальный остаток, приход, расход или конечный остаток) и соединяем их в одну таблицу с помощью «UNION ALL ». Но тут нас поджидает ловушка – в результирующей таблице будет всего 2 столбца: столбец товара и столбец данных, и это все несмотря на то, что мы по-разному называли наши столбцы данных в каждом из запросов.
Все очень просто – для правильного объединения SQL -сервер требует одинаковое количество столбцов в каждой колонке в каждой из объединяемых таблиц. И мы дадим ему это. Пусть столбец с товаром называется «Товар », а столбцы с данными будут называться соответственно «Нач », «Прих », «Расх », «Кон ». Тогда нам нужно, чтобы в каждом запросе были все четыре столбца с такими наименованиями. Лишь один из них в каждом запросе будет содержать данные, остальные будут содержать 0 , причем мы сами будем декларировать эти столбцы, как содержащие нулевое значение. Тогда объединение таблиц у нас пройдет на ура. Кроме того, раз мы имеем три значения балансовой формулы (начальный остаток, приход и расход), то конечный остаток нам высчитывать уже не нужно – мы возьмем его из уже получившейся таблицы!
Ниже приведен текст запроса в варианте написания для Rainbow , 1C++ или Neta.dll . Изначально он содержал в себе четыре запроса - 2 для остатков и по одному для прихода и расхода, но путем объединения запросов по приходу и расходу в один мы получим снижение времени выполнения запроса на 40% . Смотрите новый текст запроса:
SELECT Товар, SUM(Нач) AS Нач, SUM(Прих) AS Прих, SUM(Расх) AS Расх, SUM(Нач) + SUM(Прих) - SUM(Расх) AS Кон FROM (SELECT SP259 AS Товар, SP263 AS Нач, 0 AS Прих, 0 AS Расх FROM RG258 WHERE PERIOD = CONVERT(DATETIME, '2002-01-01 00:00:00', 102)
UNION ALL
SELECT RA258.SP259 AS Товар, RA258.SP263 * (1 - RA258.DEBKRED * 2) AS Нач, 0 AS Прих, 0 AS Расх FROM RA258 INNER JOIN _1SJOURN ON RA258.IDDOC = _1SJOURN.IDDOC WHERE (_1SJOURN.DATE_TIME_IDDOC >= '20020201') AND (_1SJOURN.DATE_TIME_IDDOC < '20020205') AND (_1SJOURN.RF258 = 1) AND (_1SJOURN.CLOSED & 1 = 1)
UNION ALL
SELECT RA258.SP259 AS Товар, 0 AS Нач, CASE WHEN RA258.DEBKRED = 0 THEN RA258.SP263 ELSE 0 END AS Прих, CASE WHEN RA258.DEBKRED = 1 THEN RA258.SP263 ELSE 0 END AS Расх FROM RA258 INNER JOIN _1SJOURN ON RA258.IDDOC = _1SJOURN.IDDOC WHERE (_1SJOURN.DATE_TIME_IDDOC >= '20020205') AND (_1SJOURN.DATE_TIME_IDDOC < '20040121') AND (_1SJOURN.RF258 = 1) AND (_1SJOURN.CLOSED & 1 = 1)) TMP GROUP BY Товар
А это первая строчка запроса для варианта написания ADO (она единственная отличается):
SELECT Товар, CONVERT(money, SUM(Нач)) AS Нач, CONVERT(money, SUM(Прих)) AS Прих, CONVERT(money, SUM(Расх)) AS Расх, CONVERT(money, SUM(Нач) + SUM(Прих) - SUM(Расх)) AS Кон
На основе приведенной конструкции можно создавать и более сложные запросы с условиями, группировками и т.д.
Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").
|