Оптимизация запроса

@SonicQ · Регистрация: 20.02.2012

Author24 — интернет-сервис помощи студентам

1С 8.2, конфигурация написана полностью с нуля. Есть запрос, который заполняет таблицу отчета по ДЗ и на данный момент отчет формируется 20 минут. Объем договоров более 100 000.

Помогите оптимизировать запрос.

1C

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

все параметры в массивах, причем в параметре

1C

1	СписокСписаний

более 60-ти записей

@Dethmontt · 18.07.2013, 14:22

Постарайся сократить количество точек в запросе!!!

Добавлено через 41 секунду
ВЫРАЗИТЬ(ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Справочник.ДоговорыКонтрагентов).Договор.Продукт.Код
ЭТО еще 2 запроса с левым соединением

@SonicQ · 18.07.2013, 14:28 **[ТС]**

без этого никак, так как после выполнения запроса выполняется такая конструкция

1C

    РезультатЗапросаПоОстаткам = ЗапросПолучитьДанные.Выполнить();
    Выборка = РезультатЗапросаПоОстаткам.Выгрузить();
    
    // Заполним таблицу значений данными из запроса
    ТаблЗнач = Новый таблицазначений;
    ТаблЗнач.Колонки.Добавить("Контрагент");
    ТаблЗнач.Колонки.Добавить("Договор");
    ТаблЗнач.Колонки.Добавить("Продукт");
    ТаблЗнач.Колонки.Добавить("Организация");
    ТаблЗнач.Колонки.Добавить("ВидДогСтрах");
    ТаблЗнач.Колонки.Добавить("СНД");
    ТаблЗнач.Колонки.Добавить("СНК");
    ТаблЗнач.Колонки.Добавить("ОБДОст");
    ТаблЗнач.Колонки.Добавить("ОБКОст");
    ТаблЗнач.Колонки.Добавить("СКД");
    ТаблЗнач.Колонки.Добавить("СКК");
    ТаблЗнач.Колонки.Добавить("ОБД");
    ТаблЗнач.Колонки.Добавить("ОБК");
    ТаблЗнач.Колонки.Добавить("ОснПосредник");
    ТаблЗнач.Колонки.Добавить("СчетОборота");
    ТаблЗнач.Колонки.Добавить("СчетОстатков");
    
    // заполняем ТЗ
    Для каждого СтрокаТз из Выборка Цикл
        НоваяСтрока = ТаблЗнач.Добавить();
        НоваяСтрока.Контрагент = СтрокаТз.Контрагент;
        НоваяСтрока.Договор = СтрокаТз.Договор;
        НоваяСтрока.Продукт = СтрокаТз.Продукт;
        НоваяСтрока.Организация = СтрокаТз.Организация;
        НоваяСтрока.ВидДогСтрах = СтрокаТз.ВидДогСтрах;
        НоваяСтрока.СНД = СтрокаТз.СНД;
        НоваяСтрока.СНК = СтрокаТз.СНК;
        НоваяСтрока.ОБДОст = СтрокаТз.ОБДОст;
        НоваяСтрока.ОБКОст = СтрокаТз.ОБКОст;
        НоваяСтрока.СКД = СтрокаТз.СКД;
        НоваяСтрока.СКК = СтрокаТз.СКК;
        НоваяСтрока.ОБД = СтрокаТз.ОБД;
        НоваяСтрока.ОБК = СтрокаТз.ОБК;
        НоваяСтрока.ОснПосредник = СтрокаТз.ОснПосредник;
        НоваяСтрока.СчетОборота = СтрокаТз.СчетОборота;
        НоваяСтрока.СчетОстатков = СтрокаТз.СчетОстатков;
        НоваяСтрока.ВидДогСтрах = СтрокаТз.ВидДогСтрах;
    КонецЦикла;
    
    // возвращаем таблицу заполненую данныим обратно в процедуру
    Возврат  ТаблЗнач;

А потом

1C

        // Созданные массивы отправляем как параметры в запрос
        ПолученныйМассивЗначений = СформироватьЗапрос(МассивСчетов5370,,СписокСписаний,,5370);      
        
        Рез5370Снд = 0;
        Рез5370Скд = 0;
        Рез5370обдОст = 0;
        Рез5370обд = 0;
        
        Для каждого Строка из ПолученныйМассивЗначений Цикл
                Рез5370Снд = Рез5370Снд + Строка.СНД;
                Рез5370Скд = Рез5370Скд + Строка.СКД;
                Если Строка.СНД>0 или Строка.СКД>0 Then
                Рез5370обдОст = Рез5370обдОст + Строка.ОБДОст;
                Рез5370обд = Рез5370обд + Строка.ОБД;
                EndIf;
        КонецЦикла;

Просто отчет сам по себе громосткий, и куча всяких проверок, причем необходимы именно ***.Договор.Продукт.Код

@Dethmontt · 18.07.2013, 14:40

Значит ты не хочешь оптимизировать запрос! иначе ты бы спросил КАК ЭТО СДЕЛАТЬ =))))

Добавлено через 19 секунд
А думать за тебя тут вряд ли кто хочет

@SonicQ · 18.07.2013, 14:44 **[ТС]**

КАК ЭТО СДЕЛАТЬ ??? ))))

Может есть какие-нибудь другие конструкции, при которых получаемые данные не изменятся

PS: Если б не хотел наверное не создавал бы темы))

@Dethmontt · 18.07.2013, 15:02

Сообщение от SonicQ

Может есть какие-нибудь другие конструкции, при которых получаемые данные не изменятся

Да. добавить временную таблицу куда выбрать все договора с нужными полями и потом соединить с основной таблицей по ссылке

Добавлено через 2 минуты
Это первое

Добавлено через 1 минуту
Второе:
Почему две таблицы? Обороты и ОстаткиИОбороты?

@SonicQ · 18.07.2013, 15:08 **[ТС]**

Сообщение от Dethmontt

куда выбрать все договора с нужными полями

и

Сообщение от Dethmontt

основной таблицей

огроменные таблицы (просто огромные), не думаю что время выполнения запроса сократится

@Dethmontt · 18.07.2013, 15:11

Лучше запрос выполнить один раз и соединить с таблицей чем выполнять его в цикле с условием

@SonicQ · 18.07.2013, 15:14 **[ТС]**

Сообщение от Dethmontt

Почему две таблицы? Обороты и ОстаткиИОбороты?

Потому что в одной можно использовать один набор параметров, по которым выбирается определенные договора и остатки для определенных столбцов в таблице, а вторая "Обороты" для того чтобы по Кор счетам выбрать определенные проводки по определенному договору при определенных условиях

Добавлено через 2 минуты
к сожалению без цикла думаю не обойтись, так как в отчете около 40-ка выводимых строк с 12-ю столбцами, причем в каждой ячейки разное условие)

@Dethmontt · 18.07.2013, 15:24

1C

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

Добавлено через 1 минуту
А в последней таблице делаешь соединение
ЛевоеСоединение ПО
ПоследняяТаблица.Договор = ТаблицаДоговоров.Ссылка

Добавлено через 3 минуты

Не по теме:

Выборка 100тыс элементов справочника отрабатывает меньше секунды

@SonicQ · 18.07.2013, 16:10 **[ТС]**

Окей, попробую. Спасибо)

Сообщение от Dethmontt

Не по теме:
Выборка 100тыс элементов справочника отрабатывает меньше секунды

не в моем случае, знал бы ты как реализованы договора тут)

@Dethmontt Модератор 3786 / 2965 / 590 Регистрация: 10.03.2011 Сообщений: 11,670 Записей в блоге: 1
	18.07.2013, 14:22	2
	Постарайся сократить количество точек в запросе!!! Добавлено через 41 секунду ВЫРАЗИТЬ(ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Справочник.ДоговорыКонтрагентов).Договор.Продукт.Код ЭТО еще 2 запроса с левым соединением 0

@Dethmontt Модератор 3786 / 2965 / 590 Регистрация: 10.03.2011 Сообщений: 11,670 Записей в блоге: 1
	18.07.2013, 14:40	4
	Значит ты не хочешь оптимизировать запрос! иначе ты бы спросил КАК ЭТО СДЕЛАТЬ =)))) Добавлено через 19 секунд А думать за тебя тут вряд ли кто хочет 0

@SonicQ 286 / 186 / 18 Регистрация: 20.02.2012 Сообщений: 925
	18.07.2013, 14:44 [ТС]	5
	КАК ЭТО СДЕЛАТЬ ??? )))) Может есть какие-нибудь другие конструкции, при которых получаемые данные не изменятся PS: Если б не хотел наверное не создавал бы темы)) 0

@Dethmontt Модератор 3786 / 2965 / 590 Регистрация: 10.03.2011 Сообщений: 11,670 Записей в блоге: 1
	18.07.2013, 15:02	6
	Сообщение от SonicQ Может есть какие-нибудь другие конструкции, при которых получаемые данные не изменятся Да. добавить временную таблицу куда выбрать все договора с нужными полями и потом соединить с основной таблицей по ссылке Добавлено через 2 минуты Это первое Добавлено через 1 минуту Второе: Почему две таблицы? Обороты и ОстаткиИОбороты? 0

@SonicQ 286 / 186 / 18 Регистрация: 20.02.2012 Сообщений: 925
	18.07.2013, 15:08 [ТС]	7
	Сообщение от Dethmontt куда выбрать все договора с нужными полями и Сообщение от Dethmontt основной таблицей огроменные таблицы (просто огромные), не думаю что время выполнения запроса сократится 0

@Dethmontt Модератор 3786 / 2965 / 590 Регистрация: 10.03.2011 Сообщений: 11,670 Записей в блоге: 1
	18.07.2013, 15:11	8
	Лучше запрос выполнить один раз и соединить с таблицей чем выполнять его в цикле с условием 0

@SonicQ 286 / 186 / 18 Регистрация: 20.02.2012 Сообщений: 925
	18.07.2013, 15:14 [ТС]	9
	Сообщение от Dethmontt Почему две таблицы? Обороты и ОстаткиИОбороты? Потому что в одной можно использовать один набор параметров, по которым выбирается определенные договора и остатки для определенных столбцов в таблице, а вторая "Обороты" для того чтобы по Кор счетам выбрать определенные проводки по определенному договору при определенных условиях Добавлено через 2 минуты к сожалению без цикла думаю не обойтись, так как в отчете около 40-ка выводимых строк с 12-ю столбцами, причем в каждой ячейки разное условие) 0

@SonicQ 286 / 186 / 18 Регистрация: 20.02.2012 Сообщений: 925
	18.07.2013, 16:10 [ТС]	11
	Окей, попробую. Спасибо) Сообщение от Dethmontt Не по теме: Выборка 100тыс элементов справочника отрабатывает меньше секунды не в моем случае, знал бы ты как реализованы договора тут) 0