gifts2017

Объединение двух таблиц значений запросом, циклом

Опубликовал Сергей Пономарёв (izidakg) в раздел Программирование - Инструментарий

При выполнении различных задач очень часто возникает необходимость объединить 2 таблицы значений. Типового механизма на эту тему нет, предполагается что это легко делается циклом с проверкой наличия записи в ТЗ1, куда сливаем данные из ТЗ2.
Но при количестве записей хотя бы 500 циклом, пользователю становится невмоготу от ожидания. В зависимости от ситуации используются циклы или запросы.
А если ТЗ1 и ТЗ2 по количеству строк под 100 000?
А если ТЗ1 и ТЗ2 отличаются по количеству колонок?

Всегда все объединения ТЗ делал циклом с проверкой наличия строки перед добавлением. Это конечно для случаев когда нельзя получить данные сразу в одну ТЗ любым методом.

Занимаясь автоматизацией получения данных с ресурса от поставщика, столкнулся с проблемой. количество строк в каталоге поставщика около 100 000 и получение их возможно только частями по принадлежности к группам.

Просмотрев массу примеров, взял 1 за основу. в моем варианте получилось в 2 раза меньше строк. Работает стабильно, но возникла следующая проблема - ТЗ1 по количеству колонок отличается от ТЗ2. Немного дополнил функцию и циклом 2 ТЗ объединяются без проблем. И снова НО, потребовалось получить весь каталог для обработки, а это в моем случае 23 запроса по группам верхнего уровня. Объединение ТЗ циклом занимает от 30 сек до 210 в зависимости от размера получаемого блока. В совокупности у меня получилось почти 20 минутное ожидание чтобы собрать каталог товаров поставщика в 1 ТЗ. Это очень долго.

Поискав примеры объединения 2-х ТЗ через запрос, собрал свой рабочий вариант. Встречал разные примеры, вот один из них http://zapros-1c-8.ru/9-yazik-zaprosov-1c-8/15-union.

И снова НО: а если ТЗ которые нужно объединять не один конкретный вариант, а их много. Писать под каждый вариант отдельный запрос на объединение? Большинство так и делают. 

В моем случае возможных вариантов ТЗ что требуется объединить не 1 и 2, а много больше и по мере развития проекта, над которым работаю, их количество будет только расти, поэтому разработал вариант универсальный.

Объединение ведется по любой указанной колонке.

Уже выложив пример процедур с использованием цикла и запроса, ну и конечно получив первые комментарии, при отладке очередной задачи частично переписал обе процедуры. Сразу оговорюсь, ситуации бывают разные, изначально получил более оптимальный алгоритм через запрос, но потом учитывая комментарий об индексе и добавив сворачивание ТЗ, получил что циклом в 2 раза быстрее чем запросом. скорость обработки зависит от многих факторов: количество строк, колонок, уникальных записей. После модернизации, мне более стал подходить вариант циклом, хотя есть другая задача, где запрос с 15% отрывом работает быстрее, так что использую оба варианта.

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

Вариант 1. Объединение циклом:

Функция ОбъединитьТЗЦиклом(ТЗ1, ТЗ2, КолонкаПоиска, СворачиватьТЗ = Ложь)
	
	Если НЕ ЗначениеЗаполнено(ТЗ1) Тогда
		Возврат ТЗ2;
	ИначеЕсли НЕ ЗначениеЗаполнено(ТЗ2) Тогда
		Возврат ТЗ1;
	КонецЕсли;
	
	Если СворачиватьТЗ Тогда
		СписокКолонок = "";
		КолКолонок = 0;
		Для Каждого КолонкаТЗ Из ТЗ1.Колонки Цикл
			КолКолонок = КолКолонок + 1;
			СписокКолонок = СписокКолонок + КолонкаТЗ.Имя+?(КолКолонок = ТЗ1.Колонки.Количество(),"",",");
		КонецЦикла;
		ТЗ1.Свернуть(СписокКолонок);
		СписокКолонок = "";
		КолКолонок = 0;
		Для Каждого КолонкаТЗ Из ТЗ2.Колонки Цикл
			КолКолонок = КолКолонок + 1;
			СписокКолонок = СписокКолонок + КолонкаТЗ.Имя+?(КолКолонок = ТЗ2.Колонки.Количество(),"",",");
		КонецЦикла;
		ТЗ2.Свернуть(СписокКолонок);
	КонецЕсли;
	
	// таблица источник должна быть меньше, что сократит время обработки
	Результат = Новый ТаблицаЗначений;  
	Если ТЗ1.Количество() > ТЗ2.Количество() Тогда
		Результат = ТЗ1.Скопировать();
		ТЗДонор   = ТЗ2.Скопировать();
	Иначе
		Результат = ТЗ2.Скопировать();
		ТЗДонор   = ТЗ1.Скопировать();
	КонецЕсли;
	
	// Дополняем результурующую ТЗ колонками, что есть только в ТЗДонор
	Для Каждого КолонкаТЗ Из ТЗДонор.Колонки Цикл
		Если Результат.Колонки.Найти(КолонкаТЗ.Имя) = Неопределено Тогда
			Результат.Колонки.Добавить(КолонкаТЗ.Имя);
		КонецЕсли;
	КонецЦикла;
	Результат.Индексы.Добавить(КолонкаПоиска);

	// определение количества, это тоже требует времени - делаем вне цикла
	РезультатКолонкиКоличество = Результат.Колонки.Количество();
	ТЗДонорКолонкиКоличество = ТЗДонор.Колонки.Количество();
	Для Каждого СтрокаТЗДонор из ТЗДонор цикл
		
		СтрокаРезультат = Результат.Найти(СтрокаТЗДонор[КолонкаПоиска],КолонкаПоиска); 
		
		Если СтрокаРезультат = Неопределено Тогда
			НоваяСтрока = Результат.Добавить();
			ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаТЗДонор);
		Иначе
			Если РезультатКолонкиКоличество <> ТЗДонорКолонкиКоличество Тогда
				ЗаполнитьЗначенияСвойств(СтрокаРезультат, СтрокаТЗДонор);
			КонецЕсли;
		КонецЕсли;
		
	КонецЦикла;
			
	Возврат Результат;
	
