Вторник. Вопрос 5

TimonChD · 15 мар 2013, 15:42

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

Если запрос содержит соединения с подзапросами, то это может привести к следующим негативным последствиям:

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

Код Выделить

ВЫБРАТЬ
	ЛимитыКредиторскойЗадолженности.Контрагент КАК Контрагент,
	ЛимитыКредиторскойЗадолженности.Договор КАК Договор,
	СУММА(ЛимитыКредиторскойЗадолженности.Сумма) КАК Сумма
ПОМЕСТИТЬ Лимиты
ИЗ
	РегистрСведений.ЛимитыКредиторскойЗадолженности КАК ЛимитыКредиторскойЗадолженности
ГДЕ
	ЛимитыКредиторскойЗадолженности.Организация = &Организация

СГРУППИРОВАТЬ ПО
	ЛимитыКредиторскойЗадолженности.Контрагент,
	ЛимитыКредиторскойЗадолженности.Договор

ИНДЕКСИРОВАТЬ ПО
	Контрагент,
	Договор
;

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

SirYozha · 15 мар 2013, 16:41

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

2. Проиндексируем созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения.

3. Не задано условие, которые фильтрует только по проведенным документам. Возможно, что существуют удаленные или не проведенные документы. Их надо исключить.

Код Выделить

ПоступлениеТоваровИУслуг.Проведен

Opera · 15 мар 2013, 17:41

1. в условии ГДЕ от ПоступлениеТоваровИУслуг.Договор.ДоговорСПодрядчиком и ПоступлениеТоваровИУслуг.Договор.ДоговорСПоставщиком за счет добавления соединения с таблицей ПоступлениеТоваровИУслуг потому что в первоначальном варианте на каждую запись фактически делался бы еще один запрос к таблице договоров для каждого условия
2. поменяла левое соединение на внутренее т.к. условие ПоступлениеТоваровИУслуг.Сумма < ЛимитыКредиторскойЗадолженности.Сумма само по себе означает что данные должны быть.
3. переместила условие ПоступлениеТоваровИУслуг.Сумма < ЛимитыКредиторскойЗадолженности.Сумма в условия объединения для сокращения возможного количества операций.
в общем случае условие ПоступлениеТоваровИУслуг.Сумма < ЛимитыКредиторскойЗадолженности.Сумма будет проверяться для всех строк результирующего объединения таблиц.
4. условие ПоступлениеТоваровИУслуг.Организация = &Организация
в общем случае уменьшит выборку документов ПоступлениеТоваровИУслуг что должно увеличить скорость выполнения запроса.

1Chelpk · 15 мар 2013, 20:28

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

AAlexandra · 15 мар 2013, 23:22

Цитата: mixqn от 12 мар 2013, 00:51Вашему вниманию предлагается запрос. Произведите его оптимизацию и поясните свои действия.

Ну.. первое, что бросается в глаза - подзапрос к РС:

Код Выделить

        ВЫБРАТЬ
            ЛимитыКредиторскойЗадолженности.Организация КАК Организация,
            ЛимитыКредиторскойЗадолженности.Контрагент КАК Контрагент,
            ЛимитыКредиторскойЗадолженности.Договор КАК Договор,
            СУММА(ЛимитыКредиторскойЗадолженности.Сумма) КАК Сумма
        ИЗ
            РегистрСведений.ЛимитыКредиторскойЗадолженности КАК ЛимитыКредиторскойЗадолженности
        ГДЕ
            ЛимитыКредиторскойЗадолженности.Организация = &Организация
        
        СГРУППИРОВАТЬ ПО
            ЛимитыКредиторскойЗадолженности.Организация,
            ЛимитыКредиторскойЗадолженности.Контрагент,
            ЛимитыКредиторскойЗадолженности.Договор

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

В условии есть чудная конструкция:

Код Выделить

    И (ПоступлениеТоваровИУслуг.Договор.ДоговорСПодрядчиком = ИСТИНА
            ИЛИ ПоступлениеТоваровИУслуг.Договор.ДоговорСПоставщиком = ИСТИНА)

Двойное обращение через точку = неявное левое соединение.
Можно было бы его сделать явными, присоединив таблицу "ДоговорыКонтрагентов". Но на производительности запроса это никак не отразится: при трансформации запроса 1с в запрос к СУБД 1с-ка все равно сделает одно левое соединение, несмотря на то что обращаемся к таблице ПоступлениеТоваровИУслуг.Договор мы дважды.

Итак, что остается:

Код Выделить

    (ПоступлениеТоваровИУслуг.Сумма < &МинимальнаяСуммаЗадолженности
            ИЛИ ПоступлениеТоваровИУслуг.Сумма < ЛимитыКредиторскойЗадолженности.Сумма)

Нет проверки на естьNULL(ЛимитыКредиторскойЗадолженности.Сумма)
Условие (Число < NULL) всегда возвращает ложь.
Можно попробовать из условия "где" перенести проверку в соединение таблиц и вместо левого соединения сделать внутреннее. Примерно так:

Код Выделить

ВНУТРЕННЕЕ СОЕДИНЕНИЕ (...) КАК ЛимитыКредиторскойЗадолженности
ПО
(ПоступлениеТоваровИУслуг.Сумма < &МинимальнаяСуммаЗадолженности)
ИЛИ	
(ПоступлениеТоваровИУслуг.Контрагент = ЛимитыКредиторскойЗадолженности.Контрагент
            И ПоступлениеТоваровИУслуг.Договор = ЛимитыКредиторскойЗадолженности.Договор
            И ПоступлениеТоваровИУслуг.Сумма < ЛимитыКредиторскойЗадолженности.Сумма)

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

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

Вторник. Вопрос 5

TimonChD

SirYozha

Opera

1Chelpk

AAlexandra

Поиск