Имеем примерно следующую ситуацию.
1. Классы
Items с полем Name - некое динамически пополняемое и очищаемое хранилище, несколько сотен тысяч записей.
Items1 c полями Id,Name,Param,Parent - древовидный справочник, несколько тысяч записей.
Items2 c полями Name, Item1Id - линейный справочник, под миллион записей, динамически пополняемый.
ItemsToItems2 с полями ItemId и Item2Id - класс, обеспечивающий отношение многие ко многим для хранилища Items и справочника Items2

Запрос, выдающий для каждого элемента справочника Items1 количество записей в хранилище, которые имеют связь через объекты класса ItemsToItems2 со справочником Items2,которые, в свою очередь, связаны с соответствующим элементом справочника Items1
SELECT Id AS Item1Id,Parent AS pId, Param,Name,
(SELECT COUNT(DISTINCT ItemId) FROM ItemsToItems2
WHERE Item2Id->Item1Id=Items1.ID) AS ItemsCount
FROM Items1 ORDER BY Param.

Запрос выполняется около минуты, что есть много.

Какие меры можно принять для ускорения запроса, как его оптимизировать, какие индексы нарисовать?

Что любопытно, при некоторых вариантах выбора поля и направления сортировки (ORDER BY Param DESC) запрос выполняется за пару секунд (жаль, что не всегда нужна именно такая сортировка, точнее она совсем никогда не нужна).

Можно было бы, конечно, при записи в Items увеличивать какой-нибудь счетчик для Items1, но на запрос могут быть наложены дополнительные условия, а на все варианты условий счетчиков не напасешься.

Что-то производительность Cache' не радует.
Есть класс, состоящий из ссылок на классы-справочники и нескольких текстовых полей, полмиллиона записей.
Если запрос выдать без сортировки, первая запись появляется практически мгновенно.
Если добавить правило ORDER BY по любому полю (по всем созданы индексы, по справочникам - bitmap), то время выдачи первой записи колеблется от 5 до 15 секунд.
Что еще можно сделать для ускорения, учитывая, что размер таблицы быстро изменяется и может быть в пределах от нуля до нескольких миллионов записей, а разные запросы могут выдавать разную долю этих записей (т.е. установка для класса значений количества записей и доли записей, возвращаемых типовым запросом вроде как бессмысленна)?

CJIECAPb
Добавил в ItemsToItems2 вычислимое поле и индекс по нему:
Что-то я не понял, откуда взялось это - Item1IdGetObjectId.
На приведенный код Cache, естественно, ругается, что такого метода нет.
Что тут имелось ввиду?

CJIECAPbХм. У меня работает. У вас какая версия каше?
В классе Item2Id поле Item1Id какой тип имеет? Если %String - замените на Items1.
В ##class(Items2) параметра SqlComputeCode пропишите полное имя класса (если пакет по умолчанию User, то ##class(User.Items2))В общих чертах заработало, просто немного ошибся при копировании текста, но возникла другая проблема,
условие запроса WHERE Item1Id=Items1.ID возвращает всегда "ложь". Почему это может быть?
При отборе Item1Id видно, какие он принимает значения. Такой объект в Items1 есть, но сравнение всегда ложно. И так любая операция сравнения кроме !=, которая всегда возвращает "истина".
С чем это может быть связано, где я опять мог ошибиться?
Бился как пчелка об мед, но пока не понял, что бы это значило.