Добрый день. Поступила задача: разобраться почему отчет за 3 месяца выполняется по 2 часа. Сам по себе отчет состоит из кучи объединений, каждое из которых представляет собой примерно такой запрос по таблице ДвиженияССубконто (https://skr.sh/s4pRZ3RcPP6). Я понимаю, что виртуальная таблица ДвиженияССубконто сама по себе очень медленная, но вопрос не в этом. Проблема в том, что при выполнении запроса даже за 1 день, по факту выполняется полное сканирование таблицы "РегистрБухгалтерии.бит_Дополнительный_1.Субконто" (https://skr.sh/s4pFRpkplRG) (см. структуру индекса https://skr.sh/s4pTGF4ktPa), читается 77 миллионов записей, несмотря на то, что установлен отбор по СубконтоКт3. Почему отбор на СубконтоКт3 не попадает в Seek Predicates?

обновление статистики не помогает?

может селективнсть плохая?

судя по запросу 1с там "вид" какой то
какую долю этот вид занимает среди всех записей?

Вот это напахнуло навозом...

"Почему отбор на СубконтоКт3 не попадает в Seek Predicates?"

Потому что в индексе тебе мешает поле _VALUE_type, оно выпадает из условия. Тебе нужно переписать запрос

Чем тебе не нравится? У системы есть два варианта поведения:
1. Сначала идти в ВТ, а оттуда по каждой записи за период поочередно нырять в кластерный индекс таблицы субконто.
2. Взять и прохешировать ту часть таблицы субконто, которая имеет отношение к указанному субконто.
Оптимизатор выберет вариант в зависимости от количества строк в первой ВТ. Если их мало (тыща другая) - будет поштучно нырять. Но судя по всему у тебя много неупорядоченных записей. Вот система и предпочитает все разложить в хеш таблицу. Радуйся, что CE угадал количество строк и хеш таблица уместилась в память.
Запрос отработал самым правильным образом.
(5) Смотри равенство количества считанных и фактических строк. Попадание в индекс было 100%, ничего там не мешает. Покажи еще Table cardinality, чтобы убедится в этом.

А слабо субконто3 выразить нужным типом?

(6) "Попадание в индекс было 100%, ничего там не мешает"

Посмотри внимательно. _Fld1287 это разделитель данных, который идёт первым в индексе. На остальные поля индекса он забил, ибо не хватает отбора по идущему вторым полю _VALUE_type

(5) нюхай навоз дальше
(6) не убедил: тебе про индекс, ты про соединение
(7) а смысл?

(9) прохфессура? Ну поведай нам, как выкрутится? Я-то выход знаю, ибо оптимизировал подобное сто раз

(0) подозреваю, что абс_проект - это реквизит...

(11) и что?

https://its.1c.ru/db/metod8dev/content/1590/hdoc

(5) Добавил отбор на тип - это ничего не поменяло (https://skr.sh/s4pQ03xS3VB). На самом деле мне кажется это лишнее указывать в отборе тип. Для поля регистратор в каком-нибудь регистре накопления тоже создается 2 поля - один для типа, другой для ссылки, но там же не обязательно указывать тип при отборе.

(14) Покажи текста запроса SQL, но не картинкой а сюда текстом

У вас текст запроса 1С не соответствует плану запроса SQL

(16) Вот сюда выложил запрос. Я его отформатировал в более понятный вид: https://github.com/besschetnov/Expert1C/blob/master/Запрос%20ДвижениеССубконто
Там же несколько запросов выполняется, я все их поместил в один файл.

(17) Что это?

(17) Пришлите текст запроса из SQL профайлера

(19) https://github.com/besschetnov/Expert1C/blob/master/Запрос%20ДвиженияССубконто2
До этого прислал запрос SQL просто отформатированный, чтобы было проще читать

(20) Последний запрос в файле как раз и сканирует всю страницу

Кому интересно, в этой статье рассказывается как отрабатывает запрос на стороне SQL к таблице ДвиженияССубконто - http://catalog.mista.ru/1c/articles/1128780/

(21) Ну странно, что SQL делает так. По идее, ему 1С предлагает 1000 строк, с которыми он должен сделать nestedloops и попасть в индекс.

(14) этого не достаточно. В индексе субконто содержится ещё и вид субконто [в терминах ПВХ]

(24) Ну подскажите, пжста, какое примерно должно быть условие

(23) Самое интересно, что в этом запросе, который тоже есть в том файле, как я понимаю индекс отрабатывает (https://skr.sh/s4phwUltXGV):
T1.RecorderTRef,
T1.RecorderRRef,
T1.LineNo_
FROM (SELECT DISTINCT
T2._RecorderTRef AS RecorderTRef,
T2._RecorderRRef AS RecorderRRef,
T2._LineNo AS LineNo_
FROM dbo._AccRg1465 T2
INNER JOIN dbo._AccRgED1511 T3
ON T3._RecorderTRef = T2._RecorderTRef AND T3._RecorderRRef = T2._RecorderRRef AND T3._LineNo = T2._LineNo AND T3._Period = T2._Period AND T3._Correspond = P1 AND T3._Period >= @P2 AND T3._Period <= @P3
WHERE (((T2._Fld1287 = @P4)) AND (T3._Fld1287 = @P5)) AND (((T2._AccountCtRRef = @P6) AND (T2._Fld1466RRef = @P7) AND (T3._Value_TYPE = 0x08 AND T3._Value_RTRef = 0x0000015E AND T3._Value_RRRef = @P8) AND (T3._Value_TYPE = 0x08 AND T3._Value_RTRef = 0x0000015E)) AND T2._Period >= @P9 AND T2._Period <= P10)) T1

(0) Переписывай запрос на обращение к первичным таблицам. Что-то типа такого.


ВЫБРАТЬ
    ХозрасчетныйСубконто.Период КАК Период,
    ХозрасчетныйСубконто.Регистратор КАК Регистратор,
    ХозрасчетныйСубконто.НомерСтроки КАК НомерСтроки,
    ХозрасчетныйСубконто.Значение КАК Значение,
    ХозрасчетныйСубконто.Вид КАК Вид
ПОМЕСТИТЬ ЗначенияСубконто
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Субконто КАК ХозрасчетныйСубконто
ГДЕ
    ХозрасчетныйСубконто.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
    И ХозрасчетныйСубконто.Значение = &ВидДоходаРасходаКурсоваяРазница
    И ХозрасчетныйСубконто.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияБухгалтерии.Кредит)

ИНДЕКСИРОВАТЬ ПО
    Период,
    Регистратор,
    НомерСтроки
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Хозрасчетный.Регистратор КАК Регистратор,
    Хозрасчетный.Организация КАК Организация,
    Хозрасчетный.СчетДт КАК СчетДт,
    Хозрасчетный.СчетКт КАК СчетКт,
    ЗначенияСубконто.Значение КАК СубконтоКт3,
    Хозрасчетный.Сумма КАК СуммаРегл
ИЗ
    РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЗначенияСубконто КАК ЗначенияСубконто
        ПО (Хозрасчетный.Период = ЗначенияСубконто.Период
                И Хозрасчетный.Регистратор = ЗначенияСубконто.Регистратор
                И Хозрасчетный.НомерСтроки = ЗначенияСубконто.НомерСтроки )
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПланСчетов.Хозрасчетный.ВидыСубконто КАК ХозрасчетныйВидыСубконто
        ПО (Хозрасчетный.СчетКт = ХозрасчетныйВидыСубконто.Ссылка
                И ХозрасчетныйВидыСубконто.НомерСтроки = 3)


(25) добавь хотя бы отбор по виду субконто в параметры виртуальной таблицы

ВидСубконтоДт3 = Значение(ПланВидовХарактеристик.ВидыСубконто.ТвойВидСубконто) и СубконтоДт3 = &ЗначениеСубконто

(27) тоже не совсем правильно. У тебя нет отбора по виду субконто

https://its.1c.ru/db/metod8dev/content/1590/hdoc

Таблица со значениями субконто
[ОРРХ | ОРНР1 +] Регистратор + НомерСтроки + Корреспонденция
[ОРРХ | ОРНР1 +] Период + Регистратор + НомерСтроки + ВидСубконто + Корреспонденция (Кластерный)
[ОРРХ | ОРНР1 +] ВидСубконто + Значение

ты на какие индексы рассчитываешь, когда отбираешь в первую ВТ?

ГДЕ
    ХозрасчетныйСубконто.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
    И ХозрасчетныйСубконто.Значение = &ВидДоходаРасходаКурсоваяРазница
    И ХозрасчетныйСубконто.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияБухгалтерии.Кредит)

(21) Забавно, что вот у меня конфа в режиме 8.3.14 и я в основной таблице РБ вижу эти субконто:
http://prntscr.com/v72cuf
хотя вот здесь они не написаны
https://its.1c.ru/db/metod8dev/content/1798/hdoc

и по идее, автору можно юзать что-то типа такого:
ВЫБРАТЬ
    ХозрасчетныйОборотыДтКт.СчетДт КАК СчетДт,
    ХозрасчетныйОборотыДтКт.СчетКт КАК СчетКт,
    ХозрасчетныйОборотыДтКт.СубконтоКт3 КАК СубконтоКт3,
    ХозрасчетныйОборотыДтКт.Регистратор КАК Регистратор,
    ХозрасчетныйОборотыДтКт.СуммаОборот КАК СуммаОборот
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&Нач, &Кон, Регистратор, , , СчетКт=&СчетКт, , СубконтоКт3 = &СубконтоКт3) КАК ХозрасчетныйОборотыДтКт

