Запросы. Временные Таблицы. Сравнение методов создания ВТ

09.02.18

Разработка - Запросы

Любой начинающий программист 1с8 познает азы искусства языка 1С из книги "1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева http://v8.1c.ru/metod/books/book.jsp?id=441 В данном учебнике на стр.429 подробно рассмотрен пример создания сложного запроса с использованием Временных Таблиц. Сразу скажу, что как для новичка, - пример достаточно сложный и не совсем понятный к самостоятельному повторению в своих разработках. В данной статье мы подробно разберем этот пример сложного запроса Радченко/Хрусталевой и выполним ту же поставленную задачу, но гораздо проще и нагляднее с помощью моей простенькой консоли запросов https://infostart.ru/public/719293/ Я еще пару месяцев назад билась над алгоритмом создания Временных Таблиц в сложном запросе, поэтому нам пригодится и эта ссылка https://forum.infostart.ru/forum9/topic183700/

Скачать файлы

Наименование Файл Версия Размер
Запросы.Временные Таблицы. Радченко/Хрусталева.Сравнение методов создания ВТ.:
.dt 191,44Kb
7
.dt 191,44Kb 7 Скачать

Итак, приступим. Где обычно нам требуются сложные запросы?

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

В указанном примере из книги Радченко/Хрусталева на стр 429 мы должны в процедуре проведения документа "Оказание услуг" найти среднюю стоимость списываемой документом номенклатуры. Для этого мы используем данные из двух регистров остатков:СтоимостьМатериалов и СтоимостьМатериаловОстатки.

Из регистра остатков материала - мы берем общее количество данного списываемого товара, а из регистра стоимость материалов - мы берем стоимость этого товара (стоимость товара в регистр записывается при проведении Приходной накладной).

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

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

Отсюда, главная наша задача: чтобы документ проводился быстро! Для этого в самом начале, при вызове регистров, нам нужно ограничить полученные данные из этих регистров перечнем нашего товара. Согласитесь, что если в документе мы проводим только две позиции товара, то зачем нам нужно перебирать оба регистра по всем его многотысячным записям?

И вот для решения задачи оптимизации проведения документа - нам и требуется создать некий фильтр из списка товара по накладной. Для этого мы создаем таблицу, выгружаем в нее номенклатуру из текущего документа. Далее к этой таблице в запросе Левым соединением прикрепляем наши регистры, после чего в итоговую выборку запроса попадают только данные, отфильтрованные перечнем товара из накладной! Ничего лишнего, все работает быстро!

Мы подошли к самой сути - принципу создания этой таблицы, в которую мы будем выгружать товар по накладной.

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

Для работы с запросами есть объект МенеджерВременныхТаблиц, о нем можно прочитать в Синтаксис-помощнике:Работа с запросами=>МенеджерВременныхТаблиц (см.рис)

Что делает этот менеджер временных таблиц? Он предоставляет нам область, место, или лист бумаги - так проще, куда мы можем временно записать и так же временно хранить нашу табличку с данными по товару из документа.

По примеру из урока - мы обозначаем эту самую область, в которую далее создадим и загрузим данными нашу табличку:

//Создать менеджер временных таблиц
	  МенеджерВТ=Новый МенеджерВременныхТаблиц;
	
	Запрос = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос.Текст = 

 Далее по уроку - мы, используя меню под правой кнопкой мыши, - открываем КонструкторЗапроса:

Теперь здесь в запросе - мы полностью формируем со всеми условиями вид нашей временной таблицы, а так же заполняем ее сразу данными. В этом запросе мы выбираем табличную часть документа "ОказаниеУслуг", выбираем необходимые нам поля из этого документа:Номенклатуру, ВидНоменклатуры, Количество по документу и Сумму по документу.

Далее в этом запросе мы накладываем условия, что работаем только с текущим конкретным документом "ОказаниеУслуг", а не со всеми этими документами сразу (в условии запроса ставим параметр &Ссылка на конкретный документ)

В этом же запросе - мы добавляем группировку (и суммирование по группировке) по Номенклатуре и ее виду, т.к. в документе у нас может быть задвоенная позиция по одному Товару.

И вот мы добрались до вкладки "Дополнительно" в Конструкторе запросов, где непосредственно указываем, что мы создаем временную таблицу, и ее новое имя "НоменклатураДокумента":

Текст Запроса с созданной временной таблицей по уроку из книги Радченко/Хрусталева будет следующим:

//Создать менеджер временных таблиц
	  МенеджерВТ=Новый МенеджерВременныхТаблиц;
	
	Запрос = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос.Текст = 
		"ВЫБРАТЬ
		|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
		|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры КАК ВидНоменклатуры,
		|	СУММА(ОказаниеУслугиПереченьНоменклатуры.Количество) КАК КоличествоВДокументе,
		|	СУММА(ОказаниеУслугиПереченьНоменклатуры.Сумма) КАК СуммаВДокументе
		|ПОМЕСТИТЬ НоменклатураДокумента
		|ИЗ
		|	Документ.ОказаниеУслуги.ПереченьНоменклатуры КАК ОказаниеУслугиПереченьНоменклатуры
		|ГДЕ
		|	ОказаниеУслугиПереченьНоменклатуры.Ссылка = &Ссылка
		|
		|СГРУППИРОВАТЬ ПО
		|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
		|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры";
	
	Запрос.УстановитьПараметр("Ссылка", Ссылка);
	
	РезультатЗапроса = Запрос.Выполнить();

 

Вот таким довольно интересным способом по уроку уважаемых преподавателей Максима Григорьевича Радченко и Елены Юрьевны Хрусталевой мы создали временную таблицу, в которую поместили данные о номенклатуре из проводимого нами документа.

Далее по занятию - нам следует связать нашу созданную временную таблицу с двумя регистрами (Остатков материалов и Стоимостью номенклатуры).

Для этого мы создаем второй Запрос, к которому сразу привяжем наш менеджер Временных таблиц, в котором, собственно, и хранится наша таблица:

	Запрос2 = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос2.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос2.Текст =

Так же с помощью меню под правой кнопкой мыши - выберем пункт КонструкторЗапросов. Здесь во втором запросе - мы добавляем наши два регистра по Остаткам и Стоимости. А вот чтобы привязать их к созданной предыдущим запросом ВременнойТаблице, которая у нас хранится в МенеджереВременныхТаблиц, нам нужно нажать на кнопку в КонструктореЗапросов "Создать описание временной таблицы" - и полностью прописать название полей и их тип, которые есть в той Временной таблице, что мы создали первым запросом:

Данный второй запрос очень простой - мы просто создаем в КонструктореЗапросов на вкладке "Связи" - связь нашей временной таблицы с двумя регистрами ЛЕВЫМ СОЕДИНЕНИЕМ. Только в этом случае у нас справа будет наша ВременнаяТаблица, а слева от нее все равно ВЕСЬ набор данных регистров!!! Поэтому, если так и оставим, то оптимизации работы процедуры проведения документа - мы так и не добьемся!

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

Материал В
	(ВЫБРАТЬ
		НоменклатураДокумента.Номенклатура
	ИЗ
		НоменклатураДокумента)

***********Весь листинг с двумя запросами по учебнику выглядит следующим образом:

//Создать менеджер временных таблиц
	МенеджерВТ=Новый МенеджерВременныхТаблиц;
	
	Запрос = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос.Текст = 
	"ВЫБРАТЬ
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры КАК ВидНоменклатуры,
	|	СУММА(ОказаниеУслугиПереченьНоменклатуры.Количество) КАК КоличествоВДокументе,
	|	СУММА(ОказаниеУслугиПереченьНоменклатуры.Сумма) КАК СуммаВДокументе
	|ПОМЕСТИТЬ НоменклатураДокумента
	|ИЗ
	|	Документ.ОказаниеУслуги.ПереченьНоменклатуры КАК ОказаниеУслугиПереченьНоменклатуры
	|ГДЕ
	|	ОказаниеУслугиПереченьНоменклатуры.Ссылка = &Ссылка
	|
	|СГРУППИРОВАТЬ ПО
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры";
	
	Запрос.УстановитьПараметр("Ссылка", Ссылка);
	
	РезультатЗапроса = Запрос.Выполнить();
	
	Запрос2 = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос2.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос2.Текст = "ВЫБРАТЬ
	|	НоменклатураДокумента.Номенклатура,
	|	НоменклатураДокумента.ВидНоменклатуры,
	|	НоменклатураДокумента.КоличествоВДокументе,
	|	НоменклатураДокумента.СуммаВДокументе,
	|	ЕСТЬNULL(СтоимостьМатериаловОстатки.СтоимостьОстаток, 0) КАК Стоимость,
	|	ЕСТЬNULL(ОстаткиМатериаловОстатки.КоличествоОстаток, 0) КАК Количество
	|ИЗ
	|	НоменклатураДокумента КАК НоменклатураДокумента
	|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
	|				,
	|				Материал В
	|					(ВЫБРАТЬ
	|						НоменклатураДокумента.Номенклатура
	|					ИЗ
	|						НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки
	|		ПО НоменклатураДокумента.Номенклатура = СтоимостьМатериаловОстатки.Материал
	|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиМатериалов.Остатки(
	|				,
	|				Материал В
	|					(ВЫБРАТЬ
	|						НоменклатураДокумента.Номенклатура
	|					ИЗ
	|						НоменклатураДокумента)) КАК ОстаткиМатериаловОстатки
	|		ПО НоменклатураДокумента.Номенклатура = ОстаткиМатериаловОстатки.Материал";

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

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Сделаем первоначальные выводы - каким образом мы создавали Временную таблицу и связывали ее с регистрами:

1.Явно прописали МенеджерВременныхТаблиц

2.Отдельным первым запросом создали Временную таблицу и заполнили ее данными

3.Переподключили МенеджерВременныхТаблиц на второй запрос

4.Во втором запросе пришлось:

1) в ручную прописывать структуру нашей ВременнойТаблицы с обязательным указанием Типов полей;