КонецФункции // ОбъединитьТЗЦиклом()

 Вариант 2. Объединение запросом:

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

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

И небольшое дополнение к решению задачи по объединению двух таблиц значений. Дело в том что при работе с ТЗ в запросе есть определенные ограничения, например строки неограниченной длины там недопустимы, другими словами при создании колонки ТЗ (если тип СТРОКА) нужно ЧЕТКО указать длину строки. Иначе при выполнении запроса получите ошибку: "Тип не может быть выбран в запросе". Поэтому будте внимательны и четко прописывайте тип колонок, а на случай если: некогда, пофиг, ну или просто невозможно это сделать по какой либо причине, предлагаю такую процедуру для исправления:

взято и переработано немного отсюда: ссылка

Функция ПодготовитьТЗ(ТЗВходящая) Экспорт 
	
	РабочаяТаблица = Новый ТаблицаЗначений;
	
	ПерваяСтрока = ТЗВходящая[0];
	Для Каждого КолонкаТаблицы из ТЗВходящая.Колонки Цикл
		ТипКолонки = ""+ТипЗнч(ПерваяСтрока[КолонкаТаблицы.Имя]);
		Если Найти(ВРег(ТипКолонки), "СТРОКА")> 0 Тогда 
			Тип = Новый ОписаниеТипов(ТипКолонки, , Новый КвалификаторыСтроки(500));
		Иначе
			Тип = Новый ОписаниеТипов(ТипКолонки);
		КонецЕсли;
		РабочаяТаблица.Колонки.Добавить(КолонкаТаблицы.Имя, Тип);
	КонецЦикла;
	
	Для Каждого СтрокаТаблицы из ТЗВходящая Цикл 
		СтрокаРабочаяТаблица = РабочаяТаблица.Добавить();
		Для Каждого КолонкаТаблицы из ТЗВходящая.Колонки Цикл
			СтрокаРабочаяТаблица[КолонкаТаблицы.Имя] = СтрокаТаблицы[КолонкаТаблицы.Имя];
		КонецЦикла;
	КонецЦикла;
	
	Возврат РабочаяТаблица;
	
КонецФункции // ПодготовитьТЗ

 


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

Наименование Файл Версия Размер Кол. Скачив.
Объединение ТЗ (цикл, запрос)
.epf 15,84Kb
15.12.14
11
.epf 15,84Kb 11 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. TMV 14.12.14 10:18
(0) В типовых конфах есть такая функция ОбщегоНазначения.ЗагрузитьВТаблицуЗначений.
2. Сергей (ildarovich) 14.12.14 19:22
На мой взгляд,
Но при количестве записей хотя бы 500 циклом, пользователю становится невмоготу от ожидания. Цикл это долго
объясняется тем, что большую часть времени занимает вот эта строчка
СтрокаРезультат = Результат.Найти(СтрокаТЗДонор[КолонкаПоиска],КолонкаПоиска);
Так происходит потому, что по колонке поиска в таблице "Результат" не создан индекс.
Результат.Индексы.Добавить(КолонкаПоиска);
Как только это будет сделано, все проблемы торможения объединения исчезнут и можно будет не городить огород с запросом, который в этой задаче просто лишний. Если захотеть, можно затем еще чуть ускорить объединение циклом.
Yashazz; artbear; AllexSoft; vikad; +4 Ответить
3. Сергей Пономарёв (izidakg) 15.12.14 10:40
конечно есть ОбщегоНазначения.ЗагрузитьВТаблицуЗначений:
Процедура ЗагрузитьВТаблицуЗначений(ТаблицаИсточник, ТаблицаПриемник) Экспорт

// Заполним значения в совпадающих колонках.
Для каждого СтрокаТаблицыИсточника Из ТаблицаИсточник Цикл

СтрокаТаблицыПриемника = ТаблицаПриемник.Добавить();
ЗаполнитьЗначенияСвойств(СтрокаТаблицыПриемника, СтрокаТаблицыИсточника);