(29) На 3 индекс он рассчитывает.

(31) а вид субконто откуда должен возникнуть?

(32) Платформа сама подставляет

(33) не-а. Платформа подтавляет только номер таблицы, вид субконто она не подставляет

Короче, 1С забило на справку

(35) субконто в основной таблице появились в 8.3.15, кажется. Но индексов по этим субконто нет

8.3.8 основная таблица не содержит значений субконто. Индекс №3 в ДвиженияССубконто не содержит Регистратор, Период, ВидДвижения
8.3.14 содержит.

(36) Точно, индексов нет. Тогда я бы попробовал вот так

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ХозрасчетныйСубконто.Регистратор КАК Регистратор
ПОМЕСТИТЬ Регистраторы
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Субконто КАК ХозрасчетныйСубконто
ГДЕ
    ХозрасчетныйСубконто.Значение = &СубконтоКт3
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ХозрасчетныйОборотыДтКт.Регистратор КАК Регистратор,
    ХозрасчетныйОборотыДтКт.СчетДт КАК СчетДт,
    ХозрасчетныйОборотыДтКт.СчетКт КАК СчетКт,
    ХозрасчетныйОборотыДтКт.СубконтоКт3 КАК СубконтоКт3,
    ХозрасчетныйОборотыДтКт.Организация КАК Организация,
    ХозрасчетныйОборотыДтКт.СуммаОборот КАК СуммаОборот
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&Нач, &Кон, Регистратор, , , СчетКт = &СчетКт, , ) КАК ХозрасчетныйОборотыДтКт
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Регистраторы КАК Регистраторы
        ПО (Регистраторы.Регистратор = ХозрасчетныйОборотыДтКт.Регистратор)

