Сильно переделанная УПП 1.3 на 8.2, MySQL 2005. Используется RLS. Столкнулся с такой проблемой, что в зависимости от того какие есть "Группы доступа" у пользователя очень сильно различается скорость чтения данных. Стал изучать проблему. Для этого переделал шаблон из RLS в обычный запрос и проверяю время выполнения. Разница между пользователями огромна: 0.14 с. и 40 с. время выполнения.
Запрос стандартный:

ВЫБРАТЬ ПЕРВЫЕ 50 //Ограничил ПЕРВЫЕ 50 для ускорения выполнения
    ТекущаяТаблица.Ссылка,
    ТекущаяТаблица.Реквизит1 //и т.д.
ИЗ
    Документ.ТаблицаККоторойНеобходимоПолучитьДоступ КАК ТекущаяТаблица
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ РАЗЛИЧНЫЕ
            СоставГруппы.Ссылка КАК ГруппаПользователей
        ИЗ
            Справочник.ГруппыПользователей.ПользователиГруппы КАК СоставГруппы
        ГДЕ
            СоставГруппы.Пользователь = &ТекущийПользователь) КАК ГруппыПользователей
        ПО (ИСТИНА)
ГДЕ
    НЕ ГруппыПользователей.ГруппаПользователей ЕСТЬ NULL
    И НЕ 1 В
                (ВЫБРАТЬ ПЕРВЫЕ 1
                    1
                ИЗ
                    РегистрСведений.НазначениеВидовОбъектовДоступа КАК НазначениеВидовОбъектовДоступа ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НастройкиПравДоступаПользователей КАК НастройкиПравДоступаПользователей
                        ПО
                            (НастройкиПравДоступаПользователей.ОбъектДоступа = ВЫБОР
                                    КОГДА НазначениеВидовОбъектовДоступа.ВидОбъектаДоступа = ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.Организации)
                                        ТОГДА ТекущаяТаблица.Организация
                                    КОГДА НазначениеВидовОбъектовДоступа.ВидОбъектаДоступа = ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.Подразделения)
                                        ТОГДА ТекущаяТаблица.Подразделение
                                    КОГДА НазначениеВидовОбъектовДоступа.ВидОбъектаДоступа = ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.СтатьиДДС)
                                        ТОГДА ТекущаяТаблица.СтатьяДвиженияДенежныхСредств
                                КОНЕЦ
                                )
                                И НастройкиПравДоступаПользователей.ВидОбъектаДоступа = НазначениеВидовОбъектовДоступа.ВидОбъектаДоступа
                                И НастройкиПравДоступаПользователей.ОбластьДанных = ЗНАЧЕНИЕ(Перечисление.ОбластиДанныхОбъектовДоступа.ПустаяСсылка)
                                И НастройкиПравДоступаПользователей.Пользователь = ГруппыПользователей.ГруппаПользователей
                ГДЕ
                    НазначениеВидовОбъектовДоступа.ГруппаПользователей = ГруппыПользователей.ГруппаПользователей
                    И НазначениеВидовОбъектовДоступа.ВидОбъектаДоступа В (
                    ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.ПустаяСсылка),
                    ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.Организации),
                    ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.Подразделения),
                    ЗНАЧЕНИЕ(Перечисление.ВидыОбъектовДоступа.СтатьиДДС)
                    )
                    И НастройкиПравДоступаПользователей.ОбъектДоступа ЕСТЬ NULL )

Посмотрел план запроса в SQL Profilere. Он одинаковый для "быстрого" и "медленного" пользователей. За единственным исключением: у "медленного" пользователя громадное значение "Actual number jf rows", равное количеству строк в нашей таблице или для некоторых операций равное произведению итераций на количество строк таблицы. "Estimeted number of rows" равен 1. Для "быстрого" пользователя данные этих параметров вполне нормальные. К примеру: "A-number" = 250, "E-number" = 55.

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

Как я понимаю, проблема в статистике. Пробовал обновить статистику для конкретной таблицы с помощью "Update statistic with fullscan". Кроме того ночью обновляется статистика всей базы. Ни то, ни другое не помогло. Есть мысль, что "медленного" пользователя очень нерепрезентативная выборка и данных мало, поэтому оптимизатор не пользуется статистикой.

