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

prickly · 12 мар 2013, 14:23

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

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

1. Убрал выборку по поступлению во вложенный запрос и наложил условие, быстрее будет выполнятся.
2. перенес условие в связи

mamanelli · 12 мар 2013, 14:23

1а. Не внушает доверия

ЦитироватьСУММА(ЛимитыКредиторскойЗадолженности.Сумма) КАК Сумма

с точки зрения логики. Во-первых "лимит" - это что-то ограничивающее по смыслу, суммировать его как-то не айс.

1б.

ЦитироватьСУММА(ЛимитыКредиторскойЗадолженности.Сумма) КАК Сумма

в итоговой выборке не используется, соответственно, незачем его вычислять.
Использующаяся в условии конструкция

ЦитироватьГДЕ ... ПоступлениеТоваровИУслуг.Сумма < ЛимитыКредиторскойЗадолженности.Сумма)

сравнивает сумму документа со значением ресурса записи РС.

2. В условие соединения

ЦитироватьПО ПоступлениеТоваровИУслуг.Контрагент = ЛимитыКредиторскойЗадолженности.Контрагент
И ПоступлениеТоваровИУслуг.Договор = ЛимитыКредиторскойЗадолженности.Договор

надо бы добавить "ПоступлениеТоваровИУслуг.Организация = ЛимитыКредиторскойЗадолженности.Организация"
Иначе в выборку попадут документы по другой организации.

3.

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

не ошибка, но правильнее использовать условие без "= ИСТИНА".

4.

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

Значение "ЛимитыКредиторскойЗадолженности.Сумма" может принять значение NULL, что вызовет ошибку выполнения запроса.

5. Можно сделать ВНУТРЕННЕЕ соединения документа "Поступление..." со справочником "Договоры контрагентов", на второй наложить условие

Код Выделить

ГДЕ ДоговорыКонтрагентов.ДоговорСПодрядчиком И  ДоговорыКонтрагентов.ДоговорСПоставщиком)

.

grupenfurer · 12 мар 2013, 14:41

При написании запросов не следует использовать соединения с подзапросами. Поэтому более оптимальным будет пакетный запрос:

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

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

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

elinatn@yandex.ru · 12 мар 2013, 14:50

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

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

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

ITW · 12 мар 2013, 16:17

Из вложеного запроса убрать Оргганизация , т.к. она однозначно задается внешним параметром &Организация
Убрать Котрагент т.к. он уже содержится в Договоре.

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

toxicoff · 12 мар 2013, 17:48

Таблицу ЛимитыКредиторскойЗадолженности вынести во временную таблицу, потом произвести соединение.
Убрал из запроса к ЛимитыКредиторскойЗадолженности лишние поля если используется отбор только по организации:
ЛимитыКредиторскойЗадолженности.Контрагент КАК Контрагент,
ЛимитыКредиторскойЗадолженности.Договор КАК Договор,

Escander · 12 мар 2013, 17:59

Следут вынести условие на реквизит РегистраСведний Организация из подзапроса в условия связи с документом ПоступлениеТоваровИУслуг (организация = организация). Это позволит попасть "в индекс" регистра сведений.

temsan · 12 мар 2013, 18:47

Результат оптимизации ("на бумаге")

Код Выделить

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

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

;

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

Что оптимизировано для СУБД SQL Server:
а) Подзапрос заменен временной таблицей
б) Добавлено соединение по Организации

Dunda · 12 мар 2013, 19:14

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

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

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

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

MrStomak · 12 мар 2013, 19:19

   ВЫБРАТЬ
    Договора.Ссылка
    Из Справочник.Договора Как Договора
    Поместить ФильтрДоговоров
   Где
    Договора.ДоговорСПоставщиком или Договора.ДоговорСПодрядчиком
   ИНДЕКСИРОВАТЬ ПО
   Договора.Ссылка
   ;

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

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

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

Предпринятые шаги:
1. Так как у нас идёт условие по договорам через разыменование и с использованием ИЛИ, системе не остаётся вариантов кроме как фулл скана таблицы поступлений, что нехорошо.
Если переписать условие на временную таблицу, то система сможет использовать поиск по индексу, если соответствующий реквизит ПТУ индексируется.
2. Вложенный запрос по РС ЛимитыКредиторскойЗадолженности переписан на временную таблицу, что само по себе облегчает задачу планировщику запросов.
3. Временная таблица ЛимитыКредиторскойЗадолженности фильтруется через внутреннее соединение по договорам. Это позволяет избавить её от лишних записей и сделает последующее соединение с ПТУ проще.
4. Временная таблица ЛимитыКредиторскойЗадолженности индексируется по полям соединения - если окажется, что документов ПТУ очень мало, то это увеличит скорость соединения

Данный запрос заточен под ситуацию, что документов ПТУ будет намного больше, чем записей в регистре по лимитам и договоров по поставщикам и подрядчикам. Если ситуация обратная, то временную таблицу регистра и договоров имеет смысл сразу фильтровать по контрагентам из поступлений.

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

Поиск