МФ и SQL - как обойти кривую выдачу

Автор Злоп, 24 мая 2025, 20:43

« назад - далее »
Печать
Вниз Страницы 1 2
Действия пользователя

Arbuz

#15
30 мая 2025, 15:05
Хм, ну вот, например, в ТиС в документе списание, ускорение в несколько тысяч раз, вместо десятков секунд — сотни миллисекунд:
Код Выделить 
//******************************************************************************
// ЗаполнениеПоДокументуОснованию()
//
// Параметры:
//  Нет
//
// Возвращаемое значение:
//  Нет
//
// Вызывается из формул элементов диалога:
//
// Описание:
//  Производит заполнение документа по документу  - основанию.
//
Функция ЗаполнениеПоДокументуОснованию()
	Перем СписанныеКоличества;
	
	Если ДокОснование.Выбран()=0 Тогда
		Возврат "Документ - основание не выбран!";
	КонецЕсли;
	
	Если (ДокОснование.Вид() <> "ИнвентаризацияТМЦ") Тогда
		Возврат "Неверный вид документа - основания!";
	КонецЕсли; // вид ДокОснования
		
	// сформируем список товаров, 
//{[*]Arbuz, 28.06.2021 14:02:53 
//	ТекстЗапроса = "//{{ЗАПРОС(ПредСписание)
//	|Период с '01.01.1980' по ДатаДок;
//	|Без итогов;
//	|Основание = Документ.СписаниеТМЦ.ДокОснование, Документ.Реализация.ДокОснование, Документ.ОтчетККМ.ДокОснование, Документ.РеализацияРозница.ДокОснование;
//	|Товар = Документ.СписаниеТМЦ.Номенклатура, Документ.Реализация.Номенклатура, Документ.ОтчетККМ.Номенклатура, Документ.РеализацияРозница.Номенклатура;
//	|Колво = Документ.СписаниеТМЦ.Количество, Документ.Реализация.Количество, Документ.ОтчетККМ.Количество, Документ.РеализацияРозница.Количество;
//	|Коэфф = Документ.СписаниеТМЦ.Коэффициент, Документ.Реализация.Коэффициент, Документ.ОтчетККМ.Коэффициент, Документ.РеализацияРозница.Коэффициент;
//	|Функция ВсегоКолво = Сумма(Колво);
//	|Группировка Товар без групп;
//	|Группировка Коэфф;
//	|Условие(Основание = ДокОснование);
//	|"//}}ЗАПРОС
//	;
//	_ф = _GetPerformanceCounter(); //[+]Arbuz, 28.06.2021 13:46:48 
//	
//	Запрос = СоздатьОбъект("Запрос");
//	Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
//		Возврат "Ошибка при отборе товаров.";
//	КонецЕсли;
//	
//	глОтладка("Чёрный " + (_GetPerformanceCounter() - _ф)); //[+]Arbuz, 28.06.2021 13:46:53 
//	
//	Запрос.Выгрузить(СписанныеКоличества, 1, 0);
//	
//	глОтладка(СписанныеКоличества, 3); //[+]Arbuz, 28.06.2021 13:45:10 
// -------------^^^===vvv-------------
	ТекстЗапроса = "
	|--EXPLAIN QUERY PLAN
	|SELECT
	|--	Товары.Документ [Основание :Документ]
	|	Товары.Товар [Товар :Справочник.Номенклатура]
	|	,Товары.Коэфф
	|	,SUM(Товары.Колво) ВсегоКолво
	|FROM (
	|	SELECT 
	|--		Документы.Документ_вид||Документы.Документ Документ
	|		COALESCE(
	|			СписаниеТМЦ.Номенклатура
	|			,Реализация.Номенклатура
	|			,ОтчетККМ.Номенклатура
	|			,РеализацияРозница.Номенклатура
	|		) Товар
	|		,COALESCE(
	|			СписаниеТМЦ.Количество
	|			,Реализация.Количество
	|			,ОтчетККМ.Количество
	|			,РеализацияРозница.Количество
	|		) Колво
	|		,COALESCE(
	|			СписаниеТМЦ.Коэффициент
	|			,Реализация.Коэффициент
	|			,ОтчетККМ.Коэффициент
	|			,РеализацияРозница.Коэффициент
	|		) Коэфф
	|	FROM (
	|		SELECT
	|			Ссылки.CHILDID Документ
	|--			,Журнал.IDDOCDEF Документ_вид
	|		FROM [__1S_crdoc] Ссылки
	|		INNER JOIN [Журнал] Журнал ON Ссылки.CHILDID = Журнал.IDDOC
	|		WHERE true
	|			AND Ссылки.PARENTVAL = :ДокОснование*
	|			AND Ссылки.MDID = '   0'
	|			AND Журнал.IDDOCDEF IN (:ВидДокумента.СписаниеТМЦ,:ВидДокумента.Реализация,:ВидДокумента.ОтчетККМ,:ВидДокумента.РеализацияРозница)
	|			AND Журнал.CLOSED=1 
	|			AND Журнал.DATE <= :Дата2
	|	) Документы
	|	LEFT JOIN [ДокументСтроки.СписаниеТМЦ] СписаниеТМЦ ON Документы.Документ = СписаниеТМЦ.IDDOC
	|	LEFT JOIN [ДокументСтроки.Реализация] Реализация ON Документы.Документ = Реализация.IDDOC
	|	LEFT JOIN [ДокументСтроки.ОтчетККМ] ОтчетККМ ON Документы.Документ = ОтчетККМ.IDDOC
	|	LEFT JOIN [ДокументСтроки.РеализацияРозница] РеализацияРозница ON Документы.Документ = РеализацияРозница.IDDOC
	|) Товары
	|GROUP BY Товар, Коэфф
	|";
	Запрос = глБазаДанных.НовыйЗапрос();	
	Запрос.Подставлять("Дата2"			,ДатаДок);
	Запрос.Подставлять("ДокОснование"	,ДокОснование);
	_ф = _GetPerformanceCounter();
	Попытка
		СписанныеКоличества = Запрос.ВыполнитьЗапрос(ТекстЗапроса);
	Исключение
		Возврат "Ошибка при отборе товаров.";
	КонецПопытки;
	глОтладка("1sqlite: " + (_GetPerformanceCounter() - _ф));