ГДЕ
    ХозрасчетныйСубконто.Значение = &СубконтоКт3


1С сама подставила ВидСубконто за меня, но вы можете и сами

(29) >> У тебя нет отбора по виду субконто.

В данном случае это не нужно.
Во-первых, первая таблица отобрана по значению субконто.
Кроме того - внимательно посмотри текст запроса в (0). Там нет отбора по виду субконто.
А если воткнуть это условие, то можно случайно исключить записи по счетам, где третье субконто имеет нужное нам значение, но принадлежит какому-то другому виду (например, пользователь в Предприятии добавил третье субконто какого-то своего вида, не предусмотренного в конфигураторе).

(39)(40) заблуждаетесь

"Кроме того - внимательно посмотри текст запроса в (0). Там нет отбора по виду субконто"

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

Непонятно, нахрена эти танцы с отдельной таблицей, если уж вы впилили эти данные в основную.

Добавьте индекс по субконто и аля-улю.
Ну не будет у вас субконт - не добавляйте индекс.

(42) Это к 1С.

(41) Хорошо, хорошо. Добавьте условие по субконто, я не запрещаю.

(43) Разве что обратная совместимость.

Ну возьмите любую консольку запросов, хотя бы с сайта ИТС, и тупо посмотрите на время выполнения

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ХозрасчетныйСубконто.Регистратор
ПОМЕСТИТЬ ВремТаблица // Эт штобы данные на клиент не гонять
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Субконто КАК ХозрасчетныйСубконто
ГДЕ
    ХозрасчетныйСубконто.Значение = &СубконтоКт3

и

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ХозрасчетныйСубконто.Регистратор
ПОМЕСТИТЬ ВремТаблица
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Субконто КАК ХозрасчетныйСубконто
ГДЕ
    ХозрасчетныйСубконто.Значение = &СубконтоКт3
    И ХозрасчетныйСубконто.Вид = &КакойТоТамВидСубконто

разумеется, данных в базе должно быть не 3 капли

(41) Еще раз. Отбор по виду субконто исказит результат.
Как только пользователь добавит свой вид субконто, твой запрос перестанет работать корректно.
Здесь логика задачи иная. Получить кредитовые обороты по конкретному значению аналитики. Без(!) привязки к виду субконто.
Нельзя забывать, что конкретное значение субконто может относится к нескольким различным видам субконто.

(46) http://prntscr.com/v73emb

(47) с чего бы он исказил, если ТС выбирает по конкретному значению? Если так уж страшно за попадание в вид субконто, то можно заранее подготовить массив со всеми возможными видами субконто (для текущего значения) и отбирать по нему

И ХозрасчетныйСубконто.Вид В (&ВсеВидыВКоторыхИспользуетсяТипВыбираемогоЗначенияСубконто)

но вообще, какой-то там швах

(28) Для чистоты эксперимента добавил отбор на ВидСубконто, хотя тут (47) правильно подмечено. В результате ничего не поменялось - https://skr.sh/s4pQc2eFyVw

Столько всего написано)  H A D G E H O G s, какой пока вывод? Что попробовать?

(51) (38)

(48) без отбора по виду субконто
https://prnt.sc/v73ypo

с отбором по виду субконто
https://prnt.sc/v73zov

(52) Там еще отбираются в этом запросе реквизиты: Организация, абс_Классификатори т.д. ((( Их нет в таблице ОборотыДтКт https://skr.sh/s4pIQL3aDNK

(54) вывод - переписывать запрос

(54) Тогда пользовать основную таблицу
РегистрБухгалтерии.Хозрасчетный
и самому считать обороты.

Как предложил (27)? Я не до конца понял зачем тут соединение?

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПланСчетов.Хозрасчетный.ВидыСубконто КАК ХозрасчетныйВидыСубконто
        ПО (Хозрасчетный.СчетКт = ХозрасчетныйВидыСубконто.Ссылка
                И ХозрасчетныйВидыСубконто.НомерСтроки = 3)

Я попробовал сделать так, но у меня вторая таблица пустая, что я не так сделал? См. запрос https://skr.sh/s4prSj8HS0o

Хозрасчетный.СчетКт = ХозрасчетныйВидыСубконто.Ссылка

(58) тут неверно. Но вообще непонятно, зачем эта 3 таблица. А так -да ,оно.

Вот в этой статье написано как переписать таблицу ДвиженияССубконто на физическую (http://catalog.mista.ru/1c/articles/1128780/) (Ctrl+F "Свой запрос движений с субконто")
(58) Сейчас посмотрю
Можно и так (27) и так как в статье попробовать

(57) >> зачем тут соединение?
(59) >> непонятно, зачем эта 3 таблица.

Отбор по третьему субконто.
По условию задачи нам нужны кредитовые обороты по конкретному значению субконто 3(!). То есть нам не нужны обороты, когда это субконто будет первым или вторым. Первая таблица отбирает записи по значению субконто в кредите, но в неё могут попасть те записи, где это субконто было первым или вторым (а нужно - только третье).

(27) Сработало за 0.126 секунды вместо 2.5 минут, индексы отработали, количество строк вернулось то же. Походу то что нужно, остановлюсь на этом варианте)
Осталось теперь полностью вникнуть в (61) и применить эти знания при переделке всего отчета, в котором 13 таких объединений с абсолютно разными отборами по разным субконто) Всем спасибо!

Если будет возможность, загляните в эту тему в конце недели, вдруг снова закопаюсь)

(61) ты отбираешь по счетуКт, периоду, регистратору и номеру строки. Откуда там может взяться постороннее субконто?

(63) А кто-то запрещает этому значению субконто оказаться первым или вторым?
Там какой-то отбор по значению &ВидДоходаРасходаКурсоваяРазница.
Тебе главбух лично мамой поклялась, что ни в коем случае не допустит использования на предприятии этого значения на тех счетах, где оно может быть вторым или третьим субконто?

(64) ну не в этом же счёте. Счёт-то один

(65) >> Счёт-то один.


Упс!
Извиняюсь!
Только сейчас увидел отбор по счету.
В таком случае безусловно дополнительное внутреннее соединение с таблицей ВидыСубконто совершенно не нужно.

(66) А как ты считаешь, в общем случае нужно использовать внутреннее или все-таки левое соединение при соединении основной таблицы с таблицей субконто? (27)

(67) Просто в статье http://catalog.mista.ru/1c/articles/1128780/ используется левое соединение. Но внутреннее быстрее отрабатывает значительно, так как в этом случае отборы можно сделать на уровне самого соединения

(62) а что будет со временем, если за отбор по абс_проект в ГДЕ вынести?

(68) "так как в этом случае отборы можно сделать на уровне самого соединения"
План запроса не изменится если при внутреннем соединении условия по значениям вынести в ГДЕ

(70) Я имел в виду, что при левом соединении не получится наложить отбор в самом соединении, результат будет отличаться

(71) ну так это уже другая функциональность, к чему тут про скорость

(67) >> в общем случае нужно использовать внутреннее или все-таки левое соединение при соединении основной таблицы с таблицей субконто?

Нет никакого общего случая. Есть конкретная ситуация. В данном конкретном случае нужно именно внутренне соединение. Т.к. нам нужны обороты по конкретному значению субконто (из таблицы Субконто) и только те записи из второй таблицы (Хозрасчетный), которые соответствуют условию соединения. А вариант, когда есть записи в таблице Субконто, но нет соответствующих им записей в таблице Хозрасчетный, является исключительным и говорит об ошибке в базе данных. Как изменится план запроса и его производительность при изменении соединения на левое, я не знаю. Нужно экспериментировать. Я обычно пишу запросы, исходя в первую очередь из логики, а эксперименты с изменениями вида соединения провожу, когда производительность запроса становится серьёзной проблемой.
Гораздо интереснее - как изменилась бы скорость запроса, если не создавать временную таблицу ЗначенияСубконто с индексами по полям соединения, а сделать вместо пакета один запрос с соединением таблиц Субконто и Хозрасчетный. И как будет зависеть производительность запроса от количества записей с нужным нам значением субконто (пример в (0) не очень показателен в этом плане, т.к. вероятнее всего количество записей с курсовой разницей относительно не много в базе).