Гуру-тест: как ускорить запрос

Форум Программирование Общие вопросы программирования (Dev)

Платформа 1С v8.3

1. fixin 4253 10.01.12 12:25 Сейчас в теме

Вот запрос, который проставляет цены в документ (конфа Розница). Нюанс в том, что если цена за характеристику указана, то используется эта цена, если не указана - то цена без характеристики.
В общем, оказалось, что если запускать по всем товарам на остатках, непропорционально сильно тормозит на файловой версии (2 часа работает) на 4000 позициях и 5 секунд на 200 позициях.

Думайте, какой способ я нашел, чтобы ускорить код. ;-)
Метод настоящего гуру. Потом опубликую, если не догоните.
Хотя бы идеи предложите.

ВЫБРАТЬ
	ТабличнаяЧастьДокумента.Номенклатура КАК Номенклатура,
	ТабличнаяЧастьДокумента.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
	ТабличнаяЧастьДокумента.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
	ЕСТЬNULL(ЦеныНоменклатурыМагазиновСрезПоследних.Цена, ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.Цена) КАК Цена,
	ЕСТЬNULL(ЦеныНоменклатурыМагазиновСрезПоследних.ЕдиницаИзмерения, ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.ЕдиницаИзмерения) КАК ЕдиницаИзмеренияЦены,
	ТабличнаяЧастьДокумента.НомерСтроки КАК НомерСтроки
ИЗ
	ВременнаяТабличнаяЧастьДокумента КАК ТабличнаяЧастьДокумента
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыМагазинов.СрезПоследних(
				&ДатаДокумента,
				Номенклатура В (&МассивНоменклатуры)
					И ХарактеристикаНоменклатуры В (&МассивХарактеристик)
					И Магазин = &Магазин) КАК ЦеныНоменклатурыМагазиновСрезПоследних
		ПО ТабличнаяЧастьДокумента.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследних.Номенклатура
			И ТабличнаяЧастьДокумента.ХарактеристикаНоменклатуры = ЦеныНоменклатурыМагазиновСрезПоследних.ХарактеристикаНоменклатуры
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыМагазинов.СрезПоследних(
				&ДатаДокумента,
				Номенклатура В (&МассивНоменклатуры)
					И ХарактеристикаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка)
					И Магазин = &Магазин) КАК ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик
		ПО ТабличнаяЧастьДокумента.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.Номенклатура

Показать

Ответы

Подписаться на ответы Инфостарт бот

Свернуть все

9. taiba 87 21.02.13 11:31 Сейчас в теме

(1) так а авторский вариант какой?

10. ildarovich 7861 21.02.13 15:15 Сейчас в теме

(1) Присоединюсь к вопросу (9) ... Или "чернила выцвели"?

2. romansun 193 10.01.12 14:40 Сейчас в теме

ну фиг знает, без базы-то.. теоретическое программирование такое программирование

навскидку, идея в том, что соединение надо сделать одно. Добавить ПустуюСсылку в &МассивНоменклатуры и соединиццо. Единственное, надо заранее в ТЧ предпросчитать те позиции, где какбэ пустая Характеристика. Например, загодя соединившись с этим регистром перескинув ТЧ с правдивой характеристикой в ВТ.

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

Чо еще... Ну индексы можно выставить...

Я обычно в таких случаях серьёзно упрощаю запрос до "выбрать первые 100 из .. где.. " и дальше, добавляя новые куски, смотрю после чего резко просело.

4. fixin 4253 10.01.12 15:15 Сейчас в теме

(2) не, ну это немного ускоряет, но истина топталась где-то в другом месте ... ;-)

3. romansun 193 10.01.12 14:41 Сейчас в теме

а вообще, конечн, мысли ворочаются сегодня с огромным трудом

5. Азбука Морзе 105 15.02.13 10:56 Сейчас в теме

Так как время исполнения запроса имеет нелинейную зависимость от входных данных, решение состоит в следующем:

Запрос выполняем в цикле небольшими порциями. Для этой задачи 4000 позиций эквивалентны 20 порциям по 200 позиций. Таким образом время выполнения сократися с 2 часов до 20*5сек = 100 сек.
В силу сильной нелинейности можно предположить, что время выполнения запроса для одной позиции составляет милисекунду. Простой расчет даст время выполнения 4000 * 0,001 = 4сек! Плюс время на выполнение самого цикла.

Миф фирмы 1С, что запросы в цикле моветон разоблачен:-)

6. taiba 87 15.02.13 18:23 Сейчас в теме

без вникания в нюансы:

Сделать выборку по всем ценам с отбором только по номенклатуре;

для объединения дать условие ТабличнаяЧастьДокумента.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.Номенклатура или ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.Номенклатура = ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка)

7. ildarovich 7861 16.02.13 12:06 Сейчас в теме

Рискну предположить, что суть проблемы не в том, что нужно еще найти цену для пустой характеристики.
Проблема в соединении больших таблиц при отсутствии подходящих индексов.
Трудоемкость здесь примерно 4000 х (4000+4000).
То есть даже если убрать второе соединение, порядок времени выполнения запроса (4000 х 4000) останется тем же (1? час для 4000 элементов).
Поэтому, кажется, ответ должен быть в том, чтобы использовать не соединение, а группировку по номенклатуре, объединив табличную часть и срез (срезы последних).
Это стандартный путь ускорения запросов в файловых базах.
Можно предположить, что в них реализован единственный способ соединения: nested loop, тогда как в SQL при отсутствии подходящих индексов используется еще hash join и merge join.
Ну а группировка по сути моделирует соединение слиянием и должна дать существенный выигрыш.
Интересно было бы знать, намеренно ли в файловой базе не сделали hash join и merge join, чтобы усилить преимущество более дорогой версии.
После первого этапа оптимизации можно посмотреть на необходимость срезов последних, которые в файловом варианте, вероятно, тоже не слишком эффективны.

8. ildarovich 7861 18.02.13 01:23 Сейчас в теме

(7) Вот такой запрос обрабатывает 4000 позиций за 0.822 секунды

ВЫБРАТЬ
	ВременнаяТабличнаяЧастьДокумента.НомерСтроки,
	ВременнаяТабличнаяЧастьДокумента.Номенклатура,
	ВременнаяТабличнаяЧастьДокумента.ХарактеристикаНоменклатуры,
	ВременнаяТабличнаяЧастьДокумента.ЕдиницаИзмерения,
	NULL КАК Цена
ПОМЕСТИТЬ Объединение
ИЗ
	ВременнаяТабличнаяЧастьДокумента КАК ВременнаяТабличнаяЧастьДокумента

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	0,
	ЦеныНоменклатурыМагазиновСрезПоследних.Номенклатура,
	ЦеныНоменклатурыМагазиновСрезПоследних.ХарактеристикаНоменклатуры,
	NULL,
	ЦеныНоменклатурыМагазиновСрезПоследних.Цена / ЦеныНоменклатурыМагазиновСрезПоследних.ЕдиницаИзмерения.Коэффициент
ИЗ
	РегистрСведений.ЦеныНоменклатурыМагазинов.СрезПоследних(
			&ДатаДокумента,
			Номенклатура В (&МассивНоменклатуры)
				И Магазин = &Магазин) КАК ЦеныНоменклатурыМагазиновСрезПоследних

ОБЪЕДИНИТЬ ВСЕ

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	МАКСИМУМ(Объединение.НомерСтроки) КАК НомерСтроки,
	Объединение.Номенклатура КАК Номенклатура,
	Объединение.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
	МАКСИМУМ(Объединение.ЕдиницаИзмерения) КАК ЕдиницаИзмерения,
	МАКСИМУМ(Объединение.Цена) КАК Цена
ИЗ
	Объединение КАК Объединение

СГРУППИРОВАТЬ ПО
	Объединение.Номенклатура,
	Объединение.ХарактеристикаНоменклатуры

ИМЕЮЩИЕ
	МАКСИМУМ(Объединение.НомерСтроки) > 0

Показать

Глубина истории цен в тесте была равна 5-ти.

11. fixin 4253 21.02.13 15:38 Сейчас в теме

Авторский вариант здесь.
Ларчик просто открывался - на вайлофое версии хреново работает объединение со срезом последних...
Так вот работает быстро и в файловой, и в SQL версии.
Тонкие такие нюансы, головой не понять, только профайлером. (Замером производительности).
Но реально тормозило.

ВЫБРАТЬ
	Т.Цена,
	Т.Номенклатура,
	Т.ХарактеристикаНоменклатуры,
	Т.ЕдиницаИзмерения
ПОМЕСТИТЬ ТЦены
ИЗ
	РегистрСведений.ЦеныНоменклатурыМагазинов.СрезПоследних(
			&ДатаДокумента,
			Номенклатура В (&МассивНоменклатуры)
				И (ХарактеристикаНоменклатуры В (&МассивХарактеристик)
					ИЛИ ХарактеристикаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка))
				И Магазин = &Магазин) КАК Т
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТабличнаяЧастьДокумента.Номенклатура КАК Номенклатура,
	ТабличнаяЧастьДокумента.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
	ТабличнаяЧастьДокумента.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
	ЕСТЬNULL(ЦеныНоменклатурыМагазиновСрезПоследних.Цена, ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.Цена) КАК Цена,
	ЕСТЬNULL(ЦеныНоменклатурыМагазиновСрезПоследних.ЕдиницаИзмерения, ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.ЕдиницаИзмерения) КАК ЕдиницаИзмеренияЦены,
	ТабличнаяЧастьДокумента.НомерСтроки КАК НомерСтроки
ИЗ
	ВременнаяТабличнаяЧастьДокумента КАК ТабличнаяЧастьДокумента
		ЛЕВОЕ СОЕДИНЕНИЕ ТЦены КАК ЦеныНоменклатурыМагазиновСрезПоследних
		ПО ТабличнаяЧастьДокумента.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследних.Номенклатура
			И ТабличнаяЧастьДокумента.ХарактеристикаНоменклатуры = ЦеныНоменклатурыМагазиновСрезПоследних.ХарактеристикаНоменклатуры
		ЛЕВОЕ СОЕДИНЕНИЕ ТЦены КАК ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик
		ПО ТабличнаяЧастьДокумента.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследнихБезХарактеристик.Номенклатура

Показать

12. ildarovich 7861 22.02.13 14:16 Сейчас в теме

(11) Да, проблема была не в соединении со срезами последних. Так, что ответ (7) для данной задачи не подходит. Хотя решение (8) тоже быстрое. Корень проблемы оказался в проверке

Номенклатура В (&МассивНоменклатуры) И ХарактеристикаНоменклатуры В (&МассивХарактеристик)

а конкретнее - в условии "И". Видимо, система декартово соединяет эти таблицы (4000х4000), а затем строит соединение с ними. Впредь будем осторожнее с условиями "И" в параметрах виртуальных таблиц. Спасибо за этот пример!

13. fixin 4253 22.02.13 14:43 Сейчас в теме

(12) да, видимо вы правы. Но не вдаваясь в тонкости, я, как настоящий гуру, просто решил проблему в обход - сформировал сначала маленькую таблицу, а уже потом с ней соединял. ;-)

Внимание! Тема сдана в архив