2) создать связи между временной таблицей и двумя регистрами;

3) на каждый из регистров наложить предварительно фильтр из данных временной таблицы;

4) накладываемый фильтр на регистры - нам пришлось писать в ручную

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

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

У нас есть очень хороший инструмент для создания запросов - КонсольЗапросов. Я пользуюсь своей //infostart.ru/public/719293/  Вы можете использовать любую КонсольЗапросов, которая Вам нравится.

Итак, откроем 1с в пользовательском режиме, вызовем КонсольЗапросов. И начнем сразу же с создания так необходимой нам временной таблицы (создайте поля и тип данных точно такой, как указан в Конфигураторе в документе "ОказанияУслуг" в его табличной части!):

Здесь нет ничего сложного, кроме одной очень важной ремарки - перед наименованием нашей таблички мы ставим амперсант!

&ВнешниеДанные

С помощью амперсанта - мы конкретно указываем, что создаем не просто табличку, а Параметр, который необходим к заполнению при отработке нашего запроса.

Далее идем в КонструктореЗапроса на вкладку "Дополнительно" - и здесь явно указываем что наш созданный набор данных - это ВременнаяТаблица! Назовем эту самую временную таблицу точно так же, как и в уроке по книжке: "НоменклатураДокумента".

Все, временная таблица есть, не нужно более над нею думать)). Но чтобы все заработало, причем в ОДНОМ запросе, нам нужно создать пакет запросов. Для этого в КонструктореЗапросов переходим на вкладку "ПакетЗапросов", нажимаем Добавить, и уже в полученном втором пакете запросов мы имеем возможность видеть,выбирать и работать с только что созданной нами временной таблицей!!!

Поэтому добавим в данный второй пакет запроса нашу ВременнуюТаблицу и нужных два регистра. Установим точно так же связи ВременнойТаблицы Левым Сооединением с регистрами.

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

Здесь перетягиваем в расчетное окно реквизит Материал регистра, пишем одну единственную буковку В, ставим скобки и внутри скобок правой клавишей мыши - вызываем еще один КонструкторЗапросов, в котором просто из нашей временной таблицы выбираем поле Номенклатура,все сохраняем и точно так же накладываем Параметр на второй регистр.

Второй КонструкторЗапросов создаст нам следующий код, который и будет условием/фильтром на регистре:

Материал В
ВЫБРАТЬ
	НоменклатураДокумента.Номенклатура
ИЗ
	НоменклатураДокумента КАК НоменклатураДокумента

Все, запрос полностью готов, сохраняем,закрываем КонструкторЗапросов. Можно, конечно еще на выбранные поля в регистров в КонструктореЗапросов поставить условие ЕстьNull. Можем так же во втором пакете сгрупировать поля по Номенклатуре и просуммировать все остальные поля.

***********Вот такой запрос в единственном экземпляре у нас получился более простым и наглядным методом:

ВЫБРАТЬ
	ВнешниеДанные.Номенклатура,
	ВнешниеДанные.Количество КАК КоличествоВДокументе,
	ВнешниеДанные.Сумма КАК СуммаВДокументе
ПОМЕСТИТЬ НоменклатураДокумента
ИЗ
	&ВнешниеДанные КАК ВнешниеДанные
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	НоменклатураДокумента.Номенклатура,
	СУММА(НоменклатураДокумента.КоличествоВДокументе) КАК КоличествоВДокументе,
	СУММА(НоменклатураДокумента.СуммаВДокументе) КАК СуммаВДокументе,
	СУММА(ЕСТЬNULL(ОстаткиМатериаловОстатки.КоличествоОстаток, 0)) КАК Количество,
	СУММА(ЕСТЬNULL(СтоимостьМатериаловОстатки.СтоимостьОстаток, 0)) КАК Стоимость
ИЗ
	НоменклатураДокумента КАК НоменклатураДокумента
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
				,
				Материал В
					(ВЫБРАТЬ
						НоменклатураДокумента.Номенклатура
					ИЗ
						НоменклатураДокумента КАК НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки
		ПО НоменклатураДокумента.Номенклатура = СтоимостьМатериаловОстатки.Материал
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиМатериалов.Остатки(
				,
				Материал В
					(ВЫБРАТЬ
						НоменклатураДокумента.Номенклатура
					ИЗ
						НоменклатураДокумента КАК НоменклатураДокумента)) КАК ОстаткиМатериаловОстатки
		ПО НоменклатураДокумента.Номенклатура = ОстаткиМатериаловОстатки.Материал

СГРУППИРОВАТЬ ПО
	НоменклатураДокумента.Номенклатура

Только в данном варианте при создании сложного запроса с ВременнымиТаблицами в КонсолиЗапросов - мы имеем преимущество - это наглядно запустить наш запрос и увидеть сразу же результат его работы!!!!!!

Для этого нажмите все соответствующие кнопки в КонсолиЗапросов (я рассказывала как ею пользоваться с вариантом  ВременныхТаблиц вот здесь //infostart.ru/public/719293/): НайтиПараметры, потом в поле "ТаблицаЗначений",на вкладке "ВремТаблицы" - заполните экспериментальные данные товара. И вот результат - все считает, все видно, все наглядно:

Осталось уже в модуле документа в процедуре Проведения - точно так же сделать обход цикла по результатам запроса и все готово. Да в данном варианте нужно будет перед запуском запроса - создать Таблицу значений и выгрузить в нее колонки табличной части документа.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Сделаем выводы по поводу второго метода создания временных таблиц в запросе, которым пользуюсь я:

1.Один единственный запрос с двумя пакетами, создается не в ручную, а в КонсолиЗапросов.

В первом пакете запросов - создали временную таблицу, которая наглядна видна при работе со вторым пакетом запросов!

2.Во втором пакете запросе пришлось:

1) Просто перетянули мышкой готовую ВременнуюТаблицу

2) создать связи между временной таблицей и двумя регистрами;

3) на каждый из регистров наложить предварительно фильтр из данных временной таблицы;

4) накладываемый фильтр на регистры - мы создавали автоматически,вызвав еще один КонструкторЗапросов!

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Итого, вывод - в учебнике  "1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева показан очень интересный способ создания Временных таблиц для Сложных запросов, но все-таки на практике проще всего работать с тем, что можно сразу проверить,пощупать,отредактировать....

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

В загрузке прилагается учебная база данных (создавала с нуля на Платформе 1с8.3)по урокам из книги  ("1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева) + описанный здесь мой вариант создания Временных таблиц (в проц.Проведения док."ОказаниеУслуг"). На данный момент как раз до уровня середины книги, то есть по описанный здесь урок о Временных таблицах в запросе.

Напомню, что данный пример рассматривается в процедуре Проведения документа "ОказаниеУслуг". В модуле этой процедуры Вы найдете оба варианта, описанного здесь: Попробуйте в режиме Предприятия посмотреть как работает один вариант, потом его заремарьте и снимите ремарку со второго варианта - посмотрите как работает другой - абсолютно индентично!

 ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

сложный запрос с временными таблицами уроки по книге Радченко/Хрусталева 1с8.3 отчеты СКД регистры сведений остатков оборотов 1с8 Платформа программирование

См. также

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

13000 руб.

02.09.2020    119976    656    389    

701

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5622    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    1964    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6098    29    mkalimulin    23    

48

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1714    vandalsvq    7    

28

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5287    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15963    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Sapiens_bru 4 10.02.18 13:50 Сейчас в теме
Многие так яростно ратуют за указание параметров во всех виртуальных таблицах, забывая что в конечном итоге это будет транслировано в sql в те же join и where.
Что по вашему происходит когда выполняется выборка из регистра с указанным отбором-запросом?
СУБД находит строку в таблице, считывает обозначенные в условии поля и начинает сравнивать их со всеми полями из таблицы подзапроса-условия. Ровно тот же самый join.

Можно еще поспорить на тему отсутствия индексов во временной таблице, но тут только профайлер рассудит. Может у вас там всего 1 запись в документе и индекс будет тормозить, или записей там тыщи и отсутвтвие индекса роняет вам сервер.
А вот лишнее!! соединение точно тормозит запрос (одно в параметрах, второе после отбора из вирт таблицы)

И да. 1С даёт нам три инструмента отбора данных при выполнении запроса. Это параметры виртуальных таблиц, ГДЕ и ИМЕЮЩИЕ. А в sql всего два.

Не надо бездумно повторять за методистами 1С, они дают практику общих случаев, чтобы грубых ошибок избегали новички. Преподносить это как святую истину в любой ситуации не верно.
user1706724; t278; RickyTickyTok; ybatiaev; kazimesh; kassbar; Plotks2017; A_Max; boln; jan-pechka; +10 Ответить
2. jan-pechka 402 11.02.18 07:54 Сейчас в теме
(1)
1С даёт нам три инструмента отбора данных при выполнении запроса. Это параметры виртуальных таблиц, ГДЕ и ИМЕЮЩИЕ. А в sql всего два.


Объясните подробней,пожалуйста...

От условия ГДЕ или амперсанда & - не избавится ни при каком раскладе....В первом варианте с помощью ГДЕ - задаются параметры ссылки на данный документ, а во втором - с помощью & Параметром выступает вся временная таблица.

...а что за оператор ИМЕЮЩИЕ в 1с?

...каких два в sql ?

Если на сервере будет стоять одна конфа, в которой перебор изначальных данных ограничен списком самих данных, н-р, 5 товаров, а 150тысяч из базы; и другая конфа - в которой перебор данных идет по всем 150тысяч товаров из базы - то какая база быстрее всего "упадет" на сервере????
Причем в условиях и параметрах запросов обоих баз будет все одинаково (те же условия ГДЕ или амперсанд &) и соединения таблиц одинаковые - левые, разница только в одном - только в одной базе на огромной по весу таблице регистров будет стоять доп.фильтр из той же временной таблицы, а на второй - не поставлен такой фильтр.
3. Sapiens_bru 4 11.02.18 09:38 Сейчас в теме
(2) Про 1С оператор ИМЕЮЩИЕ и его sql аналог having вам расскажет гугл
Про 1С оператор ГДЕ и его sql аналог where вы думаете, вы знаете.
Других операторов отбора данных в sql нет. А в 1С , внезапно, есть параметры виртуальных таблиц. Которых в sql нет. Но, как мы знаем, сам 1С запросов к бд выполнять не умеет. Вместо этого запрос транслируется в запрос sql и выполняется субд. Значит 1С операторы параметров виртуальных таблиц будут транслированы в те же операторы sql

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

Запрос1
ВЫБРАТЬ Продажи.* ИЗ ПродажиОборот(,,Номенклаура = (ВЫБРАТЬ вт.Номенклатура ИЗ вт КАК вт)) как Продажи
Запрос2
ВЫБРАТЬ Продажи.* ИЗ ПродажиОборот ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт ПО ПродажиОборот.Номенклатура = втНоменклатура КАК Продажи

Это одинаковые для субд запросы. Причем во втором варианте СУБД автоматически индексирует "вт" по полям соединения, а в первом мы ОБЯЗАНЫ не забыть это делать сами (Хрусталёва забыла)

Поймите, что перебор всех 150тыс товаров произойдет в каждом из этих случаев. И делать двойной запрос к данным, сначала в параметрах, а потом еще и в соединении , это торможение запроса. Незначительное, но торможение. Им можно и пренебречь. Выберет запрос сначала 5 данных из 150тыс, потом еще раз 5 из 5ти. Никто не заметит.

Но громко в тексте утверждать, что соединения всегда хуже чем запросы в параметрах вирт таблиц, это заблуждение, хоть и не ведущее к плачевным последствиям. Максимум замедление работы вдвое. Не надо такое безапелляционно утверждать на публике
ybatiaev; kazimesh; +2 Ответить
4. jan-pechka 402 11.02.18 14:49 Сейчас в теме
(3)
утверждать что соединения всегда хуже чем запросы в параметрах вирт таблиц, это заблуждение


Что-то мы недопонимаем друг друга...
Давайте разложим оба метода по полочкам:

*.Метод создания ВТ Хрусталевой:

1.Создается в памяти место с помощью МенеджераВременныхТаблиц.
2.Задается первый запрос, который создает временную таблицу. Эта ВТ состоит ТОЛЬКО из данных текущего документа,н-р,5шт товаров.
	МенеджерВТ=Новый МенеджерВременныхТаблиц;
	
	Запрос = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос.Текст = 
	"ВЫБРАТЬ
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры КАК ВидНоменклатуры,
	|	СУММА(ОказаниеУслугиПереченьНоменклатуры.Количество) КАК КоличествоВДокументе,
	|	СУММА(ОказаниеУслугиПереченьНоменклатуры.Сумма) КАК СуммаВДокументе
	|ПОМЕСТИТЬ НоменклатураДокумента
	|ИЗ
	|	Документ.ОказаниеУслуги.ПереченьНоменклатуры КАК ОказаниеУслугиПереченьНоменклатуры
	|ГДЕ
	|	ОказаниеУслугиПереченьНоменклатуры.Ссылка = &Ссылка
	|
	|СГРУППИРОВАТЬ ПО
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
	|	ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры";
	
	Запрос.УстановитьПараметр("Ссылка", Ссылка);
	
	РезультатЗапроса = Запрос.Выполнить();
Показать

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

п.с. Смотрите, справа ВТ из 5шт. товаров, а слева две таблицы регистров из 150тыс.товаров. Этот, именно этот процесс обработки займет у системы 1с очень много времени.

4.Уважаемый педагог Е.Хрусталева предлагает сократить данные трудозатраты - и еще на этапе формирования второго запроса, в параметры каждой энергоемкой таблицы регистра - накладывает фильтр, который и есть, собственно, созданная ранее ВТ.

	|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
	|				,
	|				Материал В
	|					(ВЫБРАТЬ
	|						НоменклатураДокумента.Номенклатура
	|					ИЗ
	|						НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки


п.с.это очень верное решение, просто способ, которым она пользуется - это в ручную пишит условие данного фильтра.

Поэтому на выходе, т.е. в функции выполнения самого запроса (РезультатЗапроса = Запрос.Выполнить();) - работают только очень коротко сжатые таблицы данных, н-р по 5шт товаров.
А Именно - при соединении ВТ Левым соединением с отработанными таблицами регистров, в в каждой выборке перебор идет между, н-р, 5-ю товарами. Что приводит к оптимизации запроса.

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

Второй вариант создания ВТ, который я здесь показала - отличается только МЕТОДОМ именно создания ВТ, весь смысл фильтрации самих таблиц регистров - у меня так же остается!

	|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
	|				,
	|				Материал В
	|					(ВЫБРАТЬ
	|						НоменклатураДокумента.Номенклатура
	|					ИЗ
	|						НоменклатураДокумента КАК НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки


просто во втором варианте мы этот фильтр можем наложить,вызвав дополнительный КонструкторЗапросов.

Здесь в статье описаны методы создания временных таблиц, но фильтрация таблиц регистра на стадии формирования запроса присутствует и в первом и во втором методе. Так как без данного фильтра - все будет работать медленно.

Отсюда, мне не понятны Ваши возражения: (3)
утверждать что соединения всегда хуже чем запросы в параметрах вирт таблиц, это заблуждение
..............
5. Sapiens_bru 4 11.02.18 15:47 Сейчас в теме
(4) "Уважаемый педагог Е.Хрусталева предлагает сократить данные трудозатраты - и еще на этапе формирования второго запроса, в параметры каждой энергоемкой таблицы регистра - накладывает фильтр, который и есть, собственно, созданная ранее ВТ. "

Фильтра не существует в субд. Он есть только в 1С , как вымышленная сущность призванная избавить программистов 1С от грубых ошибок. Чтобы отфильтровать по желанию 1С записи из БД, реальный сервер sql выполнит ровно те же действия что и при соединении таблиц. Он переберёт все 150тыс записей регистра и сравнит их с 5ю записями документа. Ровно то же самое сделает он при простом соединении.

"причем она делает очень верно - сокращая таблицы регистров перед их Соединением с ВТ"
В том то и дело, что сокращения не происходит. Она дважды выполняет склеивание таблиц, тем самым увеличивая время запроса. В вырожденном случае когда в регистре ровно столько же записей сколько в документе (например сделали док поступления на 10 строк, потом делаем документ полной продажи на те же 10строк) , запрос Хрусталёвой будет выполнятся ровно вдвое ДОЛЬШЕ чем простое соединение виртуальной и временной таблиц, безо всяких предварительных условий
burvladimir; Merkalov; ybatiaev; kazimesh; +4 Ответить
6. Sapiens_bru 4 11.02.18 15:50 Сейчас в теме
И еще раз.
Не бывает фильтрации таблиц регистра. Есть только join и where. Так работает субд, согласны с этим методисты 1С или нет, неважно.
Кстати фирма 1С при сдаче официального экзамена "Эксперт по технологическим вопросам" просит опиратся на логику именно sql запросов, а не методически верных книг "1С для чайников"
kazimesh; jan-pechka; +2 Ответить
7. jan-pechka 402 11.02.18 17:26 Сейчас в теме
(6)
Не бывает фильтрации таблиц регистра


....если Вы так уверены в этом, значит Вы умеете пользоваться этими всеми серверными sql-штучками, типа ExpressProfiler22wAddinSigned и др., и тем более есть возможность на реальном серваке протестить оба варианта запроса и выложить результаты производительности....

п.с. иначе, просто все преподы....они что,сознательно изначально учат в принципе неверному подходу?????????? Это слишком серьезно....я просто заметила, что в книге - дан слишком непростой метод построения ВТ, можно намного проще, наглядней и доходчивей создавать ВТ....А Вы утверждаете, что все намного еще глубже не правильно...
8. Sapiens_bru 4 11.02.18 18:30 Сейчас в теме
(7)
Уверен. Есть возможность. Более того, тестировал лично месяц назад. Была задача по оптимизации запроса. Он выполнялся 12 секунд. Немного вроде, но очень уж часто пользователи к нему обращались.
Нашел ошибку предыдущего программиста - отсутствие индекса во временной таблице. Затем эта временная таблица идет как параметр-выборка к виртуальной и еще как левое соединение к этой же виртуальной. Примерно как вы делаете в исходной теме.
Поставил индекс к таблице - время запроса уменьшилось до 0.45с
Убрал вообще параметр из виртуальной таблицы - время запроса 0.3с
Все замеры выполнял минимум трижды, совпадали до сотых долей секунды.
sv_baranov; Merkalov; +2 Ответить
9. jan-pechka 402 11.02.18 19:53 Сейчас в теме
(8)
Примерно как вы делаете в исходной теме.
Поставил индекс к таблице - время запроса уменьшилось до 0.45с


Хорошо, что не Вы не против самой фильтрации огромных регистров на входе в запрос, только лишь за то,чтобы ставить индексы....
но фильтр ВТ уже идет по ссылке...чем это не индекс????....Предложите формулу фильтрации по индексу...
23. kazimesh 13.03.19 13:17 Сейчас в теме
(6) Это просто пять с плюсом! Распечатаю этот комментарий и повешу в своем отделе на самом видном месте )))
25. buganov 200 11.03.20 06:37 Сейчас в теме
А еще есть Seek predicates. И фильтры, если они наложены в четком порядке по индексу, как раз и попадут в секцию Seek. И при этом из большого регистра с диска поднимутся только то, что нужно без избыточных данных.
10. jan-pechka 402 12.02.18 08:09 Сейчас в теме
Итак, нашла я решения индексации полей Запросов - это функция "ИНДЕКСИРОВАТЬ ПО", но на том же сайте сказано, что если таблица запроса маленькая по объему и если далее соединение этой таблицы идет по ссылке (а именно так у меня), то индексирование - это лишнее!!! В этом случае индексирование ухудшит производительность!!!

п.с.чтобы быстро перейти на тот сайт - вот цитата с него:" На бескрайних просторах инета неоднократно встечался со статьей, описывающей причины неоптимальной работы запросов. В данной статье присутствует такая фраза: "В качестве индексных полей следует указать все поля, которые используются в условии соединения". Воспользовавшись данной рекомендацией и получил неожиданный результат: итоговое время выполнения пакета запросов после добавления индексов увеличилось. Где-то в 1,5 раза. Естественно, индексы пихал не во все подряд временные таблицы, а лишь в те, которые заведомо будут весьма большими. Хотелось бы узнать у гуру оптимизации - в каких случая всё-таки следует производить индексацию конкретного поля, и для каких полей индексация будет заведома бессысленна? В общих чертах механизм работы индексов представляю, однако тыканье носом в теорию поощряется."
20. caponid 14.02.18 17:52 Сейчас в теме
(10) sql для маленьких таблиц всегда игнорирует индексы. По моим исследованиям, индексы использовались после 5к строк - каким образом sql выбирает это количество, мне непонятно.
d4rkmesa; jan-pechka; +2 Ответить
11. Bazil 556 12.02.18 09:47 Сейчас в теме
А почему бы не показать еще третий вариант: запрос к табличной части документа и к остаткам в одном пакетном запросе? Мне кажется, это самый простой и наглядный вариант.
Strannik777; ybatiaev; Gendelf; pm74; Amorality93; A_Max; jan-pechka; +7 Ответить
12. jan-pechka 402 12.02.18 10:24 Сейчас в теме
(11)
А почему бы не показать еще третий вариант: запрос к табличной части документа и к остаткам в одном пакетном запросе?