//}[*]___Arbuz, 28.06.2021 14:02:53 

	
	Валюта 		= ДокОснование.Валюта;
	Курс 		= ДокОснование.Курс;
	Кратность	= ДокОснование.Кратность;

	
	ТаблицаДокумента = СоздатьОбъект("ТаблицаЗначений");
	ДокОснование.ВыгрузитьТабличнуюЧасть(ТаблицаДокумента);
	
	// очищаем наш документ
	УдалитьСтроки();  
	
	ТаблицаДокумента.ВыбратьСтроки();
	Пока ТаблицаДокумента.ПолучитьСтроку() = 1 Цикл
		Если (ТаблицаДокумента.КоличествоУчет > ТаблицаДокумента.Количество) Тогда
			Товар = ТаблицаДокумента.Номенклатура;
			Колво = ТаблицаДокумента.КоличествоУчет - ТаблицаДокумента.Количество;
			
			// поищем в таблице уже списанных количеств
			Поз = 0;
			Пока (СписанныеКоличества.НайтиЗначение(Товар, Поз, "Товар") = 1)
			   и (Колво > 0)
			Цикл
				СписанныеКоличества.ПолучитьСтрокуПоНомеру(Поз);
				// пересчитаем количество в единицу измерения инвентаризации
				СписКолво  = СписанныеКоличества.ВсегоКолво * СписанныеКоличества.Коэфф / ТаблицаДокумента.Коэффициент;
				ПогашКолво = Мин(Колво, СписКолво);
				
				Если ПогашКолво >= СписКолво Тогда
					СписанныеКоличества.УдалитьСтроку(Поз);
				Иначе
//{[*]Arbuz, 28.06.2021 13:56:32 
//					СписанныеКоличества.Колво = (СписКолво - ПогашКолво) * ТаблицаДокумента.Коэффициент / СписанныеКоличества.Коэфф;
					СписанныеКоличества.ВсегоКолво = (СписКолво - ПогашКолво) * ТаблицаДокумента.Коэффициент / СписанныеКоличества.Коэфф;