КонецЦикла;

КонецПроцедуры // ЗагрузитьВТаблицуЗначений()

и сразу вопрос: эта процедура соединяет 2 ТЗ? эта процедура вставит колонки отсутствующие в одной из ТЗ?
если речь идет только про добавление строк в ТЗ1 одинаковую по структуре с ТЗ2 и без контроля дублей по колонке поиска, то да.
Предложенные алгоритмы решают мягко говоря другие задачи, очень мягко говоря.
про индекс - да так быстрее будет, дополню, но - как уже позже выяснилось, ну у меня во всяком случае. результирующая ТЗ более 250000 строк, может быть и больше. и даже добавив индекс время обработки ОЧЕНЬ ДОЛЬШЕ ЗАПРОСА. а ведь сбор данных в моем случае это еще не вся требуемая работа и т.к. дорога каждая секунда, то запрос меня спасает.
Очень многие вопросы 1С рекомендует обрабатывать запросами, не я придумал. и приведенный пример одно из объяснений почему. При решении большинства задач с таблицами сам в основном пользуюсь циклами и если речь идет про малые объемы данных, то да и еще раз да - если не требуется ничего особого делать с данными и данных мало, то цикл. И даже если надо чего делать, но данных не много, тоже можно цикл - для мелкой задачи задержка на 10-40 сек не беда наверное, пользователь такое легко прощает.
А читали описание подробно? Напомню: 23-28 отдельных блоков\запросов данных с внешней БД (1 запросом невозможно к сожалению), т.е. 23-28 раз нужно соединить 2 ТЗ. Нужна результрующая ТЗ без дублей по колонке поиска, Количества колонок может отличаться (это конечно исключительная ситуация, но разве ни у кого не было ее?). От 30 до 210 сек на объединение 2 ТЗ
Так вот циклом только объединение таблиц более 40 минут - на месте пользователя поддержу любые экзекуции над программером
Обработка запросомв среднем 1-2 сек на блок. сам запрос в 90% не более 1 сек, но бывает 2-3. как показывает тестирование это от загрузки компа, ну и сама подготовка объединения. Общее время обработки запросом сократилось до 4 мин 36 сек
4. Сергей (ildarovich) 15.12.14 11:15
(3) izidakg,
результирующая ТЗ более 250000 строк, может быть и больше. и даже добавив индекс время обработки ОЧЕНЬ ДОЛЬШЕ ЗАПРОСА
А как тогда понимать
Обработка циклом в среднем 1-2 сек на блок. сам запрос в 90% не более 1 сек, но бывает 2-3
Последнее похоже на правду. Из-за того, что при обработке таблиц данных запросом очень много времени тратится на ввод данных в запрос, этот метод должен проигрывать примерно в два раза циклу. Поэтому, если вы пишете
Общее время обработки запросом сократилось до 4 мин 36 сек
Это означает, что корректно написанное соединение циклом сократит время до 2 мин 18 сек.
Также нужно заметить, что хотя в заголовке речь идет об объединении таблиц значений, по коду получается, что вы их соединяете.
5. Сергей Пономарёв (izidakg) 15.12.14 14:48
(4) ildarovich,
насчет 1-2 циклом опечатался - это к запросу относится, на цикл уходит минимум 20-30 сек. в среднем (у меня), но это без индекса, добавлю его и проведя несколько тестов, выложу результат для сравнения. у меня минимум около 8000 строк в ТЗ.
Еще раз уточняю, что речь идет не про добавление строк из ТЗ1 в ТЗ2, а про объединение двух ТЗ, и решаются вопросы:
1) в результрующей ТЗ значения в колонке поиска уникальны (без дублей по колонке поиска)
2) Результирующая ТЗ содержит колонки обоих ТЗ, т.е. ТЗ1 может отличаться составом колонок от ТЗ2
3) универсальность - не нужно писать отдельную процедуру для каждого варианта комбинаций ТЗ
6. Яков Коган (Yashazz) 18.12.14 09:20
Можно я задам глупый вопрос: а никто не замерял скорость для такого метода, как просто в цикле слепить вместе все таблицы, а потом свернуть? Я просто не очень понимаю, зачем выше обсуждавшийся поиск вообще нужен.
7. Сергей Пономарёв (izidakg) 18.12.14 10:04
для определенных задач можно и так и возможно даже что будет быстрее хотя бы потому что строк в такой процедуре меньше
это универсальная процедура, через которую можно пропустить любую ТЗ и тут тоже есть сворачивание что при сильном засорении ТЗ дает прирост скорости. сворачивание по указанию в параметрах процедуры. тест показал что сильное засорение ТЗ при количестве строк более 10000 уже дает сильный прирост времени объединения ТЗ, со сворачиванием быстрее. при получении данных блоками в цикле, где потом на выходе надо иметь 1 ТЗ это самый оптимальный вариант.
изначально запросом было быстрее, но потом немного поправил код обработки циклом и зд у меня немного изменилась, после вышло что циклом быстрее почти в 2 раза. 100 000 примерно за 35 сек. в ТЗ более 20 колонок
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа