Имя: Пароль:
1C
1С v8
Анализ плана запроса
0 cons24
 
22.01.20
10:44
Добрый день.
Есть запрос который иногда почему-то выполняется оооочень долго.
1 cons24
 
22.01.20
10:44
Стек вызовов:

ОбщийМодуль.ДлительныеОперации.Модуль : 734 : ВыполнитьПроцедуру(ВсеПараметры.ИмяПроцедуры, ВсеПараметры.ПараметрыПроцедуры);
ОбщийМодуль.ДлительныеОперации.Модуль : 743 : ОбщегоНазначения.ВыполнитьМетодКонфигурации(ИмяПроцедуры, ПараметрыПроцедуры);
ОбщийМодуль.ОбщегоНазначения.Модуль : 4856 : Выполнить ИмяМетода + "(" + ПараметрыСтрока + ")";
  : 1 : БухгалтерскиеОтчеты.ОпределитьСостояниеЭтаповРасчетаЗакрытияМесяца(Параметры[0],Параметры[1])
  ОбщийМодуль.БухгалтерскиеОтчеты.Модуль : 1923 : Результат.СостоянияЭтапов = ЗакрытиеМесяцаСервер.ОпределитьСостояниеЭтаповРасчета(
  ОбщийМодуль.ЗакрытиеМесяцаСервер.Модуль : 11293 : Обработки.ОперацииЗакрытияМесяца.ЗаполнитьСостоянияЭтапов(ПараметрыЗапускаОбновленияСостояния);
  Обработка.ОперацииЗакрытияМесяца.МодульМенеджера : 526 : ПроверитьИспользованиеЭтапа(ПараметрыОбработчика);
   Обработка.ОперацииЗакрытияМесяца.МодульМенеджера : 779 : ОбщегоНазначения.ВыполнитьМетодКонфигурации(
   ОбщийМодуль.ОбщегоНазначения.Модуль : 4856 : Выполнить ИмяМетода + "(" + ПараметрыСтрока + ")";
    : 1 : ЗакрытиеМесяцаСервер.Использование_НастройкаПоказателейДляРаспределенияРасходовНаСебестоимостьПродукции(Параметры[0])
    ОбщийМодуль.ЗакрытиеМесяцаСервер.Модуль : 3435 : Документы.РаспределениеПрочихЗатрат.ИнициализироватьЗапросПолученияДанныхДляРаспределения(Запрос, ПараметрыЗапроса);
    Документ.РаспределениеПрочихЗатрат.МодульМенеджера : 2269 : ПодготовитьСлужебныеТаблицы(Запрос.Параметры.КонецПериода,
    Документ.РаспределениеПрочихЗатрат.МодульМенеджера : 3145 : НалоговыйУчет.ДоляТранспортныхРасходовТекущегоМесяца(Период, Организации, Запрос.МенеджерВременныхТаблиц);
     ОбщийМодуль.НалоговыйУчет.Модуль : 6408 : Запрос.Выполнить();
2 cons24
 
22.01.20
10:45
План запроса https://dropmefiles.com/5oluQ
3 Kuzmich123
 
22.01.20
10:45
Ушел за попкорном, чую, будет весело
4 cons24
 
22.01.20
10:45
Помогите проанализировать план запроса, что там есть плохого?
5 piter3
 
22.01.20
10:46
(2) ,Попробуй еще раз
6 Kuzmich123
 
22.01.20
10:54
(0) Да, кстати, а у тебя случаем не файловая база и 15.1830 платформа? А то тут вчера ругань стояла по поводу торможения платформы при запросах.
7 cons24
 
22.01.20
10:57
Платформа та, но sql. И тормоза начались еще до перехода на 15.1830.
8 cons24
 
22.01.20
10:58
(2) ссылка чего-то протухла
(5) https://dropmefiles.com/eiMdA
9 piter3
 
22.01.20
10:59
(8) Еще раз)
10 cons24
 
22.01.20
11:02
(8) да писец! что за обменник!
11 cons24
 
22.01.20
11:03
Дайте ссылку на рабочий обменник
12 hhhh
 
22.01.20
11:03
(7) ну это закрытие месяца, чего ты хотел? Оно по жизни должно медленно работать.
13 cons24
 
22.01.20
11:04
14 cons24
 
22.01.20
11:05
(12) оно зависает. По жизни не должно зависать.
Более того, выедает tempdb что диск переполняется. Год жили проблем не было и вот как всегда.
15 cons24
 
22.01.20
11:10
(8) ссылки на dropmefiles рабочие, просто сайт глючит. То выдает 404, то нормально открывает.
16 cons24
 
22.01.20
11:26
Але, знатоки, вы где?
17 hhhh
 
22.01.20
11:43
(14) раньше в своих показаниях вы указывали что выполняется долго. Про зависания ничего не писали. Вы определитесь, что у вас там происходит.
18 D_E_S_131
 
22.01.20
11:45
(16) А где сам запрос 1С-ный?
19 hhhh
 
22.01.20
11:46
(14) если зависает, то попробуйте ТИИ. А не план запроса.
20 Glup0sti
 
22.01.20
11:49
Ошибка оптимизатора вследствие неверной статистики строка 129 плана. Обнови статистику по таблице [ERP_TRADE].[dbo].[_AccumRg65591]
21 Glup0sti
 
22.01.20
11:54
Обнови статистику и сбрось кэш планов
22 H A D G E H O G s
 
22.01.20
12:04
(0) План запроса надо делать в xml форме. Текст твою прочитает только умудренный сединами какой-нибудь.
23 cons24
 
22.01.20
12:12
(18)
'INSERT INTO #tt190 WITH(TABLOCK) (_Q_001_F_000RRef, _Q_001_F_001, _Q_001_F_002, _Q_001_F_003) SELECT
T46._Q_000_F_001RRef,
CAST(SUM(T1.Q_001_F_001_) AS NUMERIC(38, 8)),
CAST(SUM(T1.Q_001_F_002_) AS NUMERIC(38, 8)),
CAST(SUM(T1.Q_001_F_003_) AS NUMERIC(38, 8))
FROM (SELECT
T2.Fld65104RRef AS Q_001_F_000RRef,
CASE WHEN T10._Q_001_F_001 = 0x01 THEN ((T2.Fld65111Balance_ - T2.Fld65112Balance_) - T2.Fld65113Balance_) ELSE T2.Fld65111Balance_ END AS Q_001_F_001_,
CAST(? AS NUMERIC(33, 2)) AS Q_001_F_002_,
CAST(? AS NUMERIC(38, 8)) AS Q_001_F_003_
FROM (SELECT
T3.Fld65104RRef AS Fld65104RRef,
CAST(SUM(T3.Fld65111Balance_) AS NUMERIC(33, 2)) AS Fld65111Balance_,
CAST(SUM(T3.Fld65113Balance_) AS NUMERIC(33, 2)) AS Fld65113Balance_,
CAST(SUM(T3.Fld65112Balance_) AS NUMERIC(33, 2)) AS Fld65112Balance_
FROM (SELECT
T4._Fld65104RRef AS Fld65104RRef,
CAST(SUM(T4._Fld65111) AS NUMERIC(27, 2)) AS Fld65111Balance_,
CAST(SUM(T4._Fld65113) AS NUMERIC(27, 2)) AS Fld65113Balance_,
CAST(SUM(T4._Fld65112) AS NUMERIC(27, 2)) AS Fld65112Balance_
FROM dbo._AccumRgT65135 T4
WHERE ((T4._Fld2226 = ?)) AND (T4._Period = ? AND (T4._Fld65104RRef IN
(SELECT
T5._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt37 T5 WITH(NOLOCK)) AND T4._Fld65107RRef IN
(SELECT
T6._Q_001_F_000RRef AS Q_001_F_000RRef
FROM #tt40 T6 WITH(NOLOCK))) AND (T4._Fld65111 <> ? OR T4._Fld65113 <> ? OR T4._Fld65112 <> ?) AND (T4._Fld65111 <> ? OR T4._Fld65113 <> ? OR T4._Fld65112 <> ?))
GROUP BY T4._Fld65104RRef
HAVING (CAST(SUM(T4._Fld65111) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T4._Fld65113) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T4._Fld65112) AS NUMERIC(27, 2))) <> 0.0
UNION ALL SELECT
T7._Fld65104RRef AS Fld65104RRef,
CAST(CAST(SUM(CASE WHEN T7._RecordKind = 0.0 THEN T7._Fld65111 ELSE -T7._Fld65111 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65111Balance_,
CAST(CAST(SUM(CASE WHEN T7._RecordKind = 0.0 THEN T7._Fld65113 ELSE -T7._Fld65113 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65113Balance_,
CAST(CAST(SUM(CASE WHEN T7._RecordKind = 0.0 THEN T7._Fld65112 ELSE -T7._Fld65112 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65112Balance_
FROM dbo._AccumRg65103 T7
WHERE ((T7._Fld2226 = ?)) AND (T7._Period >= ? AND T7._Period < ? AND T7._Active = 0x01 AND (T7._Fld65104RRef IN
(SELECT
T8._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt37 T8 WITH(NOLOCK)) AND T7._Fld65107RRef IN
(SELECT
T9._Q_001_F_000RRef AS Q_001_F_000RRef
FROM #tt40 T9 WITH(NOLOCK))))
GROUP BY T7._Fld65104RRef
HAVING (CAST(CAST(SUM(CASE WHEN T7._RecordKind = 0.0 THEN T7._Fld65111 ELSE -T7._Fld65111 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T7._RecordKind = 0.0 THEN T7._Fld65113 ELSE -T7._Fld65113 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T7._RecordKind = 0.0 THEN T7._Fld65112 ELSE -T7._Fld65112 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0) T3
GROUP BY T3.Fld65104RRef
HAVING (CAST(SUM(T3.Fld65111Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T3.Fld65113Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T3.Fld65112Balance_) AS NUMERIC(33, 2))) <> 0.0) T2
LEFT OUTER JOIN #tt186 T10 WITH(NOLOCK)
ON (T10._Q_001_F_000RRef = T2.Fld65104RRef)
UNION ALL SELECT
T11.Fld65104RRef AS Fld65104RRef,
CAST(CASE WHEN ISNULL(T15._Q_001_F_001,0x00) = 0x01 THEN ((T11.Fld65111Receipt_ - T11.Fld65112Receipt_) - T11.Fld65113Receipt_) ELSE T11.Fld65111Receipt_ END AS NUMERIC(35, 2)),
CAST(? AS NUMERIC(33, 2)),
CAST(? AS NUMERIC(38, 8))
FROM (SELECT
T12._Fld65104RRef AS Fld65104RRef,
CAST(SUM(CASE WHEN T12._RecordKind = 0.0 THEN T12._Fld65111 ELSE 0.0 END) AS NUMERIC(21, 2)) AS Fld65111Receipt_,
CAST(SUM(CASE WHEN T12._RecordKind = 0.0 THEN T12._Fld65113 ELSE 0.0 END) AS NUMERIC(21, 2)) AS Fld65113Receipt_,
CAST(SUM(CASE WHEN T12._RecordKind = 0.0 THEN T12._Fld65112 ELSE 0.0 END) AS NUMERIC(21, 2)) AS Fld65112Receipt_
FROM dbo._AccumRg65103 T12
WHERE ((T12._Fld2226 = ?)) AND (T12._Period >= ? AND T12._Period <= ? AND T12._Active = 0x01 AND (T12._Fld65104RRef IN
(SELECT
T13._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt37 T13 WITH(NOLOCK)) AND T12._Fld65107RRef IN
(SELECT
T14._Q_001_F_000RRef AS Q_001_F_000RRef
FROM #tt40 T14 WITH(NOLOCK))) AND T12._RecordKind = ?)
GROUP BY T12._Fld65104RRef
HAVING (CAST(SUM(CASE WHEN T12._RecordKind = 0.0 THEN T12._Fld65111 ELSE 0.0 END) AS NUMERIC(21, 2))) <> 0.0 OR (CAST(SUM(CASE WHEN T12._RecordKind = 0.0 THEN T12._Fld65113 ELSE 0.0 END) AS NUMERIC(21, 2))) <> 0.0 OR (CAST(SUM(CASE WHEN T12._RecordKind = 0.0 THEN T12._Fld65112 ELSE 0.0 END) AS NUMERIC(21, 2))) <> 0.0) T11
LEFT OUTER JOIN #tt186 T15 WITH(NOLOCK)
ON (T15._Q_001_F_000RRef = T11.Fld65104RRef)
UNION ALL SELECT
T19._Fld8597RRef AS Fld8597RRef,
CAST(? AS NUMERIC(35, 2)),
CASE WHEN ISNULL(T21._Q_001_F_001,0x00) = 0x01 THEN ((((((T16.Fld62153Turnover_ + T16.Fld62159Turnover_) + T16.Fld62163Turnover_) + T16.Fld62166Turnover_) + T16.Fld62170Turnover_) - T16.Fld62157Turnover_) - T16.Fld62171Turnover_) ELSE ((((T16.Fld62153Turnover_ + T16.Fld62159Turnover_) + T16.Fld62163Turnover_) + T16.Fld62166Turnover_) + T16.Fld62170Turnover_) END,
CAST(? AS NUMERIC(38, 8))
FROM (SELECT
T17._Fld62126RRef AS Fld62126RRef,
T17._Fld62128RRef AS Fld62128RRef,
CAST(SUM(T17._Fld62170) AS NUMERIC(27, 2)) AS Fld62170Turnover_,
CAST(SUM(T17._Fld62157) AS NUMERIC(27, 2)) AS Fld62157Turnover_,
CAST(SUM(T17._Fld62153) AS NUMERIC(27, 2)) AS Fld62153Turnover_,
CAST(SUM(T17._Fld62159) AS NUMERIC(27, 2)) AS Fld62159Turnover_,
CAST(SUM(T17._Fld62163) AS NUMERIC(27, 2)) AS Fld62163Turnover_,
CAST(SUM(T17._Fld62171) AS NUMERIC(27, 2)) AS Fld62171Turnover_,
CAST(SUM(T17._Fld62166) AS NUMERIC(27, 2)) AS Fld62166Turnover_
FROM dbo._AccumRgTn62191 T17
WHERE ((T17._Fld2226 = ?)) AND (T17._Period >= ? AND T17._Period < ? AND (T17._Fld62132RRef IN
(SELECT
T18._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt34 T18 WITH(NOLOCK))) AND (T17._Fld62170 <> ? OR T17._Fld62157 <> ? OR T17._Fld62153 <> ? OR T17._Fld62159 <> ? OR T17._Fld62163 <> ? OR T17._Fld62171 <> ? OR T17._Fld62166 <> ?))
GROUP BY T17._Fld62126RRef,
T17._Fld62128RRef
HAVING (CAST(SUM(T17._Fld62170) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T17._Fld62157) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T17._Fld62153) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T17._Fld62159) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T17._Fld62163) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T17._Fld62171) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T17._Fld62166) AS NUMERIC(27, 2))) <> 0.0) T16
LEFT OUTER JOIN dbo._Reference255 T19
ON (T16.Fld62128RRef = T19._IDRRef) AND (T19._Fld2226 = ?)
INNER JOIN #tt189 T20 WITH(NOLOCK)
ON (T16.Fld62126RRef = T20._Q_001_F_000RRef) AND (T19._Fld8597RRef = T20._Q_001_F_002RRef)
LEFT OUTER JOIN #tt186 T21 WITH(NOLOCK)
ON (T21._Q_001_F_000RRef = T19._Fld8597RRef)
UNION ALL SELECT
T25._Fld8597RRef AS Fld8597RRef,
CAST(? AS NUMERIC(35, 2)),
CASE WHEN ISNULL(T27._Q_001_F_001,0x00) = 0x01 THEN ((((((T22.Fld62153Turnover_ + T22.Fld62159Turnover_) + T22.Fld62163Turnover_) + T22.Fld62166Turnover_) + T22.Fld62170Turnover_) - T22.Fld62157Turnover_) - T22.Fld62171Turnover_) ELSE ((((T22.Fld62153Turnover_ + T22.Fld62159Turnover_) + T22.Fld62163Turnover_) + T22.Fld62166Turnover_) + T22.Fld62170Turnover_) END,
CAST(? AS NUMERIC(38, 8))
FROM (SELECT
T23._Fld62126RRef AS Fld62126RRef,
T23._Fld62128RRef AS Fld62128RRef,
T23._Fld62131RRef AS Fld62131RRef,
CAST(SUM(T23._Fld62170) AS NUMERIC(27, 2)) AS Fld62170Turnover_,
CAST(SUM(T23._Fld62157) AS NUMERIC(27, 2)) AS Fld62157Turnover_,
CAST(SUM(T23._Fld62153) AS NUMERIC(27, 2)) AS Fld62153Turnover_,
CAST(SUM(T23._Fld62159) AS NUMERIC(27, 2)) AS Fld62159Turnover_,
CAST(SUM(T23._Fld62163) AS NUMERIC(27, 2)) AS Fld62163Turnover_,
CAST(SUM(T23._Fld62171) AS NUMERIC(27, 2)) AS Fld62171Turnover_,
CAST(SUM(T23._Fld62166) AS NUMERIC(27, 2)) AS Fld62166Turnover_
FROM dbo._AccumRgTn62191 T23
WHERE ((T23._Fld2226 = ?)) AND (T23._Period >= ? AND T23._Period < ? AND (1=0 AND T23._Fld62132RRef IN
(SELECT
T24._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt34 T24 WITH(NOLOCK))) AND (T23._Fld62170 <> ? OR T23._Fld62157 <> ? OR T23._Fld62153 <> ? OR T23._Fld62159 <> ? OR T23._Fld62163 <> ? OR T23._Fld62171 <> ? OR T23._Fld62166 <> ?))
GROUP BY T23._Fld62126RRef,
T23._Fld62128RRef,
T23._Fld62131RRef
HAVING (CAST(SUM(T23._Fld62170) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T23._Fld62157) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T23._Fld62153) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T23._Fld62159) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T23._Fld62163) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T23._Fld62171) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T23._Fld62166) AS NUMERIC(27, 2))) <> 0.0) T22
LEFT OUTER JOIN dbo._Reference255 T25
ON (T22.Fld62128RRef = T25._IDRRef) AND (T25._Fld2226 = ?)
INNER JOIN #tt189 T26 WITH(NOLOCK)
ON (T22.Fld62126RRef = T26._Q_001_F_000RRef) AND (T25._Fld8597RRef = T26._Q_001_F_002RRef) AND (T22.Fld62131RRef = T26._Q_001_F_001RRef)
LEFT OUTER JOIN #tt186 T27 WITH(NOLOCK)
ON (T27._Q_001_F_000RRef = T25._Fld8597RRef)
UNION ALL SELECT
T28.Fld65595RRef AS Fld65595RRef,
CAST(? AS NUMERIC(35, 2)),
CAST(? AS NUMERIC(33, 2)),
CASE WHEN ISNULL(T36._Q_001_F_001,0x00) = 0x01 THEN ((((((T28.Fld65611Balance_ - T28.Fld65617Balance_) - T28.Fld65618Balance_) + T28.Fld65613Balance_) + T28.Fld65615Balance_) + T28.Fld65614Balance_) + T28.Fld65616Balance_) ELSE ((((T28.Fld65611Balance_ + T28.Fld65613Balance_) + T28.Fld65615Balance_) + T28.Fld65614Balance_) + T28.Fld65616Balance_) END
FROM (SELECT
T29.Fld65595RRef AS Fld65595RRef,
CAST(SUM(T29.Fld65615Balance_) AS NUMERIC(33, 2)) AS Fld65615Balance_,
CAST(SUM(T29.Fld65613Balance_) AS NUMERIC(33, 2)) AS Fld65613Balance_,
CAST(SUM(T29.Fld65614Balance_) AS NUMERIC(33, 2)) AS Fld65614Balance_,
CAST(SUM(T29.Fld65616Balance_) AS NUMERIC(33, 2)) AS Fld65616Balance_,
CAST(SUM(T29.Fld65611Balance_) AS NUMERIC(33, 2)) AS Fld65611Balance_,
CAST(SUM(T29.Fld65617Balance_) AS NUMERIC(33, 2)) AS Fld65617Balance_,
CAST(SUM(T29.Fld65618Balance_) AS NUMERIC(33, 2)) AS Fld65618Balance_
FROM (SELECT
T30._Fld65595RRef AS Fld65595RRef,
CAST(SUM(T30._Fld65615) AS NUMERIC(27, 2)) AS Fld65615Balance_,
CAST(SUM(T30._Fld65613) AS NUMERIC(27, 2)) AS Fld65613Balance_,
CAST(SUM(T30._Fld65614) AS NUMERIC(27, 2)) AS Fld65614Balance_,
CAST(SUM(T30._Fld65616) AS NUMERIC(27, 2)) AS Fld65616Balance_,
CAST(SUM(T30._Fld65611) AS NUMERIC(27, 2)) AS Fld65611Balance_,
CAST(SUM(T30._Fld65617) AS NUMERIC(27, 2)) AS Fld65617Balance_,
CAST(SUM(T30._Fld65618) AS NUMERIC(27, 2)) AS Fld65618Balance_
FROM dbo._AccumRgT65666 T30
WHERE ((T30._Fld2226 = ?)) AND (T30._Period = ? AND (T30._Fld65593RRef IN
(SELECT
T31._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt34 T31 WITH(NOLOCK)) AND EXISTS(SELECT
1
FROM #tt189 T32 WITH(NOLOCK)
WHERE (T30._Fld65592RRef = T32._Q_001_F_000RRef) AND (T30._Fld65595RRef = T32._Q_001_F_002RRef))) AND (T30._Fld65615 <> ? OR T30._Fld65613 <> ? OR T30._Fld65614 <> ? OR T30._Fld65616 <> ? OR T30._Fld65611 <> ? OR T30._Fld65617 <> ? OR T30._Fld65618 <> ?) AND (T30._Fld65615 <> ? OR T30._Fld65613 <> ? OR T30._Fld65614 <> ? OR T30._Fld65616 <> ? OR T30._Fld65611 <> ? OR T30._Fld65617 <> ? OR T30._Fld65618 <> ?))
GROUP BY T30._Fld65595RRef
HAVING (CAST(SUM(T30._Fld65615) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T30._Fld65613) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T30._Fld65614) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T30._Fld65616) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T30._Fld65611) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T30._Fld65617) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T30._Fld65618) AS NUMERIC(27, 2))) <> 0.0
UNION ALL SELECT
T33._Fld65595RRef AS Fld65595RRef,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65615 ELSE -T33._Fld65615 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65615Balance_,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65613 ELSE -T33._Fld65613 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65613Balance_,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65614 ELSE -T33._Fld65614 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65614Balance_,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65616 ELSE -T33._Fld65616 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65616Balance_,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65611 ELSE -T33._Fld65611 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65611Balance_,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65617 ELSE -T33._Fld65617 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65617Balance_,
CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65618 ELSE -T33._Fld65618 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65618Balance_
FROM dbo._AccumRg65591 T33
WHERE ((T33._Fld2226 = ?)) AND (T33._Period >= ? AND T33._Period <= ? AND T33._Active = 0x01 AND (T33._Fld65593RRef IN
(SELECT
T34._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt34 T34 WITH(NOLOCK)) AND EXISTS(SELECT
1
FROM #tt189 T35 WITH(NOLOCK)
WHERE (T33._Fld65592RRef = T35._Q_001_F_000RRef) AND (T33._Fld65595RRef = T35._Q_001_F_002RRef))))
GROUP BY T33._Fld65595RRef
HAVING (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65615 ELSE -T33._Fld65615 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65613 ELSE -T33._Fld65613 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65614 ELSE -T33._Fld65614 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65616 ELSE -T33._Fld65616 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65611 ELSE -T33._Fld65611 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65617 ELSE -T33._Fld65617 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T33._RecordKind = 0.0 THEN T33._Fld65618 ELSE -T33._Fld65618 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0) T29
GROUP BY T29.Fld65595RRef
HAVING (CAST(SUM(T29.Fld65615Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T29.Fld65613Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T29.Fld65614Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T29.Fld65616Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T29.Fld65611Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T29.Fld65617Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T29.Fld65618Balance_) AS NUMERIC(33, 2))) <> 0.0) T28
LEFT OUTER JOIN #tt186 T36 WITH(NOLOCK)
ON (T36._Q_001_F_000RRef = T28.Fld65595RRef)
WHERE 1=1
UNION ALL SELECT
T37.Fld65595RRef AS Fld65595RRef,
CAST(? AS NUMERIC(35, 2)),
CAST(? AS NUMERIC(33, 2)),
CASE WHEN ISNULL(T45._Q_001_F_001,0x00) = 0x01 THEN ((((((T37.Fld65611Balance_ - T37.Fld65617Balance_) - T37.Fld65618Balance_) + T37.Fld65613Balance_) + T37.Fld65615Balance_) + T37.Fld65614Balance_) + T37.Fld65616Balance_) ELSE ((((T37.Fld65611Balance_ + T37.Fld65613Balance_) + T37.Fld65615Balance_) + T37.Fld65614Balance_) + T37.Fld65616Balance_) END
FROM (SELECT
T38.Fld65595RRef AS Fld65595RRef,
CAST(SUM(T38.Fld65615Balance_) AS NUMERIC(33, 2)) AS Fld65615Balance_,
CAST(SUM(T38.Fld65613Balance_) AS NUMERIC(33, 2)) AS Fld65613Balance_,
CAST(SUM(T38.Fld65614Balance_) AS NUMERIC(33, 2)) AS Fld65614Balance_,
CAST(SUM(T38.Fld65616Balance_) AS NUMERIC(33, 2)) AS Fld65616Balance_,
CAST(SUM(T38.Fld65611Balance_) AS NUMERIC(33, 2)) AS Fld65611Balance_,
CAST(SUM(T38.Fld65617Balance_) AS NUMERIC(33, 2)) AS Fld65617Balance_,
CAST(SUM(T38.Fld65618Balance_) AS NUMERIC(33, 2)) AS Fld65618Balance_
FROM (SELECT
T39._Fld65595RRef AS Fld65595RRef,
CAST(SUM(T39._Fld65615) AS NUMERIC(27, 2)) AS Fld65615Balance_,
CAST(SUM(T39._Fld65613) AS NUMERIC(27, 2)) AS Fld65613Balance_,
CAST(SUM(T39._Fld65614) AS NUMERIC(27, 2)) AS Fld65614Balance_,
CAST(SUM(T39._Fld65616) AS NUMERIC(27, 2)) AS Fld65616Balance_,
CAST(SUM(T39._Fld65611) AS NUMERIC(27, 2)) AS Fld65611Balance_,
CAST(SUM(T39._Fld65617) AS NUMERIC(27, 2)) AS Fld65617Balance_,
CAST(SUM(T39._Fld65618) AS NUMERIC(27, 2)) AS Fld65618Balance_
FROM dbo._AccumRgT65666 T39
WHERE ((T39._Fld2226 = ?)) AND (T39._Period = ? AND (T39._Fld65593RRef IN
(SELECT
T40._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt34 T40 WITH(NOLOCK)) AND EXISTS(SELECT
1
FROM #tt189 T41 WITH(NOLOCK)
WHERE (T39._Fld65594RRef = T41._Q_001_F_001RRef) AND (T39._Fld65592RRef = T41._Q_001_F_000RRef) AND (T39._Fld65595RRef = T41._Q_001_F_002RRef))) AND (T39._Fld65615 <> ? OR T39._Fld65613 <> ? OR T39._Fld65614 <> ? OR T39._Fld65616 <> ? OR T39._Fld65611 <> ? OR T39._Fld65617 <> ? OR T39._Fld65618 <> ?) AND (T39._Fld65615 <> ? OR T39._Fld65613 <> ? OR T39._Fld65614 <> ? OR T39._Fld65616 <> ? OR T39._Fld65611 <> ? OR T39._Fld65617 <> ? OR T39._Fld65618 <> ?))
GROUP BY T39._Fld65595RRef
HAVING (CAST(SUM(T39._Fld65615) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T39._Fld65613) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T39._Fld65614) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T39._Fld65616) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T39._Fld65611) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T39._Fld65617) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(SUM(T39._Fld65618) AS NUMERIC(27, 2))) <> 0.0
UNION ALL SELECT
T42._Fld65595RRef AS Fld65595RRef,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65615 ELSE -T42._Fld65615 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65615Balance_,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65613 ELSE -T42._Fld65613 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65613Balance_,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65614 ELSE -T42._Fld65614 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65614Balance_,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65616 ELSE -T42._Fld65616 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65616Balance_,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65611 ELSE -T42._Fld65611 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65611Balance_,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65617 ELSE -T42._Fld65617 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65617Balance_,
CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65618 ELSE -T42._Fld65618 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2)) AS Fld65618Balance_
FROM dbo._AccumRg65591 T42
WHERE ((T42._Fld2226 = ?)) AND (T42._Period >= ? AND T42._Period <= ? AND T42._Active = 0x01 AND (T42._Fld65593RRef IN
(SELECT
T43._Q_000_F_000RRef AS Q_001_F_000RRef
FROM #tt34 T43 WITH(NOLOCK)) AND EXISTS(SELECT
1
FROM #tt189 T44 WITH(NOLOCK)
WHERE (T42._Fld65594RRef = T44._Q_001_F_001RRef) AND (T42._Fld65592RRef = T44._Q_001_F_000RRef) AND (T42._Fld65595RRef = T44._Q_001_F_002RRef))))
GROUP BY T42._Fld65595RRef
HAVING (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65615 ELSE -T42._Fld65615 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65613 ELSE -T42._Fld65613 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65614 ELSE -T42._Fld65614 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65616 ELSE -T42._Fld65616 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65611 ELSE -T42._Fld65611 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65617 ELSE -T42._Fld65617 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0 OR (CAST(CAST(SUM(CASE WHEN T42._RecordKind = 0.0 THEN T42._Fld65618 ELSE -T42._Fld65618 END) AS NUMERIC(21, 2)) AS NUMERIC(27, 2))) <> 0.0) T38
GROUP BY T38.Fld65595RRef
HAVING (CAST(SUM(T38.Fld65615Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T38.Fld65613Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T38.Fld65614Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T38.Fld65616Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T38.Fld65611Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T38.Fld65617Balance_) AS NUMERIC(33, 2))) <> 0.0 OR (CAST(SUM(T38.Fld65618Balance_) AS NUMERIC(33, 2))) <> 0.0) T37
LEFT OUTER JOIN #tt186 T45 WITH(NOLOCK)
ON (T45._Q_001_F_000RRef = T37.Fld65595RRef)
WHERE 1=0) T1
INNER JOIN #tt37 T46 WITH(NOLOCK)
ON (T1.Q_001_F_000RRef = T46._Q_000_F_000RRef)
GROUP BY T46._Q_000_F_001RRef
24 cons24
 
22.01.20
12:12
(22) тексты из сервиса Гилева, там выловил
25 H A D G E H O G s
 
22.01.20
12:13
(24) Возьмите SQL profiler
26 D_E_S_131
 
22.01.20
13:03
(23) 1С-ный запрос, а не галиматья из профилера. :)
27 nicxxx
 
22.01.20
13:30
(23) срез последних может резко затормозить, если сдвинуть итоги далеко в прошлое.