//}[*]___Arbuz, 28.06.2021 13:56:32 
				КонецЕсли;
				
				Колво = Колво - ПогашКолво;
				
				Поз = 0;
			КонецЦикла;
			
			Если Колво > 0 Тогда
				НоваяСтрока();
				Номенклатура= Товар;
				Количество 	= Колво;
				Единица 	= ТаблицаДокумента.Единица;
				Коэффициент = ТаблицаДокумента.Коэффициент;
				
				// цену и сумму заполняем только по рознице				
				Если Склад.РозничныйСклад = 1 Тогда
					Цена 	= ТаблицаДокумента.Цена;
					Сумма 	= Цена * Количество;
				КонецЕсли;
			КонецЕсли;
		КонецЕсли;
	КонецЦикла; // по строкам основания       
	
	Если КоличествоСтрок() = 0 Тогда
		Возврат "В документе " + глПредставлениеДокумента(ДокОснование) +
				 " отсутствуют товары, учетное количество которых превышает фактическое";
	КонецЕсли;
	
	Возврат "";
	
КонецФункции // ЗаполнениеПоДокументуОснованию()
глБазаДанных это
Код Выделить 
глБазаДанных = СоздатьОбъект("SQLiteBase");
глБазаДанных.Открыть(":memory:");
глОтладка() можно заглушить или заменить на Сообщить()

И это не считая того, что, в принципе, с помощью прямых запросов на DBF можно получить на выборках из непосредственно полей документов/справочников(/проводок?) скорость сопоставимую (скорее превышающую) с выборками на чорных запросах из регистров. Конечно тут сильно зависит от архитектуры этих регистров, но признаюсь — грешен, пользуюсь во всю, часто лень проводить реорганизацию структуры базы.

Djelf

#16
30 мая 2025, 15:23
Поосторжнее с IN в 1sqlite, это коварная штука, если попадает в индекс, то может быть как и существенное ускорение, так и существенное замедление, вот пример того что может замедлить запрос
Код Выделить 
AND Журнал.IDDOCDEF IN (:ВидДокумента.СписаниеТМЦ,:ВидДокумента.Реализация,:ВидДокумента.ОтчетККМ,:ВидДокумента.РеализацияРозница)
тут про 1sqlite не Орефкова (там движок старый был, он не понимал такую оптимизацию, а про мою сборку, на новых движках), движок будет последовательно делать несколько сканирований, по количеству их наличия в IN (старый движок не видел индексов в IN) и такого не умел
Отключается просто, добавьте "+" перед сравнением и сверьте результаты
Код Выделить 
AND +Журнал.IDDOCDEF IN (:ВидДокумента.СписаниеТМЦ,:ВидДокумента.Реализация,:ВидДокумента.ОтчетККМ,:ВидДокумента.РеализацияРозница)

Arbuz

#17
30 мая 2025, 15:35
Цитата: Djelf от 30 мая 2025, 15:23Поосторжнее с IN в 1sqlite, это коварная штука
Да-да, я помню. В данном случае я просто пренебрёг — там же неизменяемый список из четырёх значений (подумаешь, четыре сканирования по индексу  ;D ). И, помнится, я пробовал разные варианты именно с точки зрения производительности, этот меня полностью устроил. И в новых версиях вроде же существенно улучшили этот момент, в том числе на виртуальных таблицах?

ЗЫ: А, вспомнил! Там выборка катастрофически сужается сначала по PARENTVAL и MDID и этот IN практически не имеет значения.

Харлампий Дымба

#18
31 мая 2025, 01:05
Цитата: Arbuz от 30 мая 2025, 15:05Хм, ну вот, например, в ТиС в документе списание, ускорение в несколько тысяч раз, вместо десятков секунд — сотни миллисекунд:
Ну тут черный запрос изначально был как-то не на месте. Наверное и ВыбратьПодчиненныеДокументы() хоть и не миллисекунды бы занял, но за доли секунды бы отработал, что для ЗаполнениеПоДокументуОснованию() приемлимо.
Что, конечно, не отменяет великолепие прямого запроса.