Собственно вопрос: кто с чем то подобным сталкивался ? Что вообще можно сделать в такой ситуации ? Как возможна разница во времени выполнения одного и того же запроса в 280 раз ?..

А где Разрешенные ?

ВЫБРАТЬ разрешенные ПЕРВЫЕ 50

(1) Думаете это повлияет на результат замеров времени выполнения ? Проблема же в том, что один и тот же запрос оптимизатор по разному трактует..

(1) Тем более я же тестирую под полными правами. Какая разница есть флаг разрешенные или нет ?

Ну дык группы доступа же разные. Т.е. разные входные данные. А статистика для всех одна.
Что делать? Побуду совой-стратегом: не использовать типовые RLS на больших объемах данных.

(0) Одинаковые планы, выбирающие разное количество строк? Где-то тут нестыковочка. Может они всё же не одинаковые? Например и там и там CLUSTERED INDEX SEEK , но предикаты поиска разные.

(5) Согласен как то странно звучит. Но я максимально внимательно прошелся по плану и увидел различие только в "Actual number of rows". А так конечно вы правы, что наличие или отсутствие предиката в Индекс сике все меняет. Но у меня например есть и индекс скан, в котором все одинаково кроме числа строк..

(4) 150 тысяч записей в таблице -- это не так уж и много

(4) Другой вариант — апгрейдить древний скуль на современный, гда оптимизатор умнее и умеет учитывать "разные входные данные".

(6) Выложил бы планы (в тексте).

Rows         Executes     StmtText                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      StmtId       NodeId       Parent       PhysicalOp           LogicalOp            Argument                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      DefinedValues                                                                                                                                                                                                                                                                                                                                                                                                                                                EstimateRows EstimateIO   EstimateCPU  AvgRowSize   TotalSubtreeCost OutputList                                                                                                                                                                                                                                   Warnings          Type         Parallel     EstimateExecutions
----         --------     --------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      ------       ------       ------       ----------           ---------            --------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                ------------ ----------   -----------  ----------   ---------------- ----------                                                                                                                                                                                                                                   --------          ----         --------     ------------------
3            1            Top(TOP EXPRESSION:((50)))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    0            0                         Top                  Top                  TOP EXPRESSION:((50))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      50           0            5E-006       177          0,164991         [T1].[_IDRRef], [T1].[_Fld13011RRef], [T1].[_Fld13013RRef], [T1].[_Fld13020], [T1].[_Fld13021RRef], [T1].[_Fld13040RRef], [T1].[_Fld13041RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T1].[_Fld13082RRef], [T3].[_Reference77_IDRRef]                   PLAN_ROW     0            1                  
3            1              |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([T1].[_Fld13011RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T3].[_Reference77_IDRRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       0            1            0            Nested Loops         Left Anti Semi Join  OUTER REFERENCES:([T1].[_Fld13011RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T3].[_Reference77_IDRRef])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            50           0            3,77511      177          0,164986         [T1].[_IDRRef], [T1].[_Fld13011RRef], [T1].[_Fld13013RRef], [T1].[_Fld13020], [T1].[_Fld13021RRef], [T1].[_Fld13040RRef], [T1].[_Fld13041RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T1].[_Fld13082RRef], [T3].[_Reference77_IDRRef]                   PLAN_ROW     0            1                  
902310       1                   |--Nested Loops(Inner Join)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            0            2            1            Nested Loops         Inner Join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      55,5556      0            3,77511      177          0,00931134       [T1].[_IDRRef], [T1].[_Fld13011RRef], [T1].[_Fld13013RRef], [T1].[_Fld13020], [T1].[_Fld13021RRef], [T1].[_Fld13040RRef], [T1].[_Fld13041RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T1].[_Fld13082RRef], [T3].[_Reference77_IDRRef] NO JOIN PREDICATE PLAN_ROW     0            1                  
150385       1                   |    |--Clustered Index Scan(OBJECT:([LocalBase].[dbo].[_Document517].[PK___Document517NG__2E5FFDE1] AS [T1]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         0            3            2            Clustered Index Scan Clustered Index Scan OBJECT:([LocalBase].[dbo].[_Document517].[PK___Document517NG__2E5FFDE1] AS [T1])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              [T1].[_IDRRef], [T1].[_Fld13011RRef], [T1].[_Fld13013RRef], [T1].[_Fld13020], [T1].[_Fld13021RRef], [T1].[_Fld13040RRef], [T1].[_Fld13041RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T1].[_Fld13082RRef]                                                                                                                                                                                                                                             9,25078      15,9142      0,16558      161          0,00416513       [T1].[_IDRRef], [T1].[_Fld13011RRef], [T1].[_Fld13013RRef], [T1].[_Fld13020], [T1].[_Fld13021RRef], [T1].[_Fld13040RRef], [T1].[_Fld13041RRef], [T1].[_Fld13042RRef], [T1].[_Fld13061RRef], [T1].[_Fld13082RRef]                                               PLAN_ROW     0            1                  
902310       150385              |    |--Stream Aggregate(GROUP BY:([T3].[_Reference77_IDRRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        0            4            2            Stream Aggregate     Aggregate            GROUP BY:([T3].[_Reference77_IDRRef])                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      6,0055       0            6,0055E-006  23           0,00486364       [T3].[_Reference77_IDRRef]                                                                                                                                                                                                                                     PLAN_ROW     0            10,2507            
902310       150385              |         |--Index Seek(OBJECT:([LocalBase].[dbo].[_Reference77_VT2072].[_Referenc77_VT2072_ByField2075_RR] AS [T3]), SEEK:([T3].[_Fld2074RRef]=[@P1]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                0            5            4            Index Seek           Index Seek           OBJECT:([LocalBase].[dbo].[_Reference77_VT2072].[_Referenc77_VT2072_ByField2075_RR] AS [T3]), SEEK:([T3].[_Fld2074RRef]=[@P1]) ORDERED FORWARD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                [T3].[_Reference77_IDRRef]                                                                                                                                                                                                                                                                                                                                                                                                                                   6,0055       0,003125     0,000163606  23           0,00480208       [T3].[_Reference77_IDRRef]                                                                                                                                                                                                                                     PLAN_ROW     0            10,2507            
902307       902310              |--Filter(WHERE:([LocalBase].[dbo].[_InfoRg20970].[_Fld20971_TYPE] as [T5].[_Fld20971_TYPE] IS NULL AND [LocalBase].[dbo].[_InfoRg20970].[_Fld20971_RTRef] as [T5].[_Fld20971_RTRef] IS NULL AND [LocalBase].[dbo].[_InfoRg20970].[_Fld20971_RRRef] as [T5].[_Fld20971_RRRef] IS NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                0            7            1            Filter               Filter               WHERE:([LocalBase].[dbo].[_InfoRg20970].[_Fld20971_TYPE] as [T5].[_Fld20971_TYPE] IS NULL AND [LocalBase].[dbo].[_InfoRg20970].[_Fld20971_RTRef] as [T5].[_Fld20971_RTRef] IS NULL AND [LocalBase].[dbo].[_InfoRg20970].[_Fld20971_RRRef] as [T5].[_Fld20971_RRRef] IS NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               1            0            3,6917E-006  9            0,164991                                                                                                                                                                                                                                                                        PLAN_ROW     0            101,002            
914162       902310                   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T4].[_Fld20899RRef], [Expr1014]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             0            8            7            Nested Loops         Left Outer Join      OUTER REFERENCES:([T4].[

(9) Как то так не очень выкладывается :((( -- это "медленный"

(11) Для этого есть pastebin

(10) Неполный

https://my-files.ru/rmz9kw

https://my-files.ru/a6uc4q  -- быстрый

(7) Это если не всобачивать в каждый запрос вот такие портянки. Причем эта считается еще простенькой. А ведь по сути что она делает? Вычисляет список доступных пользователю ключевых объектов. Условно постоянный список (и обычно довольно короткий по итогу) - в каждом запросе, Карл!
Когда я от этого избавился, то мои RLS начали ускорять работу, а не замедлять. Как по-хорошему и должно быть.

(16) На данном этапе переделывать все шаблоны в RLS -- это нереально. Кроме того, что просто еще непонятно на что переделать. Так еще это пока что тот кусок работы, который мы не потянем физически.

(12) https://pastebin.com/bcJWDPMS  -- медленный
https://pastebin.com/9gxMXcsz  -- быстрый

В этих РЛС есть кусок кода, который отвечает за динамическое применение изменения групп доступа. Без перезахода пользователей. Его можно убрать.

(18) Откройте для себя XML форму планов запросов

(17) Чаще всего на практике используется всего несколько простых разделителей. Ну по типу как здесь - Организации, Подразделения, СтатьиДДС.
Переписывается это относительно несложно. Просто для каждого разделителя заводишь параметр сеанса вида ФиксированныйМассив и при старте программы заполняешь их по той же логике, по которой они сейчас вычисляются в запросе. Все. Для признака полных прав по разделителю я пихал в массив пустое значение.
После этого (это можно делать постепенно) меняешь типовые RLS на предельно простые, с банальными условиями на вхождение в массив.

(15) Для начала, запросы и планы намного разные.

(18) И sentry one plan explorer

(21) +100500

(23) Штатный профайлер все кажет.

(25) Этот - удобнее

(22) *немного*

(23) Спасибо, попробую завтра разобраться с этой программой
(21) Подумаем над этим вариантом. Но повторюсь, это большой кусок работы.

(22) Может быть. Я проводил много экспериментов и возможно прислал несколько отличающиеся запросы. Но не смотря на это результат один: "медленный" пользователь работает долго, "быстрый" -- быстро..

По-моему, классический случай "data skew". Оптимизатор решил, что кардинальность будет низкая, выбрал nested loops. Строк для этого пользователя оказалось много, nested loops стал неэффективен.

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

(30) (31) как это побороть ?

(32) обновление статистики не помогло ((

(32) Либо звать спеца по 2005 скулю, либо (8)

(28) Если это большой кусок работы, то я завидую вашей загрузке :)
Реализовать первую часть - два часа с отладкой и перекурами. Переписать сабжевое RLS = скорость набирания текста.

(35) мне видется, что это не все так просто. Но спасибо за идею, подумаем над ее реализацией

Помнится, тоже наткнулся на такое с sql2000, дико тормозило РЛС на ЗУП. Переход на 2008r2 проблему решил. Там оптимизатор поумнее.

А вообще, попробуй запустить sp_updatestats на текущей базе.
По-идее, это тот же UPDATE STATISTICS, вид сбоку.
Но мне как-то реально помогло на "залипшем" неоптимальном плане выполнения. Фиг его, знает, может и поможет.

Ну, т.е. оно обновляет статистику для всех таблиц и сбрасывает кэши планов выполнения.

(37) У нас лицензионный скл и просто поменять мы не можем :(
(38) читал про sp_updatestats -- возможно это и выход, но попробовать смогу наверное только на выходных

(39) выполнял отдельно:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

По моему в комплекте с update это тоже самое

(40) Это разностное обновление. Т.е. если ты статистику считаешь регулярно, то шибко времени занять не должно и на боевом продакшене вполне можно запускать - беды никакой не должно причинить.
(41) А ты все же попробуй.

(42) В любом случае буду делать. Так как с проблемой надо что то решать.

(42) Просто если с выборочным обновлением/сбросом можно напортачить, то эта хрень гарантированно кэши сбросит.

Если таблица большая - статистика может тупо не собираться.

Обновление статистики не меняет план запроса.

Речь идет о 100 млн. строк и более.

Обновление статистики не меняет план запроса.

(0) Попробуй вытащить вот это  
"
(ВЫБРАТЬ РАЗЛИЧНЫЕ
           СоставГруппы.Ссылка КАК ГруппаПользователей
        ИЗ
            Справочник.ГруппыПользователей.ПользователиГруппы КАК СоставГруппы
        ГДЕ
            СоставГруппы.Пользователь = &ТекущийПользователь)
"

Во временную таблицу.

(48) Это же переделанный запрос из RLS -- там нет временных таблиц

(47) таблица относительно маленькая -- 150 тысяч строк

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

нужно переходить на новый вид РЛС

(52) а что конкретно вы имеете ввиду ? В более новых решениях шаблоны реализованы по другому ? А как ? Может есть пример ?

(38) выполнил sp_updatestats -- не помогло

(54) Значит, дело не в статистике и не в процедурном кэше. Ваш кэп.

(51) В голову приходит dll-инжекция с перехватом обращений к sql-серверу и подстановкой хинтов, вместо "join" подставить "hash join", и так далее)