8.х "ПостроительОтчета.Результат. Выгрузить()" - висяк минут на 15-20 !

Тема в разделе "Общие вопросы "1С:Предприятие 8"", создана пользователем duke55, 19 июл 2012.

  1. TopicStarter Overlay
    duke55
    Offline

    duke55

    Регистрация:
    20 июн 2011
    Сообщения:
    8
    Симпатии:
    0
    Баллы:
    1
    Уважаемые, может было у кого подобное...


    в конце июня обновили УПП (не типовая) до 1.3.26.1(21) ... Начались непонятные тормоза и глюки в базе... Проблем появилось много... одна из них примерно такая...

    на строчке:

    ....
    ТЗ=ПостроительОтчета.Результат. Выгрузить()
    ....

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

    натравил профайлер на данную процедуру ... в поле зрения попал один запрос, который выполняется по несколько десятков секунд и загрузка процессора прыгает высоко:


    exec sp_executesql N'SELECT
    CAST(SUM(T1.Fld23100TurnoverDt_) AS NUMERIC(33, 8)),
    T39._Fld19534RRef,
    T1.Period_
    FROM (SELECT
    T2.Period_ AS Period_,
    T2.Value1_TYPE AS Value1_TYPE,
    T2.Value1_RTRef AS Value1_RTRef,
    T2.Value1_RRRef AS Value1_RRRef,
    CASE WHEN CAST(SUM(T2.Fld23100TurnoverDt_) AS NUMERIC(27, 8)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld23100TurnoverDt_) AS NUMERIC(27, 8)) END AS Fld23100TurnoverDt_
    FROM (SELECT
    DATEADD(DAY,@P1 - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,T3._Period) AS NUMERIC(4)) - 1,DATEADD(YEAR,(CAST(DATEPART(YEAR,T3._Period) AS NUMERIC(4)) - 2000) - 2000,@P2))) AS Period_,
    T5._Value_TYPE AS Value1_TYPE,
    T5._Value_RTRef AS Value1_RTRef,
    T5._Value_RRRef AS Value1_RRRef,
    T3._Fld23100 AS Fld23100TurnoverDt_
    FROM _AccRg23097 T3 WITH(NOLOCK)
    LEFT OUTER JOIN _Acc18_ExtDim23008 T4 WITH(NOLOCK)
    ON T4._Acc18_IDRRef = T3._AccountDtRRef AND T4._LineNo = @P1
    LEFT OUTER JOIN _AccRgED23125 T5 WITH(NOLOCK)
    ON T5._RecorderTRef = T3._RecorderTRef AND T5._RecorderRRef = T3._RecorderRRef AND T5._LineNo = T3._LineNo AND T5._Period = T3._Period AND T5._Correspond = @P3 AND T5._KindRRef = T4._DimKindRRef
    LEFT OUTER JOIN _Acc18_ExtDim23008 T6 WITH(NOLOCK)
    ON T6._Acc18_IDRRef = T3._AccountDtRRef AND T6._LineNo = @P4
    LEFT OUTER JOIN _AccRgED23125 T7 WITH(NOLOCK)
    ON T7._RecorderTRef = T3._RecorderTRef AND T7._RecorderRRef = T3._RecorderRRef AND T7._LineNo = T3._LineNo AND T7._Period = T3._Period AND T7._Correspond = @P3 AND T7._KindRRef = T6._DimKindRRef
    LEFT OUTER JOIN _Acc18_ExtDim23008 T8 WITH(NOLOCK)
    ON T8._Acc18_IDRRef = T3._AccountDtRRef AND T8._LineNo = @P5
    LEFT OUTER JOIN _AccRgED23125 T9 WITH(NOLOCK)
    ON T9._RecorderTRef = T3._RecorderTRef AND T9._RecorderRRef = T3._RecorderRRef AND T9._LineNo = T3._LineNo AND T9._Period = T3._Period AND T9._Correspond = @P3
    AND T9._KindRRef = T8._DimKindRRef
    WHERE T3._Active = @P6 AND ((((((T3._AccountDtRRef IN
    (SELECT
    T10._REFFIELDRRef AS REFFIELDRRef
    FROM #tt18 T10 WITH(NOLOCK))) OR (T3._AccountDtRRef IN
    (SELECT
    T11._REFFIELDRRef AS REFFIELDRRef
    FROM #tt15 T11 WITH(NOLOCK)))) OR (T3._AccountDtRRef IN
    (SELECT
    T12._REFFIELDRRef AS REFFIELDRRef
    FROM #tt5 T12 WITH(NOLOCK)))) OR (T3._AccountDtRRef IN
    (SELECT
    T13._REFFIELDRRef AS REFFIELDRRef
    FROM #tt2 T13 WITH(NOLOCK)))) OR (T3._AccountDtRRef IN
    (SELECT
    T14._REFFIELDRRef AS REFFIELDRRef
    FROM #tt9 T14 WITH(NOLOCK))))) AND (((((((NOT (((T3._AccountCtRRef IN
    (SELECT
    T15._REFFIELDRRef AS REFFIELDRRef
    FROM #tt11 T15 WITH(NOLOCK)))))) AND (NOT (((T3._AccountCtRRef IN
    (SELECT
    T16._REFFIELDRRef AS REFFIELDRRef
    FROM #tt10 T16 WITH(NOLOCK))))))) AND (NOT (((T3._AccountCtRRef IN
    (SELECT
    T17._REFFIELDRRef AS REFFIELDRRef
    FROM #tt4 T17 WITH(NOLOCK))))))) AND (NOT (((T3._AccountCtRRef IN
    (SELECT
    T18._REFFIELDRRef AS REFFIELDRRef
    FROM #tt6 T18 WITH(NOLOCK))))))) AND (NOT (((T3._AccountCtRRef IN
    (SELECT
    T19._REFFIELDRRef AS REFFIELDRRef
    FROM #tt20 T19 WITH(NOLOCK))))))) AND (NOT (((T3._AccountCtRRef IN
    (SELECT
    T20._REFFIELDRRef AS REFFIELDRRef
    FROM #tt17 T20 WITH(NOLOCK)))))))) AND (CASE WHEN (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4) THEN CASE WHEN ((((T7._Value_TYPE = 0x08 AND
    T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x01 WHEN NOT ((((T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T7._Value_TYPE = 0x08 AND T7._Value_RTRef = 0x000000C4 AND T7._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x00 END ELSE CASE WHEN ((((T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x01 WHEN NOT ((((T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T9._Value_TYPE = 0x08 AND T9._Value_RTRef = 0x000000C4 AND T9._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x00 END END) = @P6 AND T3._Period >= @P7 AND T3._Period <= @P8
    UNION ALL SELECT
    DATEADD(DAY,@P1 - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,T21._Period) AS NUMERIC(4)) - 1,DATEADD(YEAR,(CAST(DATEPART(YEAR,T21._Period) AS NUMERIC(4)) - 2000) - 2000,@P2))) AS Period_,
    T23._Value_TYPE AS Value1_TYPE,
    T23._Value_RTRef AS Value1_RTRef,
    T23._Value_RRRef AS Value1_RRRef,
    CAST(@P3 AS NUMERIC(15, 2)) AS Fld23100TurnoverDt_
    FROM _AccRg23097 T21 WITH(NOLOCK)
    LEFT OUTER JOIN _Acc18_ExtDim23008 T22 WITH(NOLOCK)
    ON T22._Acc18_IDRRef = T21._AccountCtRRef AND T22._LineNo = @P1
    LEFT OUTER JOIN _AccRgED23125 T23 WITH(NOLOCK)
    ON T23._RecorderTRef = T21._RecorderTRef AND T23._RecorderRRef = T21._RecorderRRef AND T23._LineNo = T21._LineNo AND T23._Period = T21._Period AND T23._Correspond = @P1 AND T23._KindRRef = T22._DimKindRRef
    LEFT OUTER JOIN _Acc18_ExtDim23008 T24 WITH(NOLOCK)
    ON T24._Acc18_IDRRef = T21._AccountCtRRef AND T24._LineNo = @P4
    LEFT OUTER JOIN _AccRgED23125 T25 WITH(NOLOCK)
    ON T25._RecorderTRef = T21._RecorderTRef AND T25._RecorderRRef = T21._RecorderRRef AND T25._LineNo = T21._LineNo AND T25._Period = T21._Period AND T25._Correspond = @P1 AND T25._KindRRef = T24._DimKindRRef
    LEFT OUTER JOIN _Acc18_ExtDim23008 T26 WITH(NOLOCK)
    ON T26._Acc18_IDRRef = T21._AccountCtRRef AND T26._LineNo = @P5
    LEFT OUTER JOIN _AccRgED23125 T27 WITH(NOLOCK)
    ON T27._RecorderTRef = T21._RecorderTRef AND T27._RecorderRRef = T21._RecorderRRef AND T27._LineNo = T21._LineNo AND T27._Period = T21._Period AND T27._Correspond = @P1 AND T27._KindRRef = T26._DimKindRRef
    WHERE T21._Active = @P6 AND ((((((T21._AccountCtRRef IN
    (SELECT
    T28._REFFIELDRRef AS REFFIELDRRef
    FROM #tt18 T28 WITH(NOLOCK))) OR (T21._AccountCtRRef IN
    (SELECT
    T29._REFFIELDRRef AS REFFIELDRRef
    FROM #tt15 T29 WITH(NOLOCK)))) OR (T21._AccountCtRRef IN
    (SELECT
    T30._REFFIELDRRef AS REFFIELDRRef
    FROM #tt5 T30 WITH(NOLOCK)))) OR (T21._AccountCtRRef IN
    (SELECT
    T31._REFFIELDRRef AS REFFIELDRRef
    FROM #tt2 T31 WITH(NOLOCK)))) OR (T21._AccountCtRRef IN
    (SELECT
    T32._REFFIELDRRef AS REFFIELDRRef
    FROM #tt9 T32 WITH(NOLOCK))))) AND (((((((NOT (((T21._AccountDtRRef IN
    (SELECT
    T33._REFFIELDRRef AS REFFIELDRRef
    FROM #tt11 T33 WITH(NOLOCK)))))) AND (NOT (((T21._AccountDtRRef IN
    (SELECT
    T34._REFFIELDRRef AS REFFIELDRRef
    FROM #tt10 T34 WITH(NOLOCK))))))) AND (NOT (((T21._AccountDtRRef IN
    (SELECT
    T35._REFFIELDRRef AS REFFIELDRRef
    FROM #tt4 T35 WITH(NOLOCK))))))) AND (NOT (((T21._AccountDtRRef IN
    (SELECT
    T36._REFFIELDRRef AS REFFIELDRRef
    FROM #tt6 T36 WITH(NOLOCK))))))) AND (NOT (((T21._AccountDtRRef IN
    (SELECT
    T37._REFFIELDRRef AS REFFIELDRRef
    FROM #tt20 T37 WITH(NOLOCK))))))) AND (NOT (((T21._AccountDtRRef IN
    (SELECT
    T38._REFFIELDRRef AS REFFIELDRRef
    FROM #tt17 T38 WITH(NOLOCK)))))))) AND (CASE WHEN (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4) THEN CASE WHEN ((((T25._Value_TYPE = 0x08 AND
    T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND
    T25._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x01 WHEN NOT ((((T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T25._Value_TYPE = 0x08 AND T25._Value_RTRef = 0x000000C4 AND T25._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x00 END ELSE CASE WHEN ((((T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x01 WHEN NOT ((((T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA392C) OR (T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA392D)) OR (T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA39D5)) OR (T27._Value_TYPE = 0x08 AND T27._Value_RTRef = 0x000000C4 AND T27._Value_RRRef = 0x82D700164477450511DEBA520BBA39D6)) THEN 0x00 END END) = @P6 AND T21._Period >= @P7 AND T21._Period <= @P8) T2
    GROUP BY T2.Period_,
    T2.Value1_TYPE,
    T2.Value1_RTRef,
    T2.Value1_RRRef
    HAVING (CASE WHEN CAST(SUM(T2.Fld23100TurnoverDt_) AS NUMERIC(27, 8)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld23100TurnoverDt_) AS NUMERIC(27, 8)) END) <> @P3) T1
    LEFT OUTER JOIN _InfoRg19533 T39 WITH(NOLOCK)
    ON (T1.Value1_TYPE = CASE WHEN T39._Fld19536RRef IS NOT NULL THEN @P9 END AND T1.Value1_RTRef = CASE WHEN T39._Fld19536RRef IS NOT NULL THEN @P10 END AND T1.Value1_RRRef = T39._Fld19536RRef)
    GROUP BY T1.Period_,
    T39._Fld19534RRef', N'@P1 numeric(1,0),@P2 datetime,@P3 numeric(1,0),@P4 numeric(1,0),@P5 numeric(1,0),@P6 varbinary(1),@P7 datetime,@P8 datetime,@P9 varbinary(1),@P10 varbinary(4)', 1, {ts '4000-01-01 00:00:00'}, 0, 2, 3, 0x01, {ts '4012-06-01 00:00:00'}, {ts '4012-06-30 23:59:59'}, 0x08, 0x00000096


    ТиИ в базе не проходит.. .точнее проходит, но чтобы его пройти я думаю дня на 2 или на 3 надо сервер оставить в покое (за сутки не прошел) ... Если разоварачиваешь бэкап от начала июня, то все летает....


    Возможно ли чем-нибудь шагнуть в этот самый "ПостроительОтчета.Результат. Выгрузить() " и там трассировку сделать ???
  2. nbIpKuH_BaH9I
    Offline

    nbIpKuH_BaH9I Модераторы Команда форума Модератор

    Регистрация:
    16 сен 2009
    Сообщения:
    6.977
    Симпатии:
    397
    Баллы:
    104
    Могу лишь сказать, что у меня тоже проблем не мало с новым обновлением сильно измененной УПП. Отчет, который Вы выполняете свой? Или типовой?
  3. TopicStarter Overlay
    duke55
    Offline

    duke55

    Регистрация:
    20 июн 2011
    Сообщения:
    8
    Симпатии:
    0
    Баллы:
    1
    Вечером сделали выгрузку-загрузку базы. Вроде полегчало...
  4. x_under
    Offline

    x_under Модераторы Команда форума Модератор

    Регистрация:
    25 апр 2007
    Сообщения:
    2.576
    Симпатии:
    13
    Баллы:
    29
    А внешнее тестирование делали? Попробуйте еще его.

Поделиться этой страницей