А вот интересно - может есть у кого уже готовое или подскажете: быстрый способ получить список подчиненных документов с произвольным уровнем подчинения в SQL.
Как вариант: найти все Плат.пор/РКО выписанные на основе любого из документов в цепочке Счет-Реализация-СФ и Плат.пор/РКО. Т.е. задаем документ-основание (конкретный Счет) и виды нужных подчиненных документов (Плат.пор и РКО) + возможный отбор проведенные/удаленные - и получаем список подчиненных документов нужного вида. Ну и защиту от зацикливания:, т.е. исключать определенные виды документов - нужна заглушка, если попались документы с множественным подчинением, типа Выписка (где документы-основания могут быть в табличной части).
То что есть сейчас - это функция, использующая вызов ВыбратьПодчиненныеДокументы() в рекурсии. Для одного документа - ерунда, но для отчета в тысячу документов, это уже 2 минуты. Но не могу сообразить как построить запрос правильно.

Arbuz

#19
05 июня 2025, 16:04
Цитата: Харлампий Дымба от 31 мая 2025, 01:05может есть у кого уже готовое или подскажете: быстрый способ получить список подчиненных документов с произвольным уровнем подчинения в SQL.
Хотел было кинуть СтруктураПодч.ert Орефкова, но там для каждого узла графа выполняется отдельный запрос
Код Выделить 
запросРодителей = базаДанных.НовыйЗапрос();
	запросРодителей.Подготовить("
	|select
	|substr(parentval, 3, 13) [Документ $Документ]
	|from [__1S_crdoc] Ссылки
	|where childid = @child and mdid = '   0'
	|order by parentval
	|");
	
	запросПотомков = базаДанных.НовыйЗапрос();
	запросПотомков.Подготовить("
	|select
	|Ссылки.childid [Документ $Документ],
	|Журнал.iddocdef [Документ_вид]
	|from [__1S_crdoc] Ссылки
	|inner join [Журнал] Журнал on Ссылки.childid = Журнал.iddoc
	|where Ссылки.parentval = @parent
	|and Ссылки.mdid = '   0'
	|order by Ссылки.childdate, Ссылки.childtime

ЗЫ: думаю, если помараковать, то можно и в один сделать через cte или подзапросами

Djelf

#20
05 июня 2025, 16:18
Цитата: Arbuz от 05 июня 2025, 16:04
Цитата: Харлампий Дымба от 31 мая 2025, 01:05может есть у кого уже готовое или подскажете: быстрый способ получить список подчиненных документов с произвольным уровнем подчинения в SQL.
Хотел было кинуть СтруктураПодч.ert Орефкова, но там для каждого узла графа выполняется отдельный запрос
Код Выделить 
запросРодителей = базаДанных.НовыйЗапрос();
	запросРодителей.Подготовить("
	|select
	|substr(parentval, 3, 13) [Документ $Документ]
	|from [__1S_crdoc] Ссылки
	|where childid = @child and mdid = '   0'
	|order by parentval
	|");
	
	запросПотомков = базаДанных.НовыйЗапрос();
	запросПотомков.Подготовить("
	|select
	|Ссылки.childid [Документ $Документ],
	|Журнал.iddocdef [Документ_вид]
	|from [__1S_crdoc] Ссылки
	|inner join [Журнал] Журнал on Ссылки.childid = Журнал.iddoc
	|where Ссылки.parentval = @parent
	|and Ссылки.mdid = '   0'
	|order by Ссылки.childdate, Ссылки.childtime
Когда этот запрос создавался для 1sqlite, и создавался на той старой версии, не существовало рекурсивных запросов в движке sqlite.
Теперь они есть. Мне кажется что это можно в один запрос засунуть...
Скорее всего делать не буду, экономия в 2-3 мс это ерунда...

Харлампий Дымба

#21
05 июня 2025, 16:58
Огромное спасибо! Я знал, что всё уже придумано умными людьми.
Буду разбираться, когда руки. СтруктураПодч.ert тоже скачал, на всякий случай, тоже посмотрю

Arbuz

#22
05 июня 2025, 17:55
Цитата: Харлампий Дымба от 05 июня 2025, 16:58СтруктураПодч.ert тоже скачал, на всякий случай, тоже посмотрю
Тот запрос, что я выложил — он слегка подправлен под современную 1sqlite, в части подключения виртуальных таблиц
Печать
Вверх Страницы 1 2
Действия пользователя