Имя: Пароль:
1C
1С v8
Секционирование регистра накопления
0 VitShvets
 
28.05.18
15:04
А баловался ли кто-нибудь? Есть регистр накопления, остатки. Секционировал по «_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С против прямых запросов, но уж больно не хочется этого делать. Есть идеи как можно повлиять на
РегистрНакопления.МойРегистр.Остатки ?
1 vde69
 
28.05.18
15:15
правильно спроектировать регистр....

я не понимаю как можно угробить регистр всего в 50тыс строк что-бы он запрос делал 3 минуты....
2 impulse9
 
28.05.18
15:19
(0) индекс по полю есть? 3 минуты что-то многовато для 50 тысяч строк
3 Вафель
 
28.05.18
15:32
(1) 50 тыщ строк в отборе. а не в таблице
4 Wingless
 
28.05.18
15:40
(0) Разделители данных используются?
5 vde69
 
28.05.18
16:05
(3) для этого делается ВТ и условие ... В(выбрать Т.* из ВТ1 как Т).

работает на ура...
6 VitShvets
 
28.05.18
16:10
(1), (2) В регистре сотни миллионов записей. Конкретно в таблице итогов около 300 миллионов. ОТБОР  по 50 тысячам.
(2) Индекс по полю есть. Запрос попадает в кластерный индекс(сиик).
(4) не используются разделители.
(5) Именно так. Формируется выборка во времянку, индексируется. В отборе виртуальной таблицы указывается "ПолеОтбора В (Выбрать Ссыла ИЗ ВТ)".
7 H A D G E H O G s
 
28.05.18
16:16
300 млн. в итогах - это конечно, эпик, даже если дело ведётся с бородатых лет, но я все равно не понял, что хотел сказать автор
8 Bober
 
28.05.18
16:16
(0)  Герман Кудяков делал продукт на движке 1с, в котором был механизм секционирования СУБД MS SQL.
PS Нужно помнить, что при реструктуризации регистра все придется делать заново.
9 H A D G E H O G s
 
28.05.18
16:16
Вангую итоги от рождества Христова
10 ptiz
 
28.05.18
16:17
(6) "Конкретно в таблице итогов около 300 миллионов" - жуть какая. Это только в таблице оперативных итогов _AccumRgT27531 ???
Из интереса: сколько последних месяцев хранится? Там примерно по столько же?
11 H A D G E H O G s
 
28.05.18
16:19
Чем отличается запрос 1с от запроса автора?
12 Bober
 
28.05.18
16:20
(6) так может в таблице итогов много нулевых итогов?
В 8.3 можно управлять период рассчитанных итогов, например с 1.1.2010 по 30.04.2018 (до 8.3 нельзя было управлять датой начала расчета итогов)
13 vde69
 
28.05.18
16:27
(6) а кто будет указывать первый параметр виртуальной таблицы?


РегистрНакопления.МойРегистр.Остатки(???, [Отбор])
14 VitShvets
 
28.05.18
16:28
(7) В курсе что такое секционирование и как это работает?
(8) Спасибо за наводку, пояндексю.
(9) Всего лишь с 2010 года.
(10) AccumRgT27531 это таблица всех итогов, за все года и месяцы. Оперативные итоги, это итоги у которых _Period = '5999-11-01 00:00:00'.
(11) Это один и тот-же кусок запроса, пойманный профайлером. Разница в том, что типовой 1Совый запрос получает условие по дате как параметр, а я устанавливаю условие как константу.
15 Timon1405
 
28.05.18
16:29
(13) имеются ввиду текущие остатки, для них период указывать не нужно
16 Bober
 
28.05.18
16:30
(14) какая версия СУБД и 1с?
17 VitShvets
 
28.05.18
16:33
(12) скорее много не нулевых итогов. С этим боремся, но тут помощь не нужна, вопрос не в этом.
(12) >> В 8.3 можно управлять период рассчитанных итогов, например с 1.1.2010 по 30.04.2018
Гм... А можно поподробнее? Где почитать? Я бы с удовольствием удалил итоги за 2010-2016 годы.
(13) Хочется оперативных, текущих остатков. Отбор по периоду опущен специально.
(15) Именно!
(16) SQL Server 2012 SP4. 1С, древняя ещё толстоформенная КА на 8.3.10.2561 релизе.
18 vde69
 
28.05.18
16:34
(15) кто сказал, что не нужно?

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

ихмается мне что дело тут в секционности индекса, то есть в джойне нужно использовать поля соответсвующие основному индексу (при чем в правильном порядке), возможно во ВТ следует добавить несколько полей и подумать над соединением с физ таблицей...
19 vde69
 
28.05.18
16:36
(17) >>>Хочется оперативных, текущих остатков. Отбор по периоду опущен специально.

попробуй период поставить такой (граница(конецмесяца(текущаядата())),истина)
20 Вафель
 
28.05.18
16:37
(19) ты действительно не понимаешь какой запрос на сервер уходит и почему?
21 Bober
 
28.05.18
16:39
(17) РегистрНакопленияМенеджер.УстановитьМинимальныйИМаксимальныйПериодыРассчитанныхИтогов
и еще пара методов для работы с этой возможностью.
22 VitShvets
 
28.05.18
16:41
(18) Я говорю. Если не указывать, либо в параметр периода передать пустую дату, 1С интерпретирует это как запрос к оперативным итогам. Т.е. запрос будет ТОЛЬКО к таблице итогов и ТОЛЬКО к данным где _Period = '5999-11-01 00:00:00'. В случае указания любого периода, если не будет посчитанных данных на конец месяца, 1С будет строить запрос и к итогам и к движениям. Т.е. лучше не будет точно.
23 Локи-13
 
28.05.18
16:41
(17) может проблема в версии скуля?
Почему отличается поведение по сути одинаковых запросов?
24 Bober
 
28.05.18
16:42
(0) те при запросе к остаткам регистра ты получаешь идентичный запрос как если -бы ты сам его делал через студию. Запросы отличаются только в отборе по периоду.
25 Локи-13
 
28.05.18
16:43
Опять же, если
"Разница в скорости колоссальная – 1 секунда против 3 минут. "

То не вижу ничего предосудительного в использовании прямого запроса
26 vde69
 
28.05.18
16:47
(25) а я вижу, как ты думаешь, тому кто придет на смену с этим всем разбираться?
27 Локи-13
 
28.05.18
16:48
(26) судя по размеру базы абы кто туда не придет
28 VitShvets
 
28.05.18
16:51
(21) Гляну, спасибо.
(23) Как я понял по комментариям чистых сиквельщиков, дело в неопрееленности периода. Т.к. запрос не прямой, а  через sp_executesql, отимизатор компилит его на "максимальное покрытие"
(24) Не совсем. Я ловлю запрос, копирую его в менеджмент студию, выполняю, вижу что в плане поднято 14 секций вместо одной. Чуть-чуть модифицирую - указываю дату константой а не параметром, получается тот-же запрос, но поднимающий 1 секцию и работающий на порядки быстрее.
(25) Я всё больше склоняюсь к этому.
(26) Меньше всего меня заботит на тему, что кто-то там придёт. Во первых я сам пока никуда не собираюсь. Во вторых мне зарплату платят за то, чтобы система работала. И работала в устраивающих меня и бизнес границах.
29 VitShvets
 
28.05.18
17:24
(21) + (28) Беда-печаль. Семейство методов РегистрНакопленияМенеджер.УстановитьМинимальныйИМаксимальныйПериодыРассчитанныхИтогов работают с 8.3.2, а у меня стоит режим совместимости 8.2.16. А если просто удалить записи итогов, где период меньше, например, 01.01.2017?

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

Иногда на больших выходит быстрее
31 VitShvets
 
28.05.18
17:37
(30) в непериодический регистр сведений писать и через group by итоги считать? Или как?
32 ERWINS
 
28.05.18
17:41
(31) да
33 ERWINS
 
28.05.18
17:42
Запрос проще и оптимизатору сложнее ошибиться
34 VitShvets
 
28.05.18
17:46
В таблице движений х2 количество записей от итогов. Шансов попасть в индекс столько же, как и в секцию по итогам. Тогда уж на прямые запросы переходить, проблем имхо, будет меньше.
35 pavig
 
28.05.18
18:15
(30)
Сурово
36 ERWINS
 
28.05.18
18:20
(34) а нафиг тебе индекс то?
он по большому счету не используется если есть конструкт "В"
Там почти всегда используется фулскан.
37 ERWINS
 
28.05.18
18:23
Остатки хороши для бухрегистров и то только потому, что они сделаны говенно.
38 kittystark
 
28.05.18
18:42
(0) пробовал изменить запрос на ЛЕВОЕ соединение с "отборными" полями с последующим ГДЕ ЕСТЬ NULL ?
39 ERWINS
 
28.05.18
18:54
(38) зачем?
основной запрос вообще не должен ничего включать кроме примитивного фильтра и группировки
40 Bober
 
28.05.18
18:55
(29) тогда лучше всего посмотреть как работает секционирование на новой чистой базе 1с на SQL 2014\2016.
Где база 1с без режима совместимости и база СУБД тоже без режима совместимости.
41 Bober
 
28.05.18
18:57
(29) прямым запросом все чистится без проблем. нужно только помнить что в 8.3 у такого регистра появились таблица итогов (текущий срез данных). и в таблице изменений может быть много чего зарегистрировано (но не так критично, как итоги).
42 Genayo
 
28.05.18
19:14
Были примерно такие же проблемы с оборотным регистром, перешли на использование агрегатов, и все стало хорошо. А так, оставь только актуальные итоги, и посмотри на быстродействие.
43 VitShvets
 
28.05.18
19:20
(36) В индекс оно таки попадает, в кластерный, причём сиком, а не сканом. И лопатит не всю таблицу итогов, а треть от неё. В полной таблице ещё вопрос какой индекс должен быть и как угадать попадание в него оптимизатором. Табле скан будет люто печальным, гораздо тяжелее.
(38) Пробовал join добавлять, разный, не помогает. Соединение отрабатывает после тяжелой выборки.
(40) Видимо не секционирование, а установку минимального периода итогов? Посмотрю профайлером чем он там занимается, была такая идея.
(41) Регистр сведений, что хочу почистить не периодический и флагов "Разрешить итоги: ххх" не стоит. Т.е. Таблиц итогов там нет. И изменения не фиксируются, данный регистр не участвует в планах обмена. Ну по крайней мере таблица регистраций изменений пуста.
44 VitShvets
 
28.05.18
19:26
(42) Были бы у остаточных регистров агрегаты... Только актуальные итоги оставить не получится, иногда требуется остатки на какой-то период. Собственно секционированием я и пытался добиться использования только оперативных итогов, сложил их аккуратно в отдельную секцию... Но эта зараза не желает выбирать маленькую порцию данных, упорно читает всю таблицу. В это и вопрос, как обязать сиквел и/или 1С использовать одну секцию и не лезть куда не просят.
45 ERWINS
 
28.05.18
20:08
(43) вообще то вначале будет сканировать индекс(есть некоторая не здоровая уверенность что не кластерный), а потом выбирать цифорки уже из полной таблицы, что в принципе может быть намного дольше - 2 последовательных выборок вместо одной но большей?

"Табле скан будет люто печальным, гораздо тяжелее. " попробуй несколько запросов чуть разных подряд. Скорость удивит.
46 ERWINS
 
28.05.18
20:09
(44) только если нет строк неограниченной длинны. Иначе печалька.
47 VitShvets
 
28.05.18
20:36
(45) Можно попробовать выборку из основной таблицы, без использования виртуальной "Остатки", в принципе должно тоже самое получиться. Я правильно понимаю идею? Делаю выборку по отбору с получением ресурсов и группировкой. При этом будет скорее всего индекс сик по отбору, кей-лукап для получения ресурса. Далее скорее всего будет Hash Match (Aggregate) для получения SUM. И это должно отработать быстрее получения итогов?
48 VitShvets
 
28.05.18
20:38
(46) такую ересь первым делом из регистров убрал. Было в одном месте, пока поменял ntext на nvarchar(100), попозже  удалю совсем.
49 ERWINS
 
28.05.18
20:47
(47) да. быстрее, но после прогревки кеша. Т.е. если запрос выполняется часто. Проблема именно в том, что сложный запрос к остаткам иногда выбирает неверный план.
50 ERWINS
 
28.05.18
20:49
кроме того запрос прямой. думаю что там даже не будет индекссик, просто табле скан, но это быстрее, чем сложный запрос у меня вышло.

Конечно условие что все отборы должны быть максимально простыми (= или В конкретных значений)
51 VitShvets
 
28.05.18
20:56
(49), (50) С горячим кэшем беда. У меня не один такой регистр, есть ещё более тяжелые, бухгалтерские. Кэш хитс болтается между 99% и 99.9% в среднем, но периодически выпадает сильно ниже. Но попробую, завтра уже, отпишусь.
52 ERWINS
 
29.05.18
09:39
(51) бухгалтерские отдельная песня. Там Субконто и соотвествено у меня этот финт не работал.
53 МихаилМ
 
29.05.18
09:40
(0) нормально будет работать . статистика не построилась.
54 МихаилМ
 
29.05.18
09:44
(8) есть защита от неправильного пересоздания - ddl триггеры. http://catalog.mista.ru/public/114634/
55 МихаилМ
 
29.05.18
09:45
(51) 80% попадание в кэш считается достаточным результатом.
56 ERWINS
 
29.05.18
10:02
(55) если эмуляция инмемори, то  нормальным 100%
57 VitShvets
 
29.05.18
16:40
(8) Пояндексил я наработки Германа Кудякова. Есть у него проект, Ei, брошен лет 5 назад, по крайней мере последнее обновление датируется 6 ноября 2013 г. Но такое ощущение, что проект его связан именно с инструментом-генератором SQL запросов. Дабы не в менеджмент студии запросы писать, а в 1С мышкой тыкать.
(52) Попробовал... Что хочу сказать, 1С странная... :) Результаты действительно интересные. На inner join/where отборе с временной таблицей-отбором срабатывает индекс сиик не кластерный, всё отбирается, далее по небольшой выборке проходит кей лукап и соббственно всё. При примерно одинаковом количестве ридсов, нагрузка на ЦПУ в 100 раз меньше, время выполнения в 100 раз быстрее. Буду проверять одинаковость выборок, но... Интересно.
(53) У меня каждую ночь статистика обновляется, не работает почему-то. Нужен какой-то волшебный пендель 1С и/или SQL. Вот какой?
(54) Объекты что сжаты и секционированы меняются крайне редко, руками можно восстановить. Ну в моём случае.
(55), (56) Я у себя заметил, т.к. база большая, если попадание в кэш опускается хотя-бы до 90%, производительность системы сильно падает. Визуально заметно на некоторых операциях.
58 МихаилМ
 
29.05.18
17:12
(57)
почитайте https://habr.com/post/330070/

две полседние строчки статьи. похоже на Ваш случай
59 ERWINS
 
29.05.18
17:56
(57) будут тесты, выложите плз
60 ERWINS
 
29.05.18
18:03
(57) в 2012 скл был прикол - операция где в (2 значения) вызывала индекс скан
а одно значение индекс сик

поэтому приходилось делать запрос с объединением кусков
61 VitShvets
 
29.05.18
19:56
(58) Ооочень похоже на parameter sniffing. Только как его победить... Для хранимок рекомендуют использовать локальные переменные. Для самописных sp_executesql рекомендуют OPTION(RECOMPILE). Но для этого надо иметь доступ к "поправить запрос", формируемый сервером 1С, чего не получается...
(59) За выкладывание могут "пожурить" некоторые службы. Но чем смогу, тем поделюсь. Взял за основу запрос 1С, убрал sp_executesql, сформировал "Отбор" как ещё одну времянку, получил примерно в 150-200 раз меньшие затраты по CPU, reads dполовину меньше, время - чуть меньше секунды. против 171(1С .Остатки) и 5-7 секунд 1С перебор физики.
62 VitShvets
 
29.05.18
20:00
Есть ещё одна забавная идея, набрел на нее у Германа Кудякова http://main.1c-ei.ru/Articles/TypeQurey/
Но у меня не пока получилось влезть в чужую временную таблицу. Но правда не сильно искал, завтра продолжу.
63 ERWINS
 
29.05.18
20:15
(61) все равно спасибо, за что удастся
(61) реальную временную таблицу или материализованную?
(61) идея править запросы 1с? как там после этого с проблемами грязного чтения?
64 МихаилМ
 
29.05.18
20:27
(62) в чужую вт можно было влезть на ms sql 2000

в 2005 фичу закрыли
65 МихаилМ
 
29.05.18
20:42
(0) у меня ощущение, что поигрались с флагами трассировки
не должно так работать.
66 МихаилМ
 
29.05.18
20:50
(0) про отимизирующие флаги трассировки и секционирование
http://www.queryprocessor.ru/enable_query_optimizer_hotfixes/
какая у вас версия скл и уровень совместимости ?
67 VitShvets
 
30.05.18
19:24
(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%. Но это так, больше академическая история, цель выйти на одну секцию.
68 МихаилМ
 
30.05.18
23:08
кстати можно попробовать заменить таблицу представлением
и в представлении указать план запроса.
69 VitShvets
 
31.05.18
13:50
Включение флаги трассировки и совместимость не сильно, но  помогли. Результат сопоставим с OPTION(RECOMPILE) (67), но задачу секций не решили. Буду тест разворачивать, но это вопрос не одного дня.
(68) Оригинально... Попробую как-нибудь.
70 los_hooliganos
 
01.06.18
11:17
(69) В SQL есть условие на секцию в которую пойдет запрос. Если надо брать только 1 секцию, значит на нее и надо ставить условие.
Но судя по (0) проблема в незнании оптимизатора запроса о переменной которую получит итоговый запрос, поэтому план запроса и строится по всем секциям.
Я бы изменил хранимку, которая ставила в запрос секцию или условие на период константой (прямо прописанной в запросе).
71 H A D G E H O G s
 
01.06.18
11:40
Ткните меня в "где почитать про секции"
72 Мандалай
 
01.06.18
11:57
msdn.com
73 Salimbek
 
01.06.18
12:00
РегистрНакопления.МойРегистр.Остатки(, [Отбор по одному полю в 50 тысяч строк])
---
А если попробовать слегка наоборот
---
(Выбрать * ИЗ РегистрНакопления.МойРегистр.Остатки(,))
Соединение
[Отбор по одному полю в 50 тысяч строк]
---
Смысл такой, что первым запросом поднимается только одна секция, а далее соединением на нее накладываем поиск...
74 VitShvets
 
01.06.18
13:11
(70) >> Я бы изменил хранимку, которая ставила в запрос секцию.
Я в 1С пишу
Выбрать
[Поля]
ИЗ РегистрНакопления.МойРегистр.Остатки(, [Отбор по одному полю в 50 тысяч строк])

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

(70) Я пробовал в запрос константы простых типов запихивать - дату, строку. 1С упорно добавляет это в параметры.
75 VitShvets
 
01.06.18
13:15
76 VitShvets
 
01.06.18
13:22
(73) Не получается. Была мысль, проверял, добавить во времянку-отбор константой дату и соединять по Ссылке и дате. Проблема в том, что запрос, порождаемый 1С выглядит так:
select
   [поля]
from (
       select
           [измерения]
           SUM([ресурсы])
       from [таблица итогов]
       where _Period = @1 AND [Остальные условия виртуальной таблицы]
       group by [измерения]
       having by SUM([ресурсы]) <> 0
    )
77 тарам пам пам
 
01.06.18
13:23
(0) А если запихать пустую дату в текст 1с запроса в виде ДАТАВРЕМЯ(0,0,0,0,0,0)?
78 VitShvets
 
01.06.18
13:24
+ к (76). Так вот условия любого join накладываются на внешний запрос. Внутренний как поднимал 14 секций, так и поднимает...
79 VitShvets
 
01.06.18
13:30
(77) Пустая дата в 1С, это таки ДАТАВРЕМЯ(1, 1, 1). Но в запрос это также приходит параметром:
exec sp_executesql N'SELECT
... @10
...@P4 datetime2(3)... '2001-01-01 00:00:00'
80 los_hooliganos
 
01.06.18
13:42
(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
81 los_hooliganos
 
01.06.18
13:43
(80) Поставь условие секции на вьюшку.
Раз вьюшка будет брать только актуальную секцию, то и проблем со сканированием всех секций не будет
82 VitShvets
 
01.06.18
15:30
(80), (81) Предлагаешь сделать материализованную вьюху? А как 1С отнесется с записи в это безобразие? Ну и остаются всё-таки куча запросов, где период указывается и период приходит не 5999-11-01, а скажем 4018-05-01...
83 los_hooliganos
 
01.06.18
15:48
(82) тут по разному можно извратится.
можно измерение Секция с вьюхой сделать.
можно сделать копию РН, а потом в скуле реал таблицу подменить вьюхой секции.
Если надо, сделать столько копий РН сколько нужно
84 los_hooliganos
 
01.06.18
15:48
(83) +столько копий, сколько секций
85 VitShvets
 
01.06.18
15:58
(83) Тогда уж проще на прямые запросы перейти. По крайней мере в отчетах-обработках, где не нужна блокировка. Пока покручу идею в голове, хочу таки протестировать что покажет sql 2016 + последний релиз 1С.
(84) Регистр 100 гигов весит. Две таблицы с индексами и прочим. :)
86 kauksi
 
01.06.18
16:13
Версия 8.3.10 у вас не самая удачная. Хотя может дело и не в ней
87 VitShvets
 
01.06.18
20:31
(86) Скорее всего дело вообще не в 1С. Я немного надеюсь на переработанный оптимизатор 2016-го сиквела и его хот-фиксы. Замена релиза 1С я думаю вообще ничего не поменяет. Если только режим совместимости не перевести в "Не использовать" и реструктуризировать всю базу. Но, боюсь я себе не смогу такого позволить, хотя скорее всего попробую на тестовой площадке.
88 los_hooliganos
 
05.06.18
11:07
(87) Может достаточно будет выделить таблицы регистра в отдельный файл и хранить на отдельном диске для повышения производительности? :)
89 VitShvets
 
19.06.18
21:08
(88) По оборудованию и разнесению файлов всё хорошо...
Оставлю тут, вдруг будет кому интересно-полезно.

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

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

Буду ещё бизнес-логику менять, хочу уйти от некоторых тяжелых операций. Плюс прямые запросы. Но это пробовать-экспериментировать. На "послеотпускную пору", на осень.
90 Fragster
 
гуру
19.06.18
23:41
Сделать свои итоги, с блекджеком и регистрами сведений уже предлагали? А этот регистр превратить в оборотный... Кстати,   кто-нибудь тестировал агрегаты на настоящих задачах? или это технологическое упражнение без реального применения?
91 mistеr
 
20.06.18
00:04
(89) >Мой вывод - только прямые запросы.

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

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

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

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

Кто бы мог подумать! Разве только тот, кто документацию/ИТС писал...
92 Genayo
 
20.06.18
05:54
(90) Агрегаты работают, после перехода с итогов на агрегаты размер базы значительно уменьшился, быстродействие точно не хуже.
93 hhhh
 
20.06.18
09:39
(90) ну у него 8.2.16. Поэтому применить все эти новшества от платформы 8.3 у него не получится. Правильно он копает в сторону SQL
94 Антон Долгов
 
20.06.18
10:26
(0) Когда ты пишешь свой запрос в SSMS, ты не используешь параметры, т.е. пишешь динамический SQL и для этого запроса формируется каждый раз новый план. 1С для запросов с условиями вызывает sp_executesql, для этих запросов если есть план в кэше, используется он.
95 Антон Долгов
 
20.06.18
10:39
(0) Но вообще, автор, ты занимаешься херней, а то что тебе предлагают тут (свои итоги, вьюхи, триггеры) - еще большая херня. Таких людей Рупасов расстреливал у стенки.
96 VitShvets
 
20.06.18
16:05
(90) Я правильно идею понимаю? Делаем регистр оборотным, вешаем агрегаты, добавляем регламентное заполнение регистра сведений как "итогов". В целевом запросе по остаткам собираем "итоги" + обороты за нужный период. Есть где почитать? Но, имхо, проще тогда уж прямые запросы. Не нужно новые сущности плодить, а запрос по сути тот-же самый - "ближние итоги плюс обороты". А в SQL и индекс можно подобрать и вообще...
(91) >> Кто бы мог подумать!
Вот не надо тут. Я не сомневался, что пересчет итогов поможет, но ожидал сокращения записей не более чем на несколько десятков процентов. То, что количество записей сократились в разы, и приятное и не приятное открытие. Приятное, что помогло быстро решить локальную задачу. Неприятное, что этим надо следить и следить пристально.  Через месяц буду анализировать скорость деградации, видимо писать job'ы/регламенты.
(92), (93) Я что-то пропустил? Агрегаты вроде как можно только для оборотного регистра создать? Я использовал, оборотный регистр "Продажи". Отчёты, если в агрегат попадёшь, строятся очень быстро.
97 Fragster
 
гуру
20.06.18
16:09
кстати, может быть проще выключить итоги по периодам и оставить только текущие итоги?
98 VitShvets
 
20.06.18
16:22
(94) Татычё?! Есть такая штука в составе средств управления сиквелом, профайлер называется. Он позволяет, в том числе "ловить" запросы генерируемые непосредственно 1С, вместе с актуальными их планами. Естественно запрос можно взять и нежно "покрутить" уже в SSMS, посмотреть как меняется его план.
(95) А поведай ка мне Антон Долгов, какие-нибудь есть идеи на тему 2-х терабайтной базы на предмет "обрезать", "ускорить" и всего-вот-этого-вот? При условии что монопольного доступа к базе есть 1-2 часа в сутки и сильно не каждый день. Или только овна на вентилятор вбросить не слабо? Не знаю с какого перепуга какой-то там Рупасов кого-то там расстреливал, это всяко не говорит о его выдающихся умственных способностях, во всяком случае в твоём изложении.
99 Genayo
 
20.06.18
16:42
(96) Таки да, для оборотных агрегаты. Мы об этом и говорим :)
100 Genayo
 
20.06.18
16:45
(97) Тогда надо делать 2 базы - одну оперативную, только с текущими итогами, а другую отчетную - наоборот без текущих итогов. Ибо если в базе только с текущими итогами задать период хотябы год - помрет нафик с такими объемами, как у ТС...
101 VitShvets
 
20.06.18
16:55
(97) Не, иногда требуется посмотреть сильно прошлый период. Я попробую поиграться урезанием "старых периодов", что 8.3 умеет сама установкой минимального периода итогов. Профайлером посмотрю запросы-зависимости, попробую руками почистить. Но для начала надо понять скорость деградации таблицы итогов, особенно оперативных.
102 VitShvets
 
20.06.18
17:10
(99) Так агрегаты ещё с 8.2, если не 8.1 появились. В 8.3 добавили им интеллекта по поводу "рекомендаций" и позволили управлять из предприятия.
(100) Я думал про 2 базы - архивная и оперативная. В оперативной последние 3-6 месяцев, в архивной всё остальное. Народ работает только в оперативной, длинная отчетность формируется в архивной. Не придумал как их создать быстро и как связывать между собой на предмет актуализации архивной. Так чтобы это работало быстро.
103 Антон Долгов
 
20.06.18
17:39
(98) Что именно тебе надо ускорить?
104 Genayo
 
20.06.18
17:43
(102) Объемы в сутки очень большие? РИБ не прокатит?
105 romix
 
20.06.18
18:00
(0) Смотрели стандартную обработку Все действия - стандартные обработки - управление итогами - полные возможности?


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

Режим полных возможностей позволяет получить полный доступ ко всем инструментам работы с итогами (закладка Итоги) и агрегатами (закладка Агрегаты) регистров накопления и регистров бухгалтерии.
106 VitShvets
 
20.06.18
18:07
(103) Ну как обычно - проведение документов, всякие регламентные операции.  Самое тяжелое, типовые 1Совые запросы, на которые я не могу никак повлиять. Это виртуальные таблицы "срез последних" и "остатки".
(104) Очень приличные. На пределе. Если будет какой-то сбой, уже есть шанс не догнать. Особенно в контексте планов роста. РИБ по функционалу меня устраивает полностью. Скорее всего будет гибрид какой-то. Метаданные и лёгкие объекты метаданных ходят РИБом, тяжелые SQL.
107 Антон Долгов
 
20.06.18
18:11
(106) И какой у тебя самый медленный запрос и какой у него план? Выложи в формате sqlplan
108 romix
 
20.06.18
18:20
http://1sprogress.ru/anatomiya-registra-nakopleniya-vnutrennee-ustrojstvo-i-struktura-xraneniya.html

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

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

То есть вот это надо посмотреть. Далее надо думать что там лучше по сути - регистр остатков или оборотов, закрывается ли он в 0, или там все время растет таблица остатков со временем.
109 VitShvets
 
20.06.18
18:21
(107) В (0) написано. Не самый, но один из. План не выложу, но там смотреть нечего. Не интересный - более 50% кластеред индекс сик таблицы итогов в части оперативных итогов. Остальное впополам иннер джоин с времянкой-отбором и "агрегация" хэш матчем. Основное в плане, что кластеред индекс сик партиционирован, количество актуальных секций =14.
110 romix
 
20.06.18
18:22
Пересчет таблицы итогов еще нужен ибо http://catalog.mista.ru/public/177171/
111 Антон Долгов
 
20.06.18
18:24
(109) без партиционирования запрос какой тормозит и какой у него план? что побудило заняться партиционированием? может надо с этого начинать?
112 H A D G E H O G s
 
20.06.18
18:27
(109) Там остаточных предикатов точно нет?
113 Антон Долгов
 
20.06.18
18:30
(112) а додумывай сам! хрен тебе а не sqlplan, "там смотреть нечего"
114 H A D G E H O G s
 
20.06.18
18:35
Хотяяя.
Я правильно понимаю, что автор вытаскивает ВСЕ оперативные итоги, которых 50 тыс строк?
115 mistеr
 
20.06.18
18:35
(102) Про схему архивная-оперативная и быструю актуализацию можно забыть. Пока 1С не разрешит использовать возможности скуля по быстрой обработке больших объемов данных средствами 1С. То есть многострочный DML.
116 VitShvets
 
20.06.18
18:48
(105), (108) Эта история работает с режима совместимости 8.3.5. У меня 8.2.
(110) Это видел, подчерпнул оттуда пару идей.
(111) А разве не очевидно, что побудило? Скорость работы не устраивает. Причём основные тормоза именно в тех местах на, которые я повлиять не могу. Обычные "ВЫБРАТЬ бла-бла из ххх.Остатки". Или "ххх.СрезПоследних".
(113) Нельзя выкладывать. СБ и всё такое.
117 VitShvets
 
20.06.18
18:55
(112) Вот сейчас не уверен, что точно понял о чём ты. Поясни плз, что ты имеешь ввиду, на что посмотреть?
(114) Не все, 50 тысяч из нескольких миллионов. Есть кластерный индекс, в нем первое поле период, второе поле, то что участвует в отборе.
118 VitShvets
 
20.06.18
18:57
(115) Да я как-то не шибко жажду спрашивать разрешения у 1С... Но мне больше нравится CDC.
119 H A D G E H O G s
 
20.06.18
18:58
(117) Отбой тревоги. Остаточный предикат работает только в диапазоне индекса. К твоей проблеме это не может относиться.
120 H A D G E H O G s
 
20.06.18
18:58
Сейчас я попытаюсь воспроизвести твои идеи.
121 nicxxx
 
20.06.18
19:00
(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)
122 mistеr
 
20.06.18
19:00
(118) Не спрашивать разрешения это значит жить без поддержки и в постоянном страхе, что завтра с очередным обновлением платформы все поломается.

Есть еще вариант сделать хранилище в отдельной базе (не 1С), сделать его грамотно и юзать как внешний источник.
123 H A D G E H O G s
 
20.06.18
19:05
Но вообще, цифры ты приводишь дикие.

Я грешу на непрогретый кэш.
Но даже после DROPCLEANBUFFERS у меня цифры минимальны.
Без всяких секционирований.
124 H A D G E H O G s
 
20.06.18
19:10
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
125 H A D G E H O G s
 
20.06.18
19:11
0,6 секунд вполне укладывается в твою 1 секунду. Но без секционирования.
Что то не так у тебя с запросом от 1С.
126 H A D G E H O G s
 
20.06.18
19:11
Сейчас сделаю запрос через 1С.
127 H A D G E H O G s
 
20.06.18
19:21
Ну как бэ через 1С то совсем другой запрос получается, автор.
С вложенной таблицей и группировочкой разделенных итогов.
128 H A D G E H O G s
 
20.06.18
19:38
В любом случае, после перезапуска 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
129 H A D G E H O G s
 
20.06.18
19:40
Проблема, скорее всего как раз в секциях. Он видит секции и начинает в них шарить, несмотря на индекс.
130 vs84
 
20.06.18
20:15
(0) а если в отборе не 50 тыс, а 1 тыс - план аналогичный?
131 VitShvets
 
20.06.18
20:23
(122) Это решается вьюхами. 1С спокойно себе работает с таблицами и не знает про вьюхи. Перенос данных спокойно себе работает со вьюхами и не знает про таблицы. Даже если 1С что-то поменяет в структуре хранения, актуализируем вьюхи, продолжаем работать.
(129) Сиквел хитро работает с секциями, они указаны в дереве индексов. В моём случае, т.к. он трогает все секции, я не получаю выигрыша от секционирования, "трогается" вся таблица, но по первому полю в индексе отбираются строки где Period = '5999-11-01'.
132 H A D G E H O G s
 
20.06.18
20:28
Почему трогаются все секции?
133 VitShvets
 
20.06.18
20:30
(130) На сколько я понял по тестам, ключевое отличие тут равенство или другое условие. Если взять пример (128), и предположить, что организация есть второе поле кластерного индекса после периода, то запрос:
РегистрНакопления.ТоварыНаСкладах.Остатки(, Организация = &Организация)

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

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

Где ВТ1 таблица в одно поле, 50 тысяч записей, только ссылки на заказы. И тут уже план другой получается. Поднимается по кластерному записи только по периоду, а потом иннер-джоинятся уже с ВТ1.
134 VitShvets
 
20.06.18
20:32
(132) :) Я собственно ради решения этого вопроса и тему создал. Причём берешь 1Совый запрос, меняешь совсем чуть-чуть, получаешь даже не тех-же самых данных кардинально иной результат.
135 H A D G E H O G s
 
20.06.18
21:07
(133) Вот мы и стали ближе к истине на 1 шаг.

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

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

?
136 H A D G E H O G s
 
20.06.18
21:49
Хех.
Сделал секционирование, как у автора и нарвался на его проблему.
137 H A D G E H O G s
 
20.06.18
21:49
Щас Дима разберется.
138 H A D G E H O G s
 
20.06.18
22:02
Как только в запросе начинают использоваться параметры - секционирование не работает
139 H A D G E H O G s
 
20.06.18
22:04
Секционирование работает:

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
140 H A D G E H O G s
 
20.06.18
22:05
Секционирование не работает:

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
141 H A D G E H O G s
 
20.06.18
22:07
Я бы на месте автора сходил бы на sql.ru, там бы сразу ответили, думаю.
142 H A D G E H O G s
 
20.06.18
22:07
С примерами из 139 -140
143 Genayo
 
20.06.18
22:12
(140)Интересно, что было бы на постгресе...
144 H A D G E H O G s
 
20.06.18
22:13
В 139 префикс поиска ptnid1000=1
В 140 префикс поиска ptnid1000>=1 и ptnid1000<=3
145 H A D G E H O G s
 
20.06.18
22:13
Собственно, у меня 3 секции
146 H A D G E H O G s
 
20.06.18
22:14
147 H A D G E H O G s
 
20.06.18
22:15
148 H A D G E H O G s
 
20.06.18
22:22
Немного не то, у товарища в условии - функция.
149 H A D G E H O G s
 
20.06.18
22:34
151 Антон Долгов
 
20.06.18
23:12
(149) т.е. ты слился?
152 H A D G E H O G s
 
20.06.18
23:16
(151) system, ты не выеживайся, расскажи лучше нам, почему ты до этого сам не дошел.
153 H A D G E H O G s
 
20.06.18
23:17
(151) Ну и статью почитай, про динамическую фильтрацию.
154 Антон Долгов
 
20.06.18
23:17
Вот интересно, какой-то чувак из Волгограда чота прочитал где-то про что-то и решил это у себя попробовать. Ок, ему простительно, он из региона и всё такое. Но ты-то Ежов, ты же сертифицированный иксперд, как ты повелся на невнятное блеяние и начал потакать хотелкам нуба?
155 H A D G E H O G s
 
модератор
20.06.18
23:18
Давай, до свидания.
158 breezee
 
21.06.18
03:06
(5) Ага, на ура...
Выбрать в () будет работать очень медленно.
Если нужно обрезать данные, то лучше использовать внутреннее соединение
159 nicxxx
 
21.06.18
08:53
(158) Дело не в том, как ты напишешь код запроса, а в том, какой оптимизатор построит план. Он может построить одинаковый план для inner join и in ().
160 ERWINS
 
21.06.18
09:51
РегистрНакопления.МойРегистр.Остатки(, ЗаказПокупателя В (ВЫБРАТЬ ВТ1.ЗаказПокупателя ИЗ ВТ1))

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

Кроме того попробуйте сделать для ВТ1 индекс по заказу, тогда возможен индекс мердж
161 toypaul
 
гуру
21.06.18
10:00
"Недокументировано" лезешь в базу и хочешь чтобы 1С тебя поняла? Будь готов к тому что не поймет и "отказаться от запросов 1С"

"Есть идеи как можно повлиять на РегистрНакопления.МойРегистр.Остатки ?" есть. написать в 1С, чтобы они учли это в новых релизах
162 toypaul
 
гуру
21.06.18
10:01
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?
163 VitShvets
 
21.06.18
12:21
Уххх, какая ожесточенная схватка :) А Антон Долгов, это такой местный сумасшедший? Обижаться нельзя, ибо на таких обижаться непрактично?

(139), (140) Именно так. А круче всего результат получается, если во времянку-условие добавить ещё константное поле Период со значением '5999-11-01 00:00:00' и сделать inner join по заказу+периоду с таблицей итогов. На моих данных, сейчас 0.5 секунды против 12 без секций и 6-7 секунд с секцями.
164 VitShvets
 
21.06.18
12:25
(144) схожу, правда мне кажется, что там сходу посоветуют как надо запрос переписать, чтобы секции сработали. А я это и так уже знаю. Мне бы 1С заставить каким-то хитрым способом объяснить сиквелу... Но схожу. Коли будет результат, сюда отпишусь.
165 VitShvets
 
21.06.18
12:30
(158), (159) Inner join хорошо срабатывает когда соединяется с физической таблицей. Виртуальная таблица, это "select ... from (select ..." и join будет с внешним select... Т.е. отбора по заказам, в моём случае, не будет на уровне выборки из таблицы итогов.
166 VitShvets
 
21.06.18
12:33
(160) Понятно, что "ЗаказПокупателя = &знаение" будет лучше, но мне то надо по списку заказов.
Я индексирую ВТ, но у меня на времянке индекс скан получается.
167 H A D G E H O G s
 
21.06.18
12:35
(166) Ты же понимаешь, что ты делаешь фигню?
168 VitShvets
 
21.06.18
12:36
(161) Я вот как раз и сопротивляюсь до последнего "недокументированному лазанью" в базу. Хочется минимальными затратами получить максимальный профит. Как обычно впрочем.
169 VitShvets
 
21.06.18
12:40
(162) 1С генерирует один запрос. Всё портит "sp_executesql", а точнее то, что оптимизатор при построении плана не учитывает значение параметров. Не уверен что это именно оно, но очень похоже на "Parameter sniffing".
170 VitShvets
 
21.06.18
12:41
(167) Ты про индексирование? Я не увидел разницу с индексами и без. По крайней мере по запросам, что рождает 1С. Это тесты всё.
171 VitShvets
 
21.06.18
12:43
(167) Есть кстати жгучее желание испить немного пенных напитков и обсудить ряд жизненноважных проблем :)
172 Genayo
 
21.06.18
13:03
(170) На 50000 записей и не увидишь...
173 H A D G E H O G s
 
21.06.18
13:05
(170) Именно.
Если ВТ велика (больше 1000 строк, конкретное значение уж не помню) - SQL не использует индекс при соединении.
Ведь, чтобы использовать индекс - нужен nested loops - проход по строкам временной таблицы и для каждой строки временной таблицы - индексный поиск в виртуальной.
Если строк ВТ будет много - SQL может посчитать, что проще обойти обе таблицы через mergejoin.
174 H A D G E H O G s
 
21.06.18
13:07
mergejoin будет еще более вероятен, если ВТ и Виртуальная таблицы индекскированы. Но не из-за индекса, а из-за того, что таблицы УПОРЯДОЧЕНЫ по полю поиска из за наличия этого индекса.

Но, упорядочить ВТ можно, не создавая Индекс, а отсортировав, что более дешево. Индекс опять не нужен, НО.
175 H A D G E H O G s
 
21.06.18
13:07
В вашем примере mergejoin использован не будет
176 H A D G E H O G s
 
21.06.18
13:10
Ибо, чтобы использовать mergejoin, нужно 2 таблицы, которых у вас пока нет, пока не выполниться поиск по Периоду из таблицы остатков.

По идее, если задать хинт на использование mergejoin, то SQL выполнит TableSpool (сохранение промежуточной таблицы в tempDB, фактически, та же временная таблица, средствами SQL) для результата отбора по периоду для таблицы остатков, дико медленную и потом сделает mergejoin с вашей временной.
177 H A D G E H O G s
 
21.06.18
13:10
Поэтому, SQL сделает HashJoin
178 H A D G E H O G s
 
21.06.18
13:12
SQL построит хэштаблицу для вашей временной таблицы, затем выберет записи из таблицы остатков по периоду и обойдет эти записи, ища их строки в хэштаблице.
179 los_hooliganos
 
21.06.18
13:15
(169) Все верно. Построитель запроса не знает параметров, поэтому строит план запроса на все секции.
180 los_hooliganos
 
21.06.18
13:20
Я бы сделал вьюху с таким вот условием:

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

Секционирование заработает и особо извращаться не надо и построитель запроса все должен учесть.
181 los_hooliganos
 
21.06.18
13:21
(180) ну ес-но в CASE сам пропиши соотношения периодов и секций
182 los_hooliganos
 
21.06.18
13:23
точнее :)

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
183 VitShvets
 
21.06.18
13:30
(177), (178) У меня mergejoin получается во всех вариантах.
184 H A D G E H O G s
 
21.06.18
13:48
(183) значит я что-то не учел.
185 Fragster
 
гуру
21.06.18
14:27
получается автору надо поместить остатки в ВТ, а потом её уже отбирать.
186 H A D G E H O G s
 
21.06.18
14:50
(183) Воспроизвел твою ситуацию - HashMatch
187 H A D G E H O G s
 
21.06.18
14:54
Однако я ошибся с 1000 строк ВТ.

У меня ВТ была на 30Кстрок (при 8млн. строк таблицы остатков) - был nested_loops. 30 тыс поисков по индексу в таблице остатков.
Увеличил ВТ до 80Кстрок - стал hash_join.
Видимо, есть какое то отношение размера внешней и внутренней таблицы.
188 VitShvets
 
21.06.18
16:54
(185) Выигрыша не будет. Это тоже самое, что получить все оперативные остатки.
189 VitShvets
 
21.06.18
16:55
(186) У меня есть TRC, где в одном случае нестед лупс есть, но взял сейчас тот запрос, не получатся повторить, везде mergejoin...
190 VitShvets
 
21.06.18
16:56
(187) Вот тоже такое ощущение. Сейчас записей меньше стало, планы поменялись.
192 Fragster
 
гуру
21.06.18
17:07
(188) ну так веь ты говоришь, что у тебя все секции дергаются. а это не только оперативные остатки, но и помесячные итоги, которых в несколько (десятков) раз больше
193 VitShvets
 
21.06.18
17:13
(191) Ты зря перестал принимать такие кругленькие штучки, что тётя доктор прописала. Береги себя.
194 VitShvets
 
21.06.18
17:21
(192) Дёргаются, да, но по дереву индексов всёравно я получаю оперативные итоги, что на следующем шаге иннер джоянятся mergejoin-ом с времянкой-фильтром. Как итог, с сервера вытягиваются данные только по 50 тысячам заказов. Если делать внутреннее соединение с виртуальной таблицей или складывать остатки во времянку, а потом соединять две времянки, будет как минимум дольше на вытягивание всех итогов и/или создание временной таблицы.
195 МихаилМ
 
25.06.18
23:26