Можно небольшой оффтопик? Специалисты по Ms SQL есть? На тестовой базе выполняю свертку чуточку доработанной БП 2.0. Обработка (честно, не знаю откуда, но скорее всего коллега давно взял с инфостарта - не помнит) явно переработанная от встроенной БП 1.6 для 2.0. Не в ней дело. Закрыл несколько месяцев. После этого ОСВ по счету за старые периоды (на дату свертки 01.04.2011 или в пределах 2011-2015 гг.) формируется очень долго. Если сформировать за текущий период 2017-2018 гг, то отчет формируется быстро - максимум секунд 5.. Что уже сделал:
Пересчитал итоги - несколько раз.
Сделал переиндексацию - средствами 1С - 2 раза. в SQL - много раз.
Делал truncate таблицы итогов, пересчет итогов и индексация. (нулевые записи в таблице пропали, хотя во время пересчета до этого оставались). Скорость не увеличилась.
В Active Monitor во время выполнения отчета вижу медленный запрос и его план. SQL говорит что нет индекса, хотя на другой копии и рабочей базе этот же отчет за любой период формируется быстро. Создал индекс руками , пересчитал - не помогло. Создал индекс скриптом, который SQL предлагает, пересчитал индекс - аналогично.
Честно говоря уже вообще идей никаких не осталось. Что еще можно сделать?
Пересчитал итоги - несколько раз.
Сделал переиндексацию - средствами 1С - 2 раза. в SQL - много раз.
Делал truncate таблицы итогов, пересчет итогов и индексация. (нулевые записи в таблице пропали, хотя во время пересчета до этого оставались). Скорость не увеличилась.
В Active Monitor во время выполнения отчета вижу медленный запрос и его план. SQL говорит что нет индекса, хотя на другой копии и рабочей базе этот же отчет за любой период формируется быстро. Создал индекс руками , пересчитал - не помогло. Создал индекс скриптом, который SQL предлагает, пересчитал индекс - аналогично.
Честно говоря уже вообще идей никаких не осталось. Что еще можно сделать?
SELECT
T1.AccountRRef,
T1.Fld489RRef,
T1.Fld492InitialBalanceDt_,
T1.Fld492InitialBalanceCt_,
T1.Fld492TurnoverDt_,
T1.Fld492TurnoverCt_,
T1.Fld492FinalBalanceDt_,
T1.Fld492FinalBalanceCt_,
T1.AccountRRef,
T9._Code,
T9._Kind,
T9._Fld480,
T9._OrderField
FROM (SELECT
T2.Fld489RRef AS Fld489RRef,
T2.AccountRRef AS AccountRRef,
CASE WHEN CAST(SUM(T2.Fld492TurnoverDt_) AS NUMERIC(21, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld492TurnoverDt_) AS NUMERIC(21, 2)) END AS Fld492TurnoverDt_,
CASE WHEN CAST(SUM(T2.Fld492TurnoverCt_) AS NUMERIC(21, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld492TurnoverCt_) AS NUMERIC(21, 2)) END AS Fld492TurnoverCt_,
CASE WHEN CAST(SUM(T2.Fld492Balance_) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T8._Kind) = 0.0 OR MAX(T8._Kind) = 2.0 AND CAST(SUM(T2.Fld492Balance_) AS NUMERIC(27, 2)) > 0.0 THEN CAST(SUM(T2.Fld492Balance_) AS NUMERIC(27, 2)) ELSE 0.0 END AS Fld492InitialBalanceDt_,
CASE WHEN CAST(SUM(T2.Fld492Balance_) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T8._Kind) = 1.0 OR MAX(T8._Kind) = 2.0 AND CAST(SUM(T2.Fld492Balance_) AS NUMERIC(27, 2)) < 0.0 THEN -CAST(SUM(T2.Fld492Balance_) AS NUMERIC(27, 2)) ELSE 0.0 END AS Fld492InitialBalanceCt_,
CASE WHEN CAST(SUM(T2.Fld492Balance_ + T2.Fld492FinalTurnover_) AS NUMERIC(28, 2)) IS NULL THEN 0.0 WHEN MAX(T8._Kind) = 0.0 OR MAX(T8._Kind) = 2.0 AND CAST(SUM(T2.Fld492Balance_ + T2.Fld492FinalTurnover_) AS NUMERIC(28, 2)) > 0.0 THEN CAST(SUM(T2.Fld492Balance_ + T2.Fld492FinalTurnover_) AS NUMERIC(28, 2)) ELSE 0.0 END AS Fld492FinalBalanceDt_,
CASE WHEN CAST(SUM(T2.Fld492Balance_ + T2.Fld492FinalTurnover_) AS NUMERIC(28, 2)) IS NULL THEN 0.0 WHEN MAX(T8._Kind) = 1.0 OR MAX(T8._Kind) = 2.0 AND CAST(SUM(T2.Fld492Balance_ + T2.Fld492FinalTurnover_) AS NUMERIC(28, 2)) < 0.0 THEN -CAST(SUM(T2.Fld492Balance_ + T2.Fld492FinalTurnover_) AS NUMERIC(28, 2)) ELSE 0.0 END AS Fld492FinalBalanceCt_,
MAX(T8._Kind) AS AccKind_
FROM (SELECT
T3._Fld489RRef AS Fld489RRef,
T3._AccountRRef AS AccountRRef,
T3._Fld492 AS Fld492Balance_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492FinalTurnover_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492TurnoverCt_
FROM dbo._AccRgAT0501 T3 WITH(NOLOCK)
WHERE T3._Period = @P1 AND (((T3._AccountRRef IN (@P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10)))) AND ((T3._Fld489RRef = @P11)) AND (T3._Fld492 <> @P12)
UNION ALL SELECT
T4._Fld489RRef AS Fld489RRef,
T4._AccountDtRRef AS AccountRRef,
CAST(-T4._Fld492 AS NUMERIC(21, 2)) AS Fld492Balance_,
T4._Fld492 AS Fld492FinalTurnover_,
T4._Fld492 AS Fld492TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492TurnoverCt_
FROM dbo._AccRg488 T4 WITH(NOLOCK)
WHERE T4._Active = 0x01 AND T4._AccountDtRRef <> @P13 AND (((T4._AccountDtRRef IN (@P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22)))) AND ((T4._Fld489RRef = @P23)) AND T4._Period >= @P24 AND T4._Period <= @P25
UNION ALL SELECT
T5._Fld489RRef AS Fld489RRef,
T5._AccountCtRRef AS AccountRRef,
CAST(T5._Fld492 AS NUMERIC(21, 2)) AS Fld492Balance_,
-T5._Fld492 AS Fld492FinalTurnover_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492TurnoverDt_,
T5._Fld492 AS Fld492TurnoverCt_
FROM dbo._AccRg488 T5 WITH(NOLOCK)
WHERE T5._Active = 0x01 AND T5._AccountCtRRef <> @P26 AND (((T5._AccountCtRRef IN (@P27, @P28, @P29, @P30, @P31, @P32, @P33, @P34, @P35)))) AND ((T5._Fld489RRef = @P36)) AND T5._Period >= @P37 AND T5._Period <= @P38
UNION ALL SELECT
T6._Fld489RRef AS Fld489RRef,
T6._AccountDtRRef AS AccountRRef,
CAST(-T6._Fld492 AS NUMERIC(21, 2)) AS Fld492Balance_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492FinalTurnover_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld492TurnoverCt_
FROM dbo._AccRg488 T6 WITH(NOLOCK)
WHERE T6._Active = 0x01 AND T6._AccountDtRRef <> @P39 AND (((T6._AccountDtRRef IN (@P40, @P41, @P42, @P43, @P44, @P45, @P46, @P47, @P48)))) AND ((T6._Fld489RRef = @P49)) AND T6._Period > @P50 AND T6._Period < @P51
UNION ALL SELECT
T7._Fld489RRef AS Fld489RRef,
T7._AccountCtRRef AS AccountRRef,
CAST(T7._Fld
Нашел.
При том, что я формирую ТОЛЬКО по одному счету (62) без субсчетов, без аналитики, без фильтров только по БУ.
Во. В компоновщике отчет уменьшился до правильного
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ОстаткиИОбороты.Счет КАК Счет,
ОстаткиИОбороты.Организация КАК Организация,
ОстаткиИОбороты.СуммаНачальныйОстатокДт КАК БУНачальныйОстатокДт,
ОстаткиИОбороты.СуммаНачальныйОстатокКт КАК БУНачальныйОстатокКт,
ОстаткиИОбороты.СуммаОборотДт КАК БУОборотДт,
ОстаткиИОбороты.СуммаОборотКт КАК БУОборотКт,
ОстаткиИОбороты.СуммаКонечныйОстатокДт КАК БУКонечныйОстатокДт,
ОстаткиИОбороты.СуммаКонечныйОстатокКт КАК БУКонечныйОстатокКт,
ПРЕДСТАВЛЕНИЕССЫЛКИ(ОстаткиИОбороты.Счет) КАК СчетПредставление,
ОстаткиИОбороты.Счет.Вид КАК СчетВид,
ОстаткиИОбороты.Счет.НалоговыйУчет КАК СчетНалоговыйУчет,
ОстаткиИОбороты.Счет.Порядок КАК СчетПорядок
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&П2, &П, Период, , Счет В ИЕРАРХИИ (&Счет), , Организация = &П3) КАК ОстаткиИОбороты
ГДЕ
ОстаткиИОбороты.Организация = &П3
@Kirill Если еще актуально. Нужен план запроса текстом весь или скрин той части где "cost" наибольшая. Предполагаю, что не обновлена статистика . Выполни последовательно
DBCC UPDATEUSAGE (БазаБухгалтерии) WITH NO_INFOMSGS; use БазаБухгалтерии
exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
@Berckk еще как актуально... Уже две недели бьюсь с этим. Статистику обновлял дважды вот этим скриптом https://infostart.ru/public/61071/
Вот это DBCC UPDATEUSAGE ([exp-test-3]) WITH NO_INFOMSGS; еще не пробовал.
Профайлером отловил нормальный запрос
SELECT
T1.AccountRRef,
T1.Fld489RRef,
T1.Fld492InitialBalanceDt_,
T1.Fld492InitialBalanceCt_,
T1.Fld492TurnoverDt_,
T1.Fld492TurnoverCt_,
T1.Fld492FinalBalanceDt_,
T1.Fld492FinalBalanceCt_,
T1.AccountRRef,
T4._Code,
T4._Kind,
T4._Fld480,
T4._OrderField
FROM (SELECT
T2._AccountRRef AS AccountRRef,
T2._Fld489RRef AS Fld489RRef,
CASE WHEN CAST(SUM(T2._TurnoverDt502) AS NUMERIC(27, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2._TurnoverDt502) AS NUMERIC(27, 2)) END AS Fld492TurnoverDt_,
CASE WHEN CAST(SUM(T2._TurnoverCt503) AS NUMERIC(27, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2._TurnoverCt503) AS NUMERIC(27, 2)) END AS Fld492TurnoverCt_,
CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 0.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) > 0.0 THEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) ELSE 0.0 END AS Fld492InitialBalanceDt_,
CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 1.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) < 0.0 THEN -(CAST(SUM(T2._Fld492) AS NUMERIC(27, 2))) ELSE 0.0 END AS Fld492InitialBalanceCt_,
CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 0.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2)) > 0.0 THEN CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2)) ELSE 0.0 END AS Fld492FinalBalanceDt_,
CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 1.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2)) < 0.0 THEN -(CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2))) ELSE 0.0 END AS Fld492FinalBalanceCt_,
MAX(T3._Kind) AS AccKind_
FROM dbo._AccRgAT0501 T2 WITH(NOLOCK)
INNER JOIN dbo._Acc19 T3 WITH(NOLOCK)
ON T3._IDRRef = T2._AccountRRef
WHERE T2._Period = @P1 AND (((T2._AccountRRef IN (@P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10)))) AND ((T2._Fld489RRef = @P11)) AND (T2._Fld492 <> @P12 OR T2._TurnoverDt502 <> @P13 OR T2._TurnoverCt503 <> @P14 OR T2._Turnover504 <> @P15)
GROUP BY T2._AccountRRef,
T2._Fld489RRef
HAVING (CASE WHEN CAST(SUM(T2._TurnoverDt502) AS NUMERIC(27, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2._TurnoverDt502) AS NUMERIC(27, 2)) END) <> 0.0 OR (CASE WHEN CAST(SUM(T2._TurnoverCt503) AS NUMERIC(27, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2._TurnoverCt503) AS NUMERIC(27, 2)) END) <> 0.0 OR (CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 0.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) > 0.0 THEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) ELSE 0.0 END) <> 0.0 OR (CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 1.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) < 0.0 THEN -(CAST(SUM(T2._Fld492) AS NUMERIC(27, 2))) ELSE 0.0 END) <> 0.0 OR (CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 0.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2)) > 0.0 THEN CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2)) ELSE 0.0 END) <> 0.0 OR (CASE WHEN CAST(SUM(T2._Fld492) AS NUMERIC(27, 2)) IS NULL THEN 0.0 WHEN MAX(T3._Kind) = 1.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2)) < 0.0 THEN -(CAST(SUM(T2._Fld492 + T2._Turnover504) AS NUMERIC(28, 2))) ELSE 0.0 END) <> 0.0) T1
LEFT OUTER JOIN dbo._Acc19 T4 WITH(NOLOCK)
ON T1.AccountRRef = T4._IDRRef
WHERE (T1.Fld489RRef = @P16)',N'@P1 datetime2(3),@P2 varbinary(16),@P3 varbinary(16),@P4 varbinary(16),@P5 varbinary(16),@P6 varbinary(16),@P7 varbinary(16),@P8 varbinary(16),@P9 varbinary(16),@P10 varbinary(16),@P11 varbinary(16),@P12 numeric(10),@P13 numeric(10),@P14 numeric(10),@P15 numeric(10),@P16 varbinary(16)','4011-04-01 00:00:00
гораздо компактнее и понятнее, не то, что в "кривой" копии
На инфостарте новая разработка по анализу плана запросов. Сам не пользовался, но консультировал автора
Советую взглянуть
Clustered Index Seek со стоимостью 48% - это скорее все большая выборка с отбором по дате в регистре AccRg488. По сути, это Table Scan с выбором дофига записей. Пересчет статистики не поможет
А итоги точно пересчитаны?
на другой копии и рабочей базе этот же отчет за любой период формируется быстро.
Что-то мне кажется, что беда именно в этом. Копии - это точно копии? Кажется, что они разные
По окружению исполнения
Может вам базу вынесли на мега тормозной диск?
Что-то мне кажется сбились какие-то данные, из-за которых теперь 1С некорректно строит SQL запрос
Стоит посмотреть что за таблица AccRg488
Т.к. в таблице _AccRgAT0501 нет данных за 2011-2015 годы то 1С берет эти данные из AccRg488
а потом итоги пересчитать
Большое всем спасибо. Павел, огромное спасибо за ссылку. Процесс расчета итогов еще не завершен, но уже пересчитаны итоги за проверяемый период - скорость формирования отчета - доли секунд. Если кому интересно - вот обработка - генерирует SQL скрипт для очистки таблиц. отдельно комментарием пишет таблицу хранения настроек итогов, в которой необходимо включить использование итогов. Теоретически можно написать команду обновления строки, но надо проверить на других базах поля и значения. А также рассчитывает поквартально итоги с 01.01.2010... Думаю можно это вынести в параметр.