да,это самый простой вариант:

 	      Запрос=Новый Запрос;
		  Запрос.Текст="ВЫБРАТЬ
		               |	ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
		               |	СУММА(ОказаниеУслугиПереченьНоменклатуры.Количество) КАК КоличествоВДокументе,
		               |	СУММА(ОказаниеУслугиПереченьНоменклатуры.Сумма) КАК СуммаВДокументе
		               |ПОМЕСТИТЬ НоменклатураДокумента
		               |ИЗ
		               |	Документ.ОказаниеУслуги.ПереченьНоменклатуры КАК ОказаниеУслугиПереченьНоменклатуры
		               |ГДЕ
		               |	ОказаниеУслугиПереченьНоменклатуры.Ссылка = &Ссылка
		               |
		               |СГРУППИРОВАТЬ ПО
		               |	ОказаниеУслугиПереченьНоменклатуры.Номенклатура
		               |;
		               |
		               |////////////////////////////////////////////////////////////­////////////////////
		               |ВЫБРАТЬ
		               |	НоменклатураДокумента.Номенклатура,
		               |	НоменклатураДокумента.КоличествоВДокументе,
		               |	НоменклатураДокумента.СуммаВДокументе,
		               |	ЕСТЬNULL(ОстаткиМатериаловОстатки.КоличествоОстаток, 0) КАК Количество,
		               |	ЕСТЬNULL(СтоимостьМатериаловОстатки.СтоимостьОстаток, 0) КАК Стоимость
		               |ИЗ
		               |	НоменклатураДокумента КАК НоменклатураДокумента
		               |		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиМатериалов.Остатки(
		               |				,
		               |				Материал В
		               |					(ВЫБРАТЬ
		               |						НоменклатураДокумента.Номенклатура
		               |					ИЗ
		               |						НоменклатураДокумента КАК НоменклатураДокумента)) КАК ОстаткиМатериаловОстатки
		               |		ПО НоменклатураДокумента.Номенклатура = ОстаткиМатериаловОстатки.Материал
		               |		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
		               |				,
		               |				Материал В
		               |					(ВЫБРАТЬ
		               |						НоменклатураДокумента.Номенклатура
		               |					ИЗ
		               |						НоменклатураДокумента КАК НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки
		               |		ПО НоменклатураДокумента.Номенклатура = СтоимостьМатериаловОстатки.Материал";
					   
Показать
13. user705698_bursev 12.02.18 15:42 Сейчас в теме
Объясните, пожалуйста, а в чем такая принципиальная разница в данном конкретном случае между временной таблицей и например вложенным запросом? Специально попробовал создать в консоли запрос, похожий на этот ( с созданием временной таблицы из табличной части документа и соединением ее с виртуальной таблицей), и на сопоставление похожий запрос, только вместо временной таблицы - вложенный запрос с той же самой табличной частью. И второй запрос даже быстрее отработал. В учебнике Хрусталевой смысл, насколько понимаю, в том, что таблица кочует из запроса в запрос, а тут же, так как используется всего лишь единожды, можно и вложенный запрос использовать.
14. jan-pechka 402 12.02.18 16:01 Сейчас в теме
(13)
В учебнике Хрусталевой смысл, насколько понимаю, в том, что таблица кочует из запроса в запрос


именно,правильно,в этом и смысл,т.к. далее этот препод использует сохраненную в МенеджереВременныхТаблиц - созданную ее Виртуальную таблицу для определения остатков, причем сразу делает заявление, что док нужно провести(!!!), чтобы увидеть эти остатки - так никто не делает по объяснимым причинам,может Вы объясните эти причины?

п.с. но мысль мне нравится:"таблица кочует из запроса в запрос", только использование этого метода кратко и пока не знаю как его применить...
15. user705698_bursev 12.02.18 16:11 Сейчас в теме
(14)
п.с. но мысль мне нравится:"таблица кочует из запроса в запрос", только использование этого метода кратко и пока не знаю как его применить...


В приведенном в учебнике примере бессмысленно, а на практике может, к примеру, понадобится обработка результата первого запроса до создания второго. А в том же втором понадобится временная таблица из первого, вот и вырисовывается какой-то смысл:)
jan-pechka; +1 Ответить
16. jan-pechka 402 12.02.18 17:03 Сейчас в теме
(15)
вот и вырисовывается какой-то смысл:)


да, но любой пример из практики - был бы кстати

п.с. например,я точно знаю как использовать свой второй пример из статьи....причем это будет очень востребованно, т.к. до сих пор ВСЕ менеджеры перебирают прайсы ручками....
17. PVG_73 17 14.02.18 12:41 Сейчас в теме
(13)
в чем такая принципиальная разница в данном конкретном случае между временной таблицей и например вложенным запросом?


Поправьте, если я не прав:
Если рассматривать клиент-серверный вариант, то вложенный запрос выполняется каждый раз если требуется получить данные верхние данные, а временная таблица выбирается один раз - помещается в tempdb и обращение к ней осуществляется по мере необходимости, т.е. база уже не дергается.
Если вложенный запрос выбирает небольшое количество данных, то разница в быстродействии может быть и не заметна, а вот если это достаточное количество данных, то получение его каждый раз может добавить хлопот. А так один раз получили и пользуемся.
Очень наглядно это видно если посмотреть профайлером на выполнение запроса с вложенным запросом и с временной таблицей.
sv_baranov; ybatiaev; jan-pechka; +3 Ответить
18. jan-pechka 402 14.02.18 12:48 Сейчас в теме
(17)
если посмотреть профайлером на выполнение запроса с вложенным запросом и с временной таблицей


так хочется научится пользоваться этим инструментом профайлером, но только один взгляд все что там нужно дополнительно установить и запустить - пугает...

п.с.Было бы очень здорово, если Вы попробуете объяснить "на пальцах" как правильно использовать программки для просмотра оптимизации запросов.Спасибо.
19. PVG_73 17 14.02.18 17:09 Сейчас в теме
(18) Увы уже очень давно не занимался оптимизацией, просто стараюсь поддерживать свои знания в этой области.
А из существующих основ для клиент-серверных вариантов только с MS SQL довелось видеть адекватные инструменты, с остальными только слышал, что что-то есть, но ни разу не пользовался.
А с профайлером на MS SQL - там главное правильно фильтр настроить, сейчас даже вроде есть более адаптивное описание как его настроить для работы в связке с 1С. Т.к. давно не занимался, то и ссылок увы не сохранилось.... :(
jan-pechka; +1 Ответить
21. Videon 07.12.18 11:35 Сейчас в теме
Автор не мог бы исправить слово "амперсант" на "амперсанд"? Глаз режет. И "пишит" на "пишет". ; (
WarAn; jan-pechka; +2 Ответить
22. jan-pechka 402 21.12.18 09:13 Сейчас в теме
(21)Спасибо, ЗоркийГлаз, но пока не могу исправить....так сразу же дадут медаль за лучшие познания русского языка, а слово "амперсанд" совсем не из Руси пошло....И в слово писАть: вставлять первую букву алфавита или последнюю - не имеет никакого значения, т.к. мы душу в письме изливаем в любом случае!
24. user1194102 09.01.20 19:38 Сейчас в теме
два вложенных запроса накладно для программ, проще сделать пакетным запросом из ТЧ документа и два левых соединения с двумя регистрами.
26. ImHunter 312 11.03.20 07:56 Сейчас в теме
Кстати, вопрос передачи параметров в запрос как-то всегда остается за кадром.
Например, если передавать большую ТЗ для создания ВТ, то при этом на сервер пуляется куча запросов вставки каждой строки в ВТ.
Этот момент тоже нужно иметь в виду.
Strannik777; +1 Ответить
27. sv_baranov 6 20.09.21 07:13 Сейчас в теме
А никто не думал, что это пример из учебника. И что глубокий смысл этого примера в использовании Менеджера временных таблиц.
Готов к тому что в меня полетят камни. Но. Перед авторами стояла непростая задача - сделать простой пример с использованием сложного объекта (менеджера временных таблиц).
Мне кажется они справились, потому что еще проще объяснить использование этого объекта, мне кажется нельзя.
user1366986; Strannik777; +2 Ответить
28. mihuilka 27.08.22 14:22 Сейчас в теме
Думал найду ответ в этой статье:
"Что лучше: использовать временные таблицы внутри запроса или выносить их в менеджера временных таблиц?"
Но в итоге ещё больше запутался))))
Оставьте свое сообщение