А баловался ли кто-нибудь? Есть регистр накопления, остатки. Секционировал по «_Period» таблицу итогов, по годам. Когда пишу 1Совый запрос:

РегистрНакопления.МойРегистр.Остатки(, [Отбор по одному полю в 50 тысяч строк])
1С это превращает в:

exec sp_executesql('.....
....
FROM dbo._AccumRgT27531 T2 WITH(NOLOCK)
WHERE T2._Period = P1
.....' P1 datetime2(3).... '5999-11-01 00:00:00'

В итоге SQL, из за sp_executesql и не явного указания периодов, не понимает что надо поднимать только одну секцию с периодом '5999-11-01 00:00:00' и поднимает все секции. Хотя если в менеджмент студии переписать запрос на:
…
FROM dbo._AccumRgT27531 T2 WITH(NOLOCK)
WHERE T2._Period = '5999-11-01 00:00:00'
….

Всё работает! Разница в скорости колоссальная – 1 секунда против 3 минут.

Решение есть - отказаться от запросов 1С против прямых запросов, но уж больно не хочется этого делать. Есть идеи как можно повлиять на
РегистрНакопления.МойРегистр.Остатки ?

правильно спроектировать регистр....

я не понимаю как можно угробить регистр всего в 50тыс строк что-бы он запрос делал 3 минуты....

(0) индекс по полю есть? 3 минуты что-то многовато для 50 тысяч строк

(1) 50 тыщ строк в отборе. а не в таблице

(0) Разделители данных используются?

(3) для этого делается ВТ и условие ... В(выбрать Т.* из ВТ1 как Т).

работает на ура...

(1), (2) В регистре сотни миллионов записей. Конкретно в таблице итогов около 300 миллионов. ОТБОР  по 50 тысячам.
(2) Индекс по полю есть. Запрос попадает в кластерный индекс(сиик).
(4) не используются разделители.
(5) Именно так. Формируется выборка во времянку, индексируется. В отборе виртуальной таблицы указывается "ПолеОтбора В (Выбрать Ссыла ИЗ ВТ)".

300 млн. в итогах - это конечно, эпик, даже если дело ведётся с бородатых лет, но я все равно не понял, что хотел сказать автор

(0)  Герман Кудяков делал продукт на движке 1с, в котором был механизм секционирования СУБД MS SQL.
PS Нужно помнить, что при реструктуризации регистра все придется делать заново.

Вангую итоги от рождества Христова

(6) "Конкретно в таблице итогов около 300 миллионов" - жуть какая. Это только в таблице оперативных итогов _AccumRgT27531 ???
Из интереса: сколько последних месяцев хранится? Там примерно по столько же?

Чем отличается запрос 1с от запроса автора?

(6) так может в таблице итогов много нулевых итогов?
В 8.3 можно управлять период рассчитанных итогов, например с 1.1.2010 по 30.04.2018 (до 8.3 нельзя было управлять датой начала расчета итогов)

(6) а кто будет указывать первый параметр виртуальной таблицы?


РегистрНакопления.МойРегистр.Остатки(???, [Отбор])

(7) В курсе что такое секционирование и как это работает?
(8) Спасибо за наводку, пояндексю.
(9) Всего лишь с 2010 года.
(10) AccumRgT27531 это таблица всех итогов, за все года и месяцы. Оперативные итоги, это итоги у которых _Period = '5999-11-01 00:00:00'.
(11) Это один и тот-же кусок запроса, пойманный профайлером. Разница в том, что типовой 1Совый запрос получает условие по дате как параметр, а я устанавливаю условие как константу.

(13) имеются ввиду текущие остатки, для них период указывать не нужно

(14) какая версия СУБД и 1с?

(12) скорее много не нулевых итогов. С этим боремся, но тут помощь не нужна, вопрос не в этом.
(12) >> В 8.3 можно управлять период рассчитанных итогов, например с 1.1.2010 по 30.04.2018
Гм... А можно поподробнее? Где почитать? Я бы с удовольствием удалил итоги за 2010-2016 годы.
(13) Хочется оперативных, текущих остатков. Отбор по периоду опущен специально.
(15) Именно!
(16) SQL Server 2012 SP4. 1С, древняя ещё толстоформенная КА на 8.3.10.2561 релизе.

(15) кто сказал, что не нужно?

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

ихмается мне что дело тут в секционности индекса, то есть в джойне нужно использовать поля соответсвующие основному индексу (при чем в правильном порядке), возможно во ВТ следует добавить несколько полей и подумать над соединением с физ таблицей...

(17) >>>Хочется оперативных, текущих остатков. Отбор по периоду опущен специально.

попробуй период поставить такой (граница(конецмесяца(текущаядата())),истина)

(19) ты действительно не понимаешь какой запрос на сервер уходит и почему?

(17) РегистрНакопленияМенеджер.УстановитьМинимальныйИМаксимальныйПериодыРассчитанныхИтогов
и еще пара методов для работы с этой возможностью.

(18) Я говорю. Если не указывать, либо в параметр периода передать пустую дату, 1С интерпретирует это как запрос к оперативным итогам. Т.е. запрос будет ТОЛЬКО к таблице итогов и ТОЛЬКО к данным где _Period = '5999-11-01 00:00:00'. В случае указания любого периода, если не будет посчитанных данных на конец месяца, 1С будет строить запрос и к итогам и к движениям. Т.е. лучше не будет точно.

(17) может проблема в версии скуля?
Почему отличается поведение по сути одинаковых запросов?

(0) те при запросе к остаткам регистра ты получаешь идентичный запрос как если -бы ты сам его делал через студию. Запросы отличаются только в отборе по периоду.

Опять же, если
"Разница в скорости колоссальная – 1 секунда против 3 минут. "

То не вижу ничего предосудительного в использовании прямого запроса

(25) а я вижу, как ты думаешь, тому кто придет на смену с этим всем разбираться?

(26) судя по размеру базы абы кто туда не придет

(21) Гляну, спасибо.
(23) Как я понял по комментариям чистых сиквельщиков, дело в неопрееленности периода. Т.к. запрос не прямой, а  через sp_executesql, отимизатор компилит его на "максимальное покрытие"
(24) Не совсем. Я ловлю запрос, копирую его в менеджмент студию, выполняю, вижу что в плане поднято 14 секций вместо одной. Чуть-чуть модифицирую - указываю дату константой а не параметром, получается тот-же запрос, но поднимающий 1 секцию и работающий на порядки быстрее.
(25) Я всё больше склоняюсь к этому.
(26) Меньше всего меня заботит на тему, что кто-то там придёт. Во первых я сам пока никуда не собираюсь. Во вторых мне зарплату платят за то, чтобы система работала. И работала в устраивающих меня и бизнес границах.

(21) + (28) Беда-печаль. Семейство методов РегистрНакопленияМенеджер.УстановитьМинимальныйИМаксимальныйПериодыРассчитанныхИтогов работают с 8.3.2, а у меня стоит режим совместимости 8.2.16. А если просто удалить записи итогов, где период меньше, например, 01.01.2017?

И кстати об удалениях, кто-нибудь пробовал чистить периодический регистр сведений методом удаления записей SQL-ем? Вот таким способом:
delete from ТаблицаРегистраСведений where _Period < '2017-01-01'
Натыкались на какие-то последствия?

А пробывал без регистра остатков?

Иногда на больших выходит быстрее

(30) в непериодический регистр сведений писать и через group by итоги считать? Или как?

(31) да

Запрос проще и оптимизатору сложнее ошибиться

В таблице движений х2 количество записей от итогов. Шансов попасть в индекс столько же, как и в секцию по итогам. Тогда уж на прямые запросы переходить, проблем имхо, будет меньше.

(30)
Сурово

(34) а нафиг тебе индекс то?
он по большому счету не используется если есть конструкт "В"
Там почти всегда используется фулскан.

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

(0) пробовал изменить запрос на ЛЕВОЕ соединение с "отборными" полями с последующим ГДЕ ЕСТЬ NULL ?

(38) зачем?
основной запрос вообще не должен ничего включать кроме примитивного фильтра и группировки

(29) тогда лучше всего посмотреть как работает секционирование на новой чистой базе 1с на SQL 2014\2016.
Где база 1с без режима совместимости и база СУБД тоже без режима совместимости.

(29) прямым запросом все чистится без проблем. нужно только помнить что в 8.3 у такого регистра появились таблица итогов (текущий срез данных). и в таблице изменений может быть много чего зарегистрировано (но не так критично, как итоги).

Были примерно такие же проблемы с оборотным регистром, перешли на использование агрегатов, и все стало хорошо. А так, оставь только актуальные итоги, и посмотри на быстродействие.

(36) В индекс оно таки попадает, в кластерный, причём сиком, а не сканом. И лопатит не всю таблицу итогов, а треть от неё. В полной таблице ещё вопрос какой индекс должен быть и как угадать попадание в него оптимизатором. Табле скан будет люто печальным, гораздо тяжелее.
(38) Пробовал join добавлять, разный, не помогает. Соединение отрабатывает после тяжелой выборки.
(40) Видимо не секционирование, а установку минимального периода итогов? Посмотрю профайлером чем он там занимается, была такая идея.
(41) Регистр сведений, что хочу почистить не периодический и флагов "Разрешить итоги: ххх" не стоит. Т.е. Таблиц итогов там нет. И изменения не фиксируются, данный регистр не участвует в планах обмена. Ну по крайней мере таблица регистраций изменений пуста.

(42) Были бы у остаточных регистров агрегаты... Только актуальные итоги оставить не получится, иногда требуется остатки на какой-то период. Собственно секционированием я и пытался добиться использования только оперативных итогов, сложил их аккуратно в отдельную секцию... Но эта зараза не желает выбирать маленькую порцию данных, упорно читает всю таблицу. В это и вопрос, как обязать сиквел и/или 1С использовать одну секцию и не лезть куда не просят.

(43) вообще то вначале будет сканировать индекс(есть некоторая не здоровая уверенность что не кластерный), а потом выбирать цифорки уже из полной таблицы, что в принципе может быть намного дольше - 2 последовательных выборок вместо одной но большей?

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

(44) только если нет строк неограниченной длинны. Иначе печалька.

(45) Можно попробовать выборку из основной таблицы, без использования виртуальной "Остатки", в принципе должно тоже самое получиться. Я правильно понимаю идею? Делаю выборку по отбору с получением ресурсов и группировкой. При этом будет скорее всего индекс сик по отбору, кей-лукап для получения ресурса. Далее скорее всего будет Hash Match (Aggregate) для получения SUM. И это должно отработать быстрее получения итогов?

(46) такую ересь первым делом из регистров убрал. Было в одном месте, пока поменял ntext на nvarchar(100), попозже  удалю совсем.

(47) да. быстрее, но после прогревки кеша. Т.е. если запрос выполняется часто. Проблема именно в том, что сложный запрос к остаткам иногда выбирает неверный план.

кроме того запрос прямой. думаю что там даже не будет индекссик, просто табле скан, но это быстрее, чем сложный запрос у меня вышло.

Конечно условие что все отборы должны быть максимально простыми (= или В конкретных значений)

(49), (50) С горячим кэшем беда. У меня не один такой регистр, есть ещё более тяжелые, бухгалтерские. Кэш хитс болтается между 99% и 99.9% в среднем, но периодически выпадает сильно ниже. Но попробую, завтра уже, отпишусь.

(51) бухгалтерские отдельная песня. Там Субконто и соотвествено у меня этот финт не работал.

(0) нормально будет работать . статистика не построилась.

(8) есть защита от неправильного пересоздания - ddl триггеры. http://catalog.mista.ru/public/114634/

(51) 80% попадание в кэш считается достаточным результатом.

(55) если эмуляция инмемори, то  нормальным 100%

(8) Пояндексил я наработки Германа Кудякова. Есть у него проект, Ei, брошен лет 5 назад, по крайней мере последнее обновление датируется 6 ноября 2013 г. Но такое ощущение, что проект его связан именно с инструментом-генератором SQL запросов. Дабы не в менеджмент студии запросы писать, а в 1С мышкой тыкать.
(52) Попробовал... Что хочу сказать, 1С странная... :) Результаты действительно интересные. На inner join/where отборе с временной таблицей-отбором срабатывает индекс сиик не кластерный, всё отбирается, далее по небольшой выборке проходит кей лукап и соббственно всё. При примерно одинаковом количестве ридсов, нагрузка на ЦПУ в 100 раз меньше, время выполнения в 100 раз быстрее. Буду проверять одинаковость выборок, но... Интересно.
(53) У меня каждую ночь статистика обновляется, не работает почему-то. Нужен какой-то волшебный пендель 1С и/или SQL. Вот какой?
(54) Объекты что сжаты и секционированы меняются крайне редко, руками можно восстановить. Ну в моём случае.
(55), (56) Я у себя заметил, т.к. база большая, если попадание в кэш опускается хотя-бы до 90%, производительность системы сильно падает. Визуально заметно на некоторых операциях.

(57)
почитайте https://habr.com/post/330070/

две полседние строчки статьи. похоже на Ваш случай

(57) будут тесты, выложите плз

(57) в 2012 скл был прикол - операция где в (2 значения) вызывала индекс скан
а одно значение индекс сик

поэтому приходилось делать запрос с объединением кусков

(58) Ооочень похоже на parameter sniffing. Только как его победить... Для хранимок рекомендуют использовать локальные переменные. Для самописных sp_executesql рекомендуют OPTION(RECOMPILE). Но для этого надо иметь доступ к "поправить запрос", формируемый сервером 1С, чего не получается...
(59) За выкладывание могут "пожурить" некоторые службы. Но чем смогу, тем поделюсь. Взял за основу запрос 1С, убрал sp_executesql, сформировал "Отбор" как ещё одну времянку, получил примерно в 150-200 раз меньшие затраты по CPU, reads dполовину меньше, время - чуть меньше секунды. против 171(1С .Остатки) и 5-7 секунд 1С перебор физики.

Есть ещё одна забавная идея, набрел на нее у Германа Кудякова http://main.1c-ei.ru/Articles/TypeQurey/
Но у меня не пока получилось влезть в чужую временную таблицу. Но правда не сильно искал, завтра продолжу.

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

(62) в чужую вт можно было влезть на ms sql 2000

в 2005 фичу закрыли

(0) у меня ощущение, что поигрались с флагами трассировки
не должно так работать.

(0) про отимизирующие флаги трассировки и секционирование
http://www.queryprocessor.ru/enable_query_optimizer_hotfixes/
какая у вас версия скл и уровень совместимости ?

(63) реальную временную. Но Михаил в (64) прав, такой финт ушами не пройдёт на сиквелах 2005 и старше. По крайней мере я не нашел как это сделать, а "старшие товарищи", они-же чистые сиквельщики (и DBA и прогеры), крутят пальцем у виска.
(63) Править именно запросы 1С не получится. Была идея "подменить" часть алгоритма, как описано у Германа, ссылка в (62). Выполняем часть запроса, подменяем одну из временных таблиц своим содержимым, идём дальше по алгоритму. С грязным чтением всё также как и у 1С. В обработках-отчётах везде поголовно (nolock). Теоретически можно на снапшот поменять, но об этом думать буду если всётаки 100% решу переходить на прямые запросы.
(65), (66) Не игрались флагами, но сегодня попробую:
1. Как рекомендует 1С в https://its.1c.ru/db/metod8dev#content:5904:hdoc включить флаг трассировки 4199.
2. Включу режим совместимости 110, была 100.

Связался на инфостарте с Германом, он ответил. Не ожидал если честно :) Он посоветовал сменить версию SQL. Завтра займусь, на тестовой среде попробую поднять 2016 SQL и поиграться с текущей и последней платформой 1С. Посмотрю что там получится.
Про сервер я писал в (17), у меня SQL Server 2012 SP4.

Попробовал, кстати рецепт по лечению parameter sniffing - OPTION(RECOMPILE). Руками, в менеджмент студии, подсовывая свою времянку. План запроса не поменялся, но быстрее отработал блок Inner Join - стоимость оператора снизилась на 30%. В целом по запросу, ридсы остались те-же, но CPU и время выполнения снизились на 30%. Но это так, больше академическая история, цель выйти на одну секцию.

кстати можно попробовать заменить таблицу представлением
и в представлении указать план запроса.

Включение флаги трассировки и совместимость не сильно, но  помогли. Результат сопоставим с OPTION(RECOMPILE) (67), но задачу секций не решили. Буду тест разворачивать, но это вопрос не одного дня.
(68) Оригинально... Попробую как-нибудь.

(69) В SQL есть условие на секцию в которую пойдет запрос. Если надо брать только 1 секцию, значит на нее и надо ставить условие.
Но судя по (0) проблема в незнании оптимизатора запроса о переменной которую получит итоговый запрос, поэтому план запроса и строится по всем секциям.
Я бы изменил хранимку, которая ставила в запрос секцию или условие на период константой (прямо прописанной в запросе).

Ткните меня в "где почитать про секции"

msdn.com

РегистрНакопления.МойРегистр.Остатки(, [Отбор по одному полю в 50 тысяч строк])
---
А если попробовать слегка наоборот
---
(Выбрать * ИЗ РегистрНакопления.МойРегистр.Остатки(,))
Соединение
[Отбор по одному полю в 50 тысяч строк]
---
Смысл такой, что первым запросом поднимается только одна секция, а далее соединением на нее накладываем поиск...

(70) >> Я бы изменил хранимку, которая ставила в запрос секцию.
Я в 1С пишу
Выбрать
[Поля]
ИЗ РегистрНакопления.МойРегистр.Остатки(, [Отбор по одному полю в 50 тысяч строк])

Что где поправить, о какой хранимке речь? [Отбор по одному полю в 50 тысяч строк] это условие вида:
Измерение В (Выбрать ВТ.Ссылка ИЗ ВТ)

(70) Я пробовал в запрос константы простых типов запихивать - дату, строку. 1С упорно добавляет это в параметры.

(71) Вот неплохая статейка http://www.sql.ru/articles/mssql/2005/073102partitionedtablesandindexes.shtml

(73) Не получается. Была мысль, проверял, добавить во времянку-отбор константой дату и соединять по Ссылке и дате. Проблема в том, что запрос, порождаемый 1С выглядит так:
select
   [поля]
from (
       select
           [измерения]
           SUM([ресурсы])
       from [таблица итогов]
       where _Period = @1 AND [Остальные условия виртуальной таблицы]
       group by [измерения]
       having by SUM([ресурсы]) <> 0
    )

(0) А если запихать пустую дату в текст 1с запроса в виде ДАТАВРЕМЯ(0,0,0,0,0,0)?

+ к (76). Так вот условия любого join накладываются на внешний запрос. Внутренний как поднимал 14 секций, так и поднимает...

(77) Пустая дата в 1С, это таки ДАТАВРЕМЯ(1, 1, 1). Но в запрос это также приходит параметром:
exec sp_executesql N'SELECT
... @10
...@P4 datetime2(3)... '2001-01-01 00:00:00'

(79) Попробуй таблицу подменить вьюшкой.
А в запросе вьюшки сделай условие, что берется только секция текущего года.

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/partition-transact-sql?view=sql-server-2017

В. Получение всех строк из одной секции секционированной таблицы или индекса
Следующий пример иллюстрирует получение всех строк, которые содержит секция 5 таблицы TransactionHistory

SELECT * FROM Production.TransactionHistory  
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;


Yj ns ukfdyjt gjcnfdm 'nj yf dm.ire

(80) Поставь условие секции на вьюшку.
Раз вьюшка будет брать только актуальную секцию, то и проблем со сканированием всех секций не будет

(80), (81) Предлагаешь сделать материализованную вьюху? А как 1С отнесется с записи в это безобразие? Ну и остаются всё-таки куча запросов, где период указывается и период приходит не 5999-11-01, а скажем 4018-05-01...

(82) тут по разному можно извратится.
можно измерение Секция с вьюхой сделать.
можно сделать копию РН, а потом в скуле реал таблицу подменить вьюхой секции.
Если надо, сделать столько копий РН сколько нужно

(83) +столько копий, сколько секций

(83) Тогда уж проще на прямые запросы перейти. По крайней мере в отчетах-обработках, где не нужна блокировка. Пока покручу идею в голове, хочу таки протестировать что покажет sql 2016 + последний релиз 1С.
(84) Регистр 100 гигов весит. Две таблицы с индексами и прочим. :)

Версия 8.3.10 у вас не самая удачная. Хотя может дело и не в ней

(86) Скорее всего дело вообще не в 1С. Я немного надеюсь на переработанный оптимизатор 2016-го сиквела и его хот-фиксы. Замена релиза 1С я думаю вообще ничего не поменяет. Если только режим совместимости не перевести в "Не использовать" и реструктуризировать всю базу. Но, боюсь я себе не смогу такого позволить, хотя скорее всего попробую на тестовой площадке.

(87) Может достаточно будет выделить таблицы регистра в отдельный файл и хранить на отдельном диске для повышения производительности? :)

(88) По оборудованию и разнесению файлов всё хорошо...
Оставлю тут, вдруг будет кому интересно-полезно.

Попробовал я на тестовой площадке и разные релизы 1С и сиквел 2016 с разными настройками совместимости и параметрами запуска сиквела. Не помогает, упорно оптимизатор поднимает все секции. Мой вывод - только прямые запросы. Пока правда не решил через что - АДО понятно, думал попробовать внешние источники данных, может там есть какие-то "полезные плюшки", но руки пока не дошли.

Пересчитал я итоги. По очереди, по кусочкам... Не ожидал я конечно ТАКОГО результата. Оказывается 1С ооочень сильно любит оставлять пустые и "несхлопутые" записи, особенно если включено "разделение итогов". Пересчет итогов снизил количество записей в таблице итогов более чем в 2 раза. Плюс повесил индексов несколько мимо 1С. Плюс переписал немного запросы под эти индексы. Как результат, скорость выполнения ряда тяжелых операций снизилась на 30-40%.

Буду ещё бизнес-логику менять, хочу уйти от некоторых тяжелых операций. Плюс прямые запросы. Но это пробовать-экспериментировать. На "послеотпускную пору", на осень.

Сделать свои итоги, с блекджеком и регистрами сведений уже предлагали? А этот регистр превратить в оборотный... Кстати,   кто-нибудь тестировал агрегаты на настоящих задачах? или это технологическое упражнение без реального применения?

(89) >Мой вывод - только прямые запросы.

Поддерживаю.

>Пока правда не решил через что - АДО понятно, думал попробовать внешние источники данных

Отдельная база (не 1С) и внешний источник.

>Оказывается 1С ооочень сильно любит оставлять пустые и "несхлопутые" записи, особенно если включено "разделение итогов"

Кто бы мог подумать! Разве только тот, кто документацию/ИТС писал...

(90) Агрегаты работают, после перехода с итогов на агрегаты размер базы значительно уменьшился, быстродействие точно не хуже.

(90) ну у него 8.2.16. Поэтому применить все эти новшества от платформы 8.3 у него не получится. Правильно он копает в сторону SQL

(0) Когда ты пишешь свой запрос в SSMS, ты не используешь параметры, т.е. пишешь динамический SQL и для этого запроса формируется каждый раз новый план. 1С для запросов с условиями вызывает sp_executesql, для этих запросов если есть план в кэше, используется он.

(0) Но вообще, автор, ты занимаешься херней, а то что тебе предлагают тут (свои итоги, вьюхи, триггеры) - еще большая херня. Таких людей Рупасов расстреливал у стенки.

(90) Я правильно идею понимаю? Делаем регистр оборотным, вешаем агрегаты, добавляем регламентное заполнение регистра сведений как "итогов". В целевом запросе по остаткам собираем "итоги" + обороты за нужный период. Есть где почитать? Но, имхо, проще тогда уж прямые запросы. Не нужно новые сущности плодить, а запрос по сути тот-же самый - "ближние итоги плюс обороты". А в SQL и индекс можно подобрать и вообще...
(91) >> Кто бы мог подумать!
Вот не надо тут. Я не сомневался, что пересчет итогов поможет, но ожидал сокращения записей не более чем на несколько десятков процентов. То, что количество записей сократились в разы, и приятное и не приятное открытие. Приятное, что помогло быстро решить локальную задачу. Неприятное, что этим надо следить и следить пристально.  Через месяц буду анализировать скорость деградации, видимо писать job'ы/регламенты.
(92), (93) Я что-то пропустил? Агрегаты вроде как можно только для оборотного регистра создать? Я использовал, оборотный регистр "Продажи". Отчёты, если в агрегат попадёшь, строятся очень быстро.

кстати, может быть проще выключить итоги по периодам и оставить только текущие итоги?

(94) Татычё?! Есть такая штука в составе средств управления сиквелом, профайлер называется. Он позволяет, в том числе "ловить" запросы генерируемые непосредственно 1С, вместе с актуальными их планами. Естественно запрос можно взять и нежно "покрутить" уже в SSMS, посмотреть как меняется его план.
(95) А поведай ка мне Антон Долгов, какие-нибудь есть идеи на тему 2-х терабайтной базы на предмет "обрезать", "ускорить" и всего-вот-этого-вот? При условии что монопольного доступа к базе есть 1-2 часа в сутки и сильно не каждый день. Или только овна на вентилятор вбросить не слабо? Не знаю с какого перепуга какой-то там Рупасов кого-то там расстреливал, это всяко не говорит о его выдающихся умственных способностях, во всяком случае в твоём изложении.

(96) Таки да, для оборотных агрегаты. Мы об этом и говорим :)

(97) Тогда надо делать 2 базы - одну оперативную, только с текущими итогами, а другую отчетную - наоборот без текущих итогов. Ибо если в базе только с текущими итогами задать период хотябы год - помрет нафик с такими объемами, как у ТС...

(97) Не, иногда требуется посмотреть сильно прошлый период. Я попробую поиграться урезанием "старых периодов", что 8.3 умеет сама установкой минимального периода итогов. Профайлером посмотрю запросы-зависимости, попробую руками почистить. Но для начала надо понять скорость деградации таблицы итогов, особенно оперативных.

(99) Так агрегаты ещё с 8.2, если не 8.1 появились. В 8.3 добавили им интеллекта по поводу "рекомендаций" и позволили управлять из предприятия.
(100) Я думал про 2 базы - архивная и оперативная. В оперативной последние 3-6 месяцев, в архивной всё остальное. Народ работает только в оперативной, длинная отчетность формируется в архивной. Не придумал как их создать быстро и как связывать между собой на предмет актуализации архивной. Так чтобы это работало быстро.

(98) Что именно тебе надо ускорить?

(102) Объемы в сутки очень большие? РИБ не прокатит?

(0) Смотрели стандартную обработку Все действия - стандартные обработки - управление итогами - полные возможности?


Полные возможности

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

(103) Ну как обычно - проведение документов, всякие регламентные операции.  Самое тяжелое, типовые 1Совые запросы, на которые я не могу никак повлиять. Это виртуальные таблицы "срез последних" и "остатки".
(104) Очень приличные. На пределе. Если будет какой-то сбой, уже есть шанс не догнать. Особенно в контексте планов роста. РИБ по функционалу меня устраивает полностью. Скорее всего будет гибрид какой-то. Метаданные и лёгкие объекты метаданных ходят РИБом, тяжелые SQL.

(106) И какой у тебя самый медленный запрос и какой у него план? Выложи в формате sqlplan

http://1sprogress.ru/anatomiya-registra-nakopleniya-vnutrennee-ustrojstvo-i-struktura-xraneniya.html

1С хранит текущие актуальные остатки (если поставить галку в настройках!) и хранит помесячно от сих до сих (две даты можно поставить в той же форме настроек (105).

Таблицы можно пересчитать.

То есть вот это надо посмотреть. Далее надо думать что там лучше по сути - регистр остатков или оборотов, закрывается ли он в 0, или там все время растет таблица остатков со временем.

(107) В (0) написано. Не самый, но один из. План не выложу, но там смотреть нечего. Не интересный - более 50% кластеред индекс сик таблицы итогов в части оперативных итогов. Остальное впополам иннер джоин с времянкой-отбором и "агрегация" хэш матчем. Основное в плане, что кластеред индекс сик партиционирован, количество актуальных секций =14.

Пересчет таблицы итогов еще нужен ибо http://catalog.mista.ru/public/177171/

(109) без партиционирования запрос какой тормозит и какой у него план? что побудило заняться партиционированием? может надо с этого начинать?

(109) Там остаточных предикатов точно нет?

(112) а додумывай сам! хрен тебе а не sqlplan, "там смотреть нечего"

Хотяяя.
Я правильно понимаю, что автор вытаскивает ВСЕ оперативные итоги, которых 50 тыс строк?

(102) Про схему архивная-оперативная и быструю актуализацию можно забыть. Пока 1С не разрешит использовать возможности скуля по быстрой обработке больших объемов данных средствами 1С. То есть многострочный DML.

(105), (108) Эта история работает с режима совместимости 8.3.5. У меня 8.2.
(110) Это видел, подчерпнул оттуда пару идей.
(111) А разве не очевидно, что побудило? Скорость работы не устраивает. Причём основные тормоза именно в тех местах на, которые я повлиять не могу. Обычные "ВЫБРАТЬ бла-бла из ххх.Остатки". Или "ххх.СрезПоследних".
(113) Нельзя выкладывать. СБ и всё такое.

(112) Вот сейчас не уверен, что точно понял о чём ты. Поясни плз, что ты имеешь ввиду, на что посмотреть?
(114) Не все, 50 тысяч из нескольких миллионов. Есть кластерный индекс, в нем первое поле период, второе поле, то что участвует в отборе.

(115) Да я как-то не шибко жажду спрашивать разрешения у 1С... Но мне больше нравится CDC.

(117) Отбой тревоги. Остаточный предикат работает только в диапазоне индекса. К твоей проблеме это не может относиться.

Сейчас я попытаюсь воспроизвести твои идеи.

(43) в кластерный может попадать из-за того, что много полей тянутся из таблицы.
Important: SQL Server does not use nonclustered indexes if it estimates that a large number of key or RID lookup operations will be required. (Korotkevitch D. - Pro SQL Server Internals)

(118) Не спрашивать разрешения это значит жить без поддержки и в постоянном страхе, что завтра с очередным обновлением платформы все поломается.

Есть еще вариант сделать хранилище в отдельной базе (не 1С), сделать его грамотно и юзать как внешний источник.

Но вообще, цифры ты приводишь дикие.

Я грешу на непрогретый кэш.
Но даже после DROPCLEANBUFFERS у меня цифры минимальны.
Без всяких секционирований.

SELECT count(*)
      
FROM [IEGAIS_MP].[dbo].[_AccumRgT687]

Результат: 8896130 за 49 секунд


SELECT *      
  FROM [IEGAIS_MP].[dbo].[_AccumRgT687]
  where [_Period]= ('59991101 00:00:00.000') and _Fld679RRef=0x810E000D3A22309E11E5EC30EF6782BC

После перезапуска ms sql сервер
Результат: 50598 строк за 0,6 секунд
HDD WD 5400, 2 Тб
Подозреваю, что жив кэш HDD

0,6 секунд вполне укладывается в твою 1 секунду. Но без секционирования.
Что то не так у тебя с запросом от 1С.

Сейчас сделаю запрос через 1С.

Ну как бэ через 1С то совсем другой запрос получается, автор.
С вложенной таблицей и группировочкой разделенных итогов.

В любом случае, после перезапуска 1С и MS SQL запрос
    Запрос=Новый Запрос;
    Старт=ТекущаяУниверсальнаяДатаВМиллисекундах();
    Запрос.Текст=
    "ВЫБРАТЬ
    |    ТоварыНаСкладахОстатки.Организация,
    |    ТоварыНаСкладахОстатки.ОбособленноеПодразделение,
    |    ТоварыНаСкладахОстатки.Склад,
    |    ТоварыНаСкладахОстатки.Номенклатура,
    |    ТоварыНаСкладахОстатки.Серия,
    |    ТоварыНаСкладахОстатки.КоличествоОстаток
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(, Организация = &Организация) КАК ТоварыНаСкладахОстатки";
    Запрос.УстановитьПараметр("Организация",Объект.Организация);
    Выборка=Запрос.Выполнить().Выбрать();
    Стоп=ТекущаяУниверсальнаяДатаВМиллисекундах();
    Сообщить(Строка(Выборка.Количество())+" строк за "+Строка(Стоп-Старт)+" мсек.");

Выполняется:
42 074 строк за 3 002 мсек.

Размер таблицы 8896130 строк

В профайлере он же
exec sp_executesql N'SELECT
T1.Fld679RRef,
T1.Fld680RRef,
T1.Fld681RRef,
T1.Fld682RRef,
T1.Fld683RRef,
T1.Fld684Balance_
FROM (SELECT
T2._Fld679RRef AS Fld679RRef,
T2._Fld680RRef AS Fld680RRef,
T2._Fld681RRef AS Fld681RRef,
T2._Fld682RRef AS Fld682RRef,
T2._Fld683RRef AS Fld683RRef,
CAST(SUM(T2._Fld684) AS NUMERIC(22, 2)) AS Fld684Balance_
FROM dbo._AccumRgT687 T2
WHERE T2._Period = P1 AND ((T2._Fld679RRef = @P2)) AND (T2._Fld684 <> @P3) AND (T2._Fld684 <> @P4)
GROUP BY T2._Fld679RRef,
T2._Fld680RRef,
T2._Fld681RRef,
T2._Fld682RRef,
T2._Fld683RRef
HAVING (CAST(SUM(T2._Fld684) AS NUMERIC(22, 2))) <> 0.0) T1',N'P1 datetime2(3),@P2 varbinary(16),@P3 numeric(10),@P4 numeric(10)','5999-11-01 00:00:00',0x811F000D3A223D1B11E6056572D76893,0,0

Проблема, скорее всего как раз в секциях. Он видит секции и начинает в них шарить, несмотря на индекс.

(0) а если в отборе не 50 тыс, а 1 тыс - план аналогичный?

(122) Это решается вьюхами. 1С спокойно себе работает с таблицами и не знает про вьюхи. Перенос данных спокойно себе работает со вьюхами и не знает про таблицы. Даже если 1С что-то поменяет в структуре хранения, актуализируем вьюхи, продолжаем работать.
(129) Сиквел хитро работает с секциями, они указаны в дереве индексов. В моём случае, т.к. он трогает все секции, я не получаю выигрыша от секционирования, "трогается" вся таблица, но по первому полю в индексе отбираются строки где Period = '5999-11-01'.

Почему трогаются все секции?

(130) На сколько я понял по тестам, ключевое отличие тут равенство или другое условие. Если взять пример (128), и предположить, что организация есть второе поле кластерного индекса после периода, то запрос:
РегистрНакопления.ТоварыНаСкладах.Остатки(, Организация = &Организация)

Полностью покроется кластерным индексом. Без дополнительных inner join. У меня же условие выглядит как:

РегистрНакопления.МойРегистр.Остатки(, ЗаказПокупателя В (ВЫБРАТЬ ВТ1.ЗаказПокупателя ИЗ ВТ1))

Где ВТ1 таблица в одно поле, 50 тысяч записей, только ссылки на заказы. И тут уже план другой получается. Поднимается по кластерному записи только по периоду, а потом иннер-джоинятся уже с ВТ1.

(132) :) Я собственно ради решения этого вопроса и тему создал. Причём берешь 1Совый запрос, меняешь совсем чуть-чуть, получаешь даже не тех-же самых данных кардинально иной результат.

(133) Вот мы и стали ближе к истине на 1 шаг.

Есть же теперь понимание того, что
РегистрНакопления.МойРегистр.Остатки(, ЗаказПокупателя В (ВЫБРАТЬ ВТ1.ЗаказПокупателя ИЗ ВТ1))

использует индекс только по Периоду, без участия в нем ЗаказПокупателя

?

Хех.
Сделал секционирование, как у автора и нарвался на его проблему.

Щас Дима разберется.

Как только в запросе начинают использоваться параметры - секционирование не работает

Секционирование работает:

exec sp_executesql N'SELECT
T1.Fld671RRef,
T1.Fld672RRef,
T1.Fld673RRef,
T1.Fld674RRef,
T1.Fld675RRef,
T1.Fld863_,
T1.Fld676Balance_
FROM (SELECT
T2._Fld673RRef AS Fld673RRef,
T2._Fld675RRef AS Fld675RRef,
T2._Fld671RRef AS Fld671RRef,
T2._Fld672RRef AS Fld672RRef,
T2._Fld674RRef AS Fld674RRef,
T2._Fld863 AS Fld863_,
CAST(SUM(T2._Fld676) AS NUMERIC(27, 3)) AS Fld676Balance_
FROM dbo._AccumRgT677 T2
WHERE T2._Period = ''59991101 00:00:00.000'' AND ((T2._Fld671RRef = 0x810E000D3A22309E11E5EC30EF6782BC)) AND (T2._Fld676 <> 0) AND (T2._Fld676 <> 0)
GROUP BY T2._Fld673RRef,
T2._Fld675RRef,
T2._Fld671RRef,
T2._Fld672RRef,
T2._Fld674RRef,
T2._Fld863
HAVING (CAST(SUM(T2._Fld676) AS NUMERIC(27, 3))) <> 0.0) T1',N'@P1 datetime2(3),@P2 varbinary(16),@P3 numeric(10),@P4 numeric(10)','5999-11-01 00:00:00',0x811F000D3A223D1B11E6056572D76893,0,0

Секционирование не работает:

exec sp_executesql N'SELECT
T1.Fld671RRef,
T1.Fld672RRef,
T1.Fld673RRef,
T1.Fld674RRef,
T1.Fld675RRef,
T1.Fld863_,
T1.Fld676Balance_
FROM (SELECT
T2._Fld673RRef AS Fld673RRef,
T2._Fld675RRef AS Fld675RRef,
T2._Fld671RRef AS Fld671RRef,
T2._Fld672RRef AS Fld672RRef,
T2._Fld674RRef AS Fld674RRef,
T2._Fld863 AS Fld863_,
CAST(SUM(T2._Fld676) AS NUMERIC(27, 3)) AS Fld676Balance_
FROM dbo._AccumRgT677 T2
WHERE T2._Period = P1 AND ((T2._Fld671RRef = @P2)) AND (T2._Fld676 <> @P3) AND (T2._Fld676 <> @P4)
GROUP BY T2._Fld673RRef,
T2._Fld675RRef,
T2._Fld671RRef,
T2._Fld672RRef,
T2._Fld674RRef,
T2._Fld863
HAVING (CAST(SUM(T2._Fld676) AS NUMERIC(27, 3))) <> 0.0) T1',N'P1 datetime2(3),@P2 varbinary(16),@P3 numeric(10),@P4 numeric(10)','5999-11-01 00:00:00',0x811F000D3A223D1B11E6056572D76893,0,0

Я бы на месте автора сходил бы на sql.ru, там бы сразу ответили, думаю.

С примерами из 139 -140

(140)Интересно, что было бы на постгресе...

В 139 префикс поиска ptnid1000=1
В 140 префикс поиска ptnid1000>=1 и ptnid1000<=3

Собственно, у меня 3 секции

https://ra-patience.livejournal.com/88958.html

https://blogs.msdn.microsoft.com/sqlcat/2006/02/17/partition-elimination-in-sql-server-2005/

Немного не то, у товарища в условии - функция.

http://sqlcom.ru/partition/sql-server-partition-for-beginers/

(149) т.е. ты слился?

(151) system, ты не выеживайся, расскажи лучше нам, почему ты до этого сам не дошел.

(151) Ну и статью почитай, про динамическую фильтрацию.

Вот интересно, какой-то чувак из Волгограда чота прочитал где-то про что-то и решил это у себя попробовать. Ок, ему простительно, он из региона и всё такое. Но ты-то Ежов, ты же сертифицированный иксперд, как ты повелся на невнятное блеяние и начал потакать хотелкам нуба?

Давай, до свидания.

(5) Ага, на ура...
Выбрать в () будет работать очень медленно.
Если нужно обрезать данные, то лучше использовать внутреннее соединение

(158) Дело не в том, как ты напишешь код запроса, а в том, какой оптимизатор построит план. Он может построить одинаковый план для inner join и in ().

РегистрНакопления.МойРегистр.Остатки(, ЗаказПокупателя В (ВЫБРАТЬ ВТ1.ЗаказПокупателя ИЗ ВТ1))

в часто приводит к скану. Лучше
РегистрНакопления.МойРегистр.Остатки(, ЗаказПокупателя = &знаение)   если одно значение, тогда вроде он ВСЕГДА использует индекс сик

Кроме того попробуйте сделать для ВТ1 индекс по заказу, тогда возможен индекс мердж

"Недокументировано" лезешь в базу и хочешь чтобы 1С тебя поняла? Будь готов к тому что не поймет и "отказаться от запросов 1С"

"Есть идеи как можно повлиять на РегистрНакопления.МойРегистр.Остатки ?" есть. написать в 1С, чтобы они учли это в новых релизах

exec sp_executesql('.....
....
FROM dbo._AccumRgT27531 T2 WITH(NOLOCK)
WHERE T2._Period = P1
.....' P1 datetime2(3).... '5999-11-01 00:00:00'

я правильно понимаю что 1С генерит несколько таких запросов вместо одного? пуская и с sp_executesql. или запрос один и все портит sp_executesql?

Уххх, какая ожесточенная схватка :) А Антон Долгов, это такой местный сумасшедший? Обижаться нельзя, ибо на таких обижаться непрактично?

(139), (140) Именно так. А круче всего результат получается, если во времянку-условие добавить ещё константное поле Период со значением '5999-11-01 00:00:00' и сделать inner join по заказу+периоду с таблицей итогов. На моих данных, сейчас 0.5 секунды против 12 без секций и 6-7 секунд с секцями.

(144) схожу, правда мне кажется, что там сходу посоветуют как надо запрос переписать, чтобы секции сработали. А я это и так уже знаю. Мне бы 1С заставить каким-то хитрым способом объяснить сиквелу... Но схожу. Коли будет результат, сюда отпишусь.

(158), (159) Inner join хорошо срабатывает когда соединяется с физической таблицей. Виртуальная таблица, это "select ... from (select ..." и join будет с внешним select... Т.е. отбора по заказам, в моём случае, не будет на уровне выборки из таблицы итогов.

(160) Понятно, что "ЗаказПокупателя = &знаение" будет лучше, но мне то надо по списку заказов.
Я индексирую ВТ, но у меня на времянке индекс скан получается.

(166) Ты же понимаешь, что ты делаешь фигню?

(161) Я вот как раз и сопротивляюсь до последнего "недокументированному лазанью" в базу. Хочется минимальными затратами получить максимальный профит. Как обычно впрочем.

(162) 1С генерирует один запрос. Всё портит "sp_executesql", а точнее то, что оптимизатор при построении плана не учитывает значение параметров. Не уверен что это именно оно, но очень похоже на "Parameter sniffing".

(167) Ты про индексирование? Я не увидел разницу с индексами и без. По крайней мере по запросам, что рождает 1С. Это тесты всё.

(167) Есть кстати жгучее желание испить немного пенных напитков и обсудить ряд жизненноважных проблем :)

(170) На 50000 записей и не увидишь...

(170) Именно.
Если ВТ велика (больше 1000 строк, конкретное значение уж не помню) - SQL не использует индекс при соединении.
Ведь, чтобы использовать индекс - нужен nested loops - проход по строкам временной таблицы и для каждой строки временной таблицы - индексный поиск в виртуальной.
Если строк ВТ будет много - SQL может посчитать, что проще обойти обе таблицы через mergejoin.

mergejoin будет еще более вероятен, если ВТ и Виртуальная таблицы индекскированы. Но не из-за индекса, а из-за того, что таблицы УПОРЯДОЧЕНЫ по полю поиска из за наличия этого индекса.

Но, упорядочить ВТ можно, не создавая Индекс, а отсортировав, что более дешево. Индекс опять не нужен, НО.

В вашем примере mergejoin использован не будет

Ибо, чтобы использовать mergejoin, нужно 2 таблицы, которых у вас пока нет, пока не выполниться поиск по Периоду из таблицы остатков.

По идее, если задать хинт на использование mergejoin, то SQL выполнит TableSpool (сохранение промежуточной таблицы в tempDB, фактически, та же временная таблица, средствами SQL) для результата отбора по периоду для таблицы остатков, дико медленную и потом сделает mergejoin с вашей временной.

Поэтому, SQL сделает HashJoin

SQL построит хэштаблицу для вашей временной таблицы, затем выберет записи из таблицы остатков по периоду и обойдет эти записи, ища их строки в хэштаблице.

(169) Все верно. Построитель запроса не знает параметров, поэтому строит план запроса на все секции.

Я бы сделал вьюху с таким вот условием:

WHERE case T2._Period = '2001-01-01 00:00:00' and '2002-01-01 00:00:00' and $PARTITION.partition_function(_Period) = 2001 then true

Секционирование заработает и особо извращаться не надо и построитель запроса все должен учесть.

(180) ну ес-но в CASE сам пропиши соотношения периодов и секций

точнее :)

WHERE case when view.Period between '2001-01-01 00:00:00' and '2002-01-01 00:00:00' and $PARTITION.partition_function(Period) = 2001 then true
when ... then

end

(177), (178) У меня mergejoin получается во всех вариантах.

(183) значит я что-то не учел.

получается автору надо поместить остатки в ВТ, а потом её уже отбирать.

(183) Воспроизвел твою ситуацию - HashMatch

Однако я ошибся с 1000 строк ВТ.

У меня ВТ была на 30Кстрок (при 8млн. строк таблицы остатков) - был nested_loops. 30 тыс поисков по индексу в таблице остатков.
Увеличил ВТ до 80Кстрок - стал hash_join.
Видимо, есть какое то отношение размера внешней и внутренней таблицы.

(185) Выигрыша не будет. Это тоже самое, что получить все оперативные остатки.

(186) У меня есть TRC, где в одном случае нестед лупс есть, но взял сейчас тот запрос, не получатся повторить, везде mergejoin...

(187) Вот тоже такое ощущение. Сейчас записей меньше стало, планы поменялись.

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

(191) Ты зря перестал принимать такие кругленькие штучки, что тётя доктор прописала. Береги себя.

(192) Дёргаются, да, но по дереву индексов всёравно я получаю оперативные итоги, что на следующем шаге иннер джоянятся mergejoin-ом с времянкой-фильтром. Как итог, с сервера вытягиваются данные только по 50 тысячам заказов. Если делать внутреннее соединение с виртуальной таблицей или складывать остатки во времянку, а потом соединять две времянки, будет как минимум дольше на вытягивание всех итогов и/или создание временной таблицы.

еще можно попробовать
https://msdn.microsoft.com/ru-ru/uk-an/library/ms179880(v=sql.120).aspx