|
Анализ плана запроса | ☑ | ||
---|---|---|---|---|
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) срез последних может резко затормозить, если сдвинуть итоги далеко в прошлое.
|
Форум | Правила | Описание | Объявления | Секции | Поиск | Книга знаний | Вики-